Creating two lists from Multiple Columns

[Macro628]

Hello,

I'm trying to find a method to create what's seen in column R and in column S from the information in columns B-F.
I provided the sample worksheet of this problem within this post.
The process is to add all the core words(column D) to each of the seed words(Column E) and all the location words(Column D).
In addition, there will be two words one singular and one plural but both will be within the same group much like the setup seen in Column R.
I attempted using If statements, If statements with concatenate formulas but they haven't produced the desired results.


Any help would be greatly appreciated

[mancubus]

1
are the combinations in col R correct?

rule:
combine each value in D with all values in E and combine each value in E with all values in F
is this true?

because below do not comply with the combination rule?
Health: Therapy - EMT Chesapeake - Diploma
Health: Therapy - EMT Chesapeake - General
Health: Therapy - EMT Chesapeake - General
Health: Therapy - EMT Chesapeake - General
Health: Therapy - EMT Chesapeake - General


2
values in D:
Certification
certifications
Class
classes

combinations in R
Health: Therapy - EMT Certification - Chesapeake
Health: Therapy - EMT Certification - Chesapeake
Health: Therapy - EMT Class - Chesapeake
Health: Therapy - EMT Class - Chesapeake

no plural? correct?

3
is the order in R important?
or do you need only combinations?

[mancubus]

try below code where
1 combination rule is correct (and examples in my previous post is wrong)
2 singular plural is important (so there is a typo in the example file)
3 the order in the example is not important


Sub vbax_57626_create_lists_from_cells_multi_columns()

Dim pref As String
Dim d As Long, e As Long, f As Long

With Worksheets("Sheet1") 'change Sheet1 to suit
pref = Range("B2").Value & ": " & Range("C2").Value & " - "
For d = 2 To .Range("D" & .Rows.Count).End(xlUp).Row
For e = 2 To .Range("E" & .Rows.Count).End(xlUp).Row
For f = 2 To .Range("F" & .Rows.Count).End(xlUp).Row
.Range("R" & .Rows.Count).End(xlUp).Offset(1).Value = pref & .Range("D" & d).Value & " " & .Range("E" & e).Value & " - " & .Range("F" & f).Value
.Range("S" & .Rows.Count).End(xlUp).Offset(1).Value = .Range("D" & d).Value & " " & .Range("E" & e).Value & " " & .Range("F" & f).Value
Next f
Next e
Next d
End With

End Sub


i assume the values in cols are used as they are. so i did not capitilize the letters like examples.

[Macro628]

Hello Mancubus,

Thank you for taking time to take a look at this problem. To answer a few questions:

1) What's seen in Cols R and S is the ultimate goal that is trying to be achieved from Cols B to Col F
2) For Col R it needs to be in that format that is in the example
3) For Col S it is the combination of Col D - Col F (which that code worked wonderfully for Col S)
4) The only thing left is there a way within that macro code you provided to capitalize the letters to have it look like what's in Col R?

Thanks again

[mancubus]

Sub vbax_57626_create_lists_from_cells_multi_columns()

Dim pref As String
Dim d As Long, e As Long, f As Long

With Worksheets("Sheet1") 'change Sheet1 to suit
pref = Range("B2").Value & ": " & Range("C2").Value & " - "
For d = 2 To .Range("D" & .Rows.Count).End(xlUp).Row
For e = 2 To .Range("E" & .Rows.Count).End(xlUp).Row
For f = 2 To .Range("F" & .Rows.Count).End(xlUp).Row
.Range("R" & .Rows.Count).End(xlUp).Offset(1).Value = pref & StrConv(.Range("D" & d).Value, vbProperCase) & " " & StrConv(.Range("E" & e).Value, vbProperCase) & " - " & StrConv(.Range("F" & f).Value, vbProperCase)
.Range("S" & .Rows.Count).End(xlUp).Offset(1).Value = .Range("D" & d).Value & " " & .Range("E" & e).Value & " " & .Range("F" & f).Value
Next f
Next e
Next d
End With

End Sub


you can replace vbProperCase with 3 to shorten the line.
google StrConv function.

[mancubus]

ooops

because of With - End With block, change

pref = Range("B2").Value & ": " & Range("C2").Value & " - "
to

pref = .Range("B2").Value & ": " & .Range("C2").Value & " - "
ie insert "period"s before "Range"s.