Combine two columns using multiple selections

[nigelrs]

Hello,

I have struggled with this one and I am not a coder, was looking for some code or assistance. I have 2 columns:

A1 Shirt B1 Blue
A2 Jumper B2 Red
A3 Socks B3 Green
A4 Hat B4 White

I want it to go to the first line in A1 and then combine with every line in column B, so you have Shirt Blue, Shirt Red, Shirt Green, Shirt White. The go to line A2 and repeat with Jumper Blue, Jumper Red, Jumper Green, Jumper White. There are several hundreds of items in both column A & B - which will give a total combination of over 500,000 item combinations.

I cannot find a way to concatenate these easily. Any advise appreciated.

 

[dermie_72]

you could try:

C2 = A1&" "&B$1", "&A1&" "&B$2", "&A1&" "&B$3", "&A1&" "&B$4
then drag it down.
That will give you Shirt Blue, Shirt Red, Shirt Green, Shirt White in one cell.
and for each cell under it will combine the same 4 variables, with a different Start.
If they need to be in different cells.

C2 = A1&" "&B$1
D2 = A1&" "&B$2
E2 = A1&" "&B$3
F2 = A1&" "&B$4

Then drag down.

Hope that helps.

 

[nigelrs]

you could try:

C2 = A1&" "&B$1", "&A1&" "&B$2", "&A1&" "&B$3", "&A1&" "&B$4
then drag it down.
That will give you Shirt Blue, Shirt Red, Shirt Green, Shirt White in one cell.
and for each cell under it will combine the same 4 variables, with a different Start.
If they need to be in different cells.

C2 = A1&" "&B$1
D2 = A1&" "&B$2
E2 = A1&" "&B$3
F2 = A1&" "&B$4

Then drag down.

Hope that helps.

Thanks for your feedback - trust someone to give me feedback from the same city I was born in half way around the world!

 

[Rick Rothstein]

Hello,

I have struggled with this one and I am not a coder, was looking for some code or assistance. I have 2 columns:

A1 Shirt B1 Blue
A2 Jumper B2 Red
A3 Socks B3 Green
A4 Hat B4 White

I want it to go to the first line in A1 and then combine with every line in column B, so you have Shirt Blue, Shirt Red, Shirt Green, Shirt White. The go to line A2 and repeat with Jumper Blue, Jumper Red, Jumper Green, Jumper White. There are several hundreds of items in both column A & B - which will give a total combination of over 500,000 item combinations.

I cannot find a way to concatenate these easily. Any advise appreciated.

Put this formula in cell C1 and copy it across until it produces a blank cell (actually, you can copy it across even further if you think the list in Column B might grow), then copy all those cells down at least to the bottom of the list in Column A (you can copy them down even further is you expect the list in Column A to grow)...

=IF(OR(A1="",COLUMN()-2>COUNTA($B:$B)),"",$A1&" "&INDEX($B:$B,COLUMN()-2))