how do i combine 17 cells into 1 cell (SEE BELOW)

[Alan Cox]

The whole list on the right needs to be combined with each cell on the left

8SS00387       644778063081B
8SS00014       644778080767B
8SS00028       644778063265B
8SS00029       644778022385B
8SS00038       644778022392B
8SS00040       644778096140B
8SS00041       799366338765B
8SS00058       799366815235B
8SS00063       644778061391B
8SS00061       799366137085B
8SS00069       644778050883B
8S000732       644778073776B
8SS00087       799366357001B
8SS00092       799366329091B
8SS00113       799366034100B
8SS00115       799366820888B
8SS00121       799366710646B
8SS00146
8SS00151
8SS00153
8SS00154
8SS00165

[#2]

Hi Alan,

"Combine" you mean to generate the string from value in the cell from left column and all values from right column?

And on which version of Excel are you? If on 2016 TEXTJOIN shall work, otherwise it'll be bit more complex.

[#3]

=A2&B2

[#4]

If this way when A2&B2&B3&...&B25

[#5]

Alan,
If you need spaces between numbers , you can use the following for each row
=A1&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
=A2&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
=A3&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
=A4&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
=A5&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
=A6&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
=A7&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
=A8&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
=A9&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
=A10&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
=A11&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
=A12&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
=A13&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
=A14&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
=A15&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
=A16&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
=A17&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
=A18&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
=A19&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
=A20&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
=A21&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17
=A22&" "&B1&" "&B2&" "&B3&" "&B4&" "&B5&" "&B6&" "&B7&" "&B8&" "&B9&" "&B10&" "&B11&" "&B12&" "&B13&" "&B14&" "&B15&" "&B16&" "&B17

[#6]

excel version 2016

here are my instructions for the test

Combine each of the values in UPC tab with the corresponding Ship to ID (First group, (2 sided, Red) should have 374 combinations)

Put all combinations from each of the Red, Yellow and Blue tabs into one column in newly created tab, See Combos tab (Purple) column A, this is the sample of what it should look like, in total there will be 770 combinations from all 3 tabs)

[#7]

Alan, thank you for the clarification.

I din't check but i guess UPCs are all different for all 3 tabs

[#8]

Alan,

That's the formula only for first pair of tabs. Please check if i understood your logic correctly and if it works in your environment

=TEXTJOIN("",TRUE,
   OFFSET('2 Sided Ship to ID''s '!$A$1,INT((ROW()-2)/(COUNTA('2 Sided UPCs'!A:A)-1))+1,0),
   IF(ROW()>(COUNTA('2 Sided Ship to ID''s '!A:A)-1)*(COUNTA('2 Sided UPCs'!A:A)-1)+1,
      "",
      OFFSET('2 Sided UPCs'!$A$1,MOD(ROW()-2,(COUNTA('2 Sided UPCs'!A:A)-1))+1,0)
   )
)

That is A column in Combos tab attached

[#9]

Here's another option. This solution is not pretty, and I know it's not the most efficient, but it works.

I see your data is in column A and column B, starting in row 2 and continues down for an undetermined amount, which could change from sheet to sheet. You can put this formula in cell C2 and drag down until you've reached your 274 or other number of combinations:

=CONCATENATE(INDIRECT("A"&IF((MOD((CELL("row",A2)-1),COUNTIF($A$2:$A$500,"<>"&"")))=0,COUNTIF($A$2:$A$500,"<>"&""),(MOD((CELL("row",A2)-1),COUNTIF($A$2:$A$500,"<>"&""))))+1),INDIRECT("B"&(ROUNDDOWN((CELL("row",C2)-2)/COUNTIF($A$2:$A$500,"<>"&""),0)+2)))

Like I said, not pretty or succint but it works. It will count how many entries you have, runs through every entry in column A combining it with the first entry in column B, then combines them all with the second entry in column B, etc...

[#10]

Hi Bryant,

Yes, good. Logic is the same, but this one more universal if TEXTJOIN is not available. Two points

1) I'd add to the logic returning of empty string after all combinations are built

2) To simplify the formula a bit. In particular, these formulas return exactly the same

22	=COUNTIF($A$2:$A$500,"<>"&"")
22	=COUNTIF($A$2:$A$500,"<>")
22	=COUNTA($A$2:$A$500)
22	=COUNTA(A:A)-1
	===
1	=ROUNDDOWN(33/22,0)
1	=INT(33/22)

And taking into account how the data is structured in the file finally like

=INDIRECT("A"& 
 IF(MOD(ROW()-1,COUNTA(A:A)-1),
    MOD(ROW()-1,COUNTA(A:A)-1),
    COUNTA(A:A)-1
 )+1) &
 INDIRECT("B" & INT((ROW()-2)/(COUNTA(A:A)-1))+2)

[#11]

Great tips! And awesome improvements on my suggestion!