Concatenate of all data in different columns?

[TomKnight]

Hi,

I am wondering if it possible to write a formula/script to concatenate all words within a column, and combine them with all words in other columns.

e.g.

Verbs	Adjective	Noun
buy	men's	shoes
cheap	women's	boots

<tbody>
</tbody>

would return 'buy mens shoes', 'cheap mens shoes', 'buy womens boots' etc.

Thank you very much in advance

Tom.

 

[Oeldere]

d2=a2&" "&b2&" "&c2&","&a3&""&b2&" "&c2&","&a2&" "&b3&" "&c3

(and drag down)

 

[MickG]

Perhaps this:-
Results start "E1"

[COLOR=Navy]Sub[/COLOR] MG10Sep25
[COLOR=Navy]Dim[/COLOR] Ray
[COLOR=Navy]Dim[/COLOR] L, M, R
[COLOR=Navy]Dim[/COLOR] c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
Ray = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp)).Resize(, 3)
[COLOR=Navy]For[/COLOR] L = 1 To UBound(Ray)
    [COLOR=Navy]For[/COLOR] M = 1 To UBound(Ray)
        [COLOR=Navy]For[/COLOR] R = 1 To UBound(Ray)
             c = c + 1
             Cells(c, "E") = Ray(L, 1) & ", " & Ray(M, 2) & ", " & Ray(R, 3)
        [COLOR=Navy]Next[/COLOR] R
 [COLOR=Navy]Next[/COLOR] M
[COLOR=Navy]Next[/COLOR] L
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[TomKnight]

Thank you both for your answers.

Mick, that worked like a charm. Thank you very much!

 

[L. Howard]

Is this what you are looking for?

If so, then you probably want a Cartseian Permutation.


buy men's shoes
buy men's boots
buy women's shoes
buy women's boots
cheap men's shoes
cheap men's boots
cheap women's shoes
cheap women's boots


Not sure how this will post, but each word is in a cell.

Regards,
Howard

 

[TomKnight]

Hi Mick,

How would the script change if I wanted the results in a different column that E? And have the list populate in Row 2 (e.g. F2) instead of F1?

Thanks,
Tom

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG10Sep29
[COLOR="Navy"]Dim[/COLOR] Ray
[COLOR="Navy"]Dim[/COLOR] L, M, R
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
c = 1
Ray = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp)).Resize(, 3)
[COLOR="Navy"]For[/COLOR] L = 1 To UBound(Ray)
    [COLOR="Navy"]For[/COLOR] M = 1 To UBound(Ray)
        [COLOR="Navy"]For[/COLOR] R = 1 To UBound(Ray)
             c = c + 1
             Cells(c, "F") = Ray(L, 1) & ", " & Ray(M, 2) & ", " & Ray(R, 3)
        [COLOR="Navy"]Next[/COLOR] R
 [COLOR="Navy"]Next[/COLOR] M
[COLOR="Navy"]Next[/COLOR] L
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[snb_]

Sub M_snb()
   sn = Cells(1).CurrentRegion.Resize(, 6)

   For j = 2 To UBound(sn)
     sn(j, 6) = Join(Application.Index(sn, 1, Array(1, 2, 3)), ",")
   Next

   Cells(1).CurrentRegion.Resize(, 6) = sn
End Sub