Concatenate of all data in different columns?

TomKnight

New Member
Joined
Oct 9, 2013
Messages
17
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.

VerbsAdjectiveNoun
buymen'sshoes
cheapwomen'sboots

<tbody>
</tbody>

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

Thank you very much in advance :)

Tom.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
d2=a2&" "&b2&" "&c2&","&a3&""&b2&" "&c2&","&a2&" "&b3&" "&c3

(and drag down)
 
Last edited:
Upvote 0
Perhaps this:-
Results start "E1"
Code:
[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
 
Upvote 0

Thank you both for your answers.

Mick, that worked like a charm. Thank you very much!
 
Last edited:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Try this:-
Code:
[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
 
Upvote 0
Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top