Forum Discussion
how do i combine 17 cells into 1 cell (SEE BELOW)
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)
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
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...
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)Great tips! And awesome improvements on my suggestion!
