Better Formula for Combining data into one cell for unique instances

[tsimp42]

Hello,

Looking for a better option to combine the data in Suffix Column to the Suffix Combo column when the UID repeats.

Current Formula:
=if(AH11=AH10,AJ10&", "&AI11,AI11)

For the last 2 rows in this sheet the UID is the same. Rather than only seeing the full combination of all suffix in the 2nd row would like to see it in both rows.

UID (AH)	Suffix (AI)	Suffix Combo (AJ)
100098504453943235	QZ, QS, P3, N/A	QZ, QS, P3, N/A
100101504453143239	QZ, QS, P2, N/A	QZ, QS, P2, N/A
100104664454545378	AA, P2, QS, N/A	AA, P2, QS, N/A
100104894453219328	QZ, P3, N/A, N/A	QZ, P3, N/A, N/A
100104894456019328	QZ, P3, N/A, N/A	QZ, P3, N/A, N/A
100105504453659409	QZ, P2, N/A, N/A	QZ, P2, N/A, N/A
100106684455027096N	LT, N/A, N/A, N/A	LT, N/A, N/A, N/A
100107164455327447	QZ, P2, N/A, N/A	QZ, P2, N/A, N/A
100107724454549650	QZ, QS, P3, N/A	QZ, QS, P3, N/A
100108414453659820	QK, P1, N/A, N/A	QK, P1, N/A, N/A
100108414453659820	QX, P1, N/A, N/A	QK, P1, N/A, N/A, QX, P1, N/A, N/A

 

[Fluff]

If the UID was repeated 3 times, would you want the suffix to be repeated 3 times in every cell as well?

 

[Evil Bean Counter]

I copied the above data and dropped it into a spreadsheet, A2:C13, your headers are in row 2.

Then I entered an embedded IF formula to consider if there is a matching suffix entry above or below the row I'm looking at. =IF(A12=A11,B11&" "&B12,IF(A12=A13,B12&" "&B13,B12))

Got these results for the last two rows.

QK, P1, N/A, N/A QX, P1, N/A, N/A QK, P1, N/A, N/A QX, P1, N/A, N/A

 

[tsimp42]

If the UID was repeated 3 times, would you want the suffix to be repeated 3 times in every cell as well?

Yes, each time the UID repeats combine all suffix.

 

[Fluff]

Ok, how about

+Fluff 1.xlsm
	AH	AI	AJ
1
2	100098504453943235	QZ, QS, P3, N/A	QZ, QS, P3, N/A
3	100101504453143239	QZ, QS, P2, N/A	QZ, QS, P2, N/A
4	100104664454545378	AA, P2, QS, N/A	AA, P2, QS, N/A AX, P2, QS, N/A AI, P2, QS, N/A
5	100104664454545378	AX, P2, QS, N/A	AA, P2, QS, N/A AX, P2, QS, N/A AI, P2, QS, N/A
6	100104664454545378	AI, P2, QS, N/A	AA, P2, QS, N/A AX, P2, QS, N/A AI, P2, QS, N/A
7	100105504453659000	QZ, P2, N/A, N/A	QZ, P2, N/A, N/A
8	100106684455027096N	LT, N/A, N/A, N/A	LT, N/A, N/A, N/A
9	100107164455327000	QZ, P2, N/A, N/A	QZ, P2, N/A, N/A
10	100107724454549000	QZ, QS, P3, N/A	QZ, QS, P3, N/A
11	100108414453659820	QK, P1, N/A, N/A	QK, P1, N/A, N/A QX, P1, N/A, N/A
12	100108414453659820	QX, P1, N/A, N/A	QK, P1, N/A, N/A QX, P1, N/A, N/A
Primary

Cell Formulas
Range	Formula
AJ2:AJ12	AJ2	=TEXTJOIN(" ",,FILTER($AI$2:$AI$100,$AH$2:$AH$100=AH2))

 

[tsimp42]

I copied the above data and dropped it into a spreadsheet, A2:C13, your headers are in row 2.

Then I entered an embedded IF formula to consider if there is a matching suffix entry above or below the row I'm looking at. =IF(A12=A11,B11&" "&B12,IF(A12=A13,B12&" "&B13,B12))

Got these results for the last two rows.

QK, P1, N/A, N/A QX, P1, N/A, N/A QK, P1, N/A, N/A QX, P1, N/A, N/A

Thank you. Think this assumes the UID is only 2 instances and immediately precedes and follows the original UID; unfortunately my data is not consistently in that format. That would be too easy. Thanks again. Have a great day.

 

[tsimp42]

Ok, how about

+Fluff 1.xlsm
	AH	AI	AJ
1
2	100098504453943235	QZ, QS, P3, N/A	QZ, QS, P3, N/A
3	100101504453143239	QZ, QS, P2, N/A	QZ, QS, P2, N/A
4	100104664454545378	AA, P2, QS, N/A	AA, P2, QS, N/A AX, P2, QS, N/A AI, P2, QS, N/A
5	100104664454545378	AX, P2, QS, N/A	AA, P2, QS, N/A AX, P2, QS, N/A AI, P2, QS, N/A
6	100104664454545378	AI, P2, QS, N/A	AA, P2, QS, N/A AX, P2, QS, N/A AI, P2, QS, N/A
7	100105504453659000	QZ, P2, N/A, N/A	QZ, P2, N/A, N/A
8	100106684455027096N	LT, N/A, N/A, N/A	LT, N/A, N/A, N/A
9	100107164455327000	QZ, P2, N/A, N/A	QZ, P2, N/A, N/A
10	100107724454549000	QZ, QS, P3, N/A	QZ, QS, P3, N/A
11	100108414453659820	QK, P1, N/A, N/A	QK, P1, N/A, N/A QX, P1, N/A, N/A
12	100108414453659820	QX, P1, N/A, N/A	QK, P1, N/A, N/A QX, P1, N/A, N/A
Primary

Cell Formulas
Range	Formula
AJ2:AJ12	AJ2	=TEXTJOIN(" ",,FILTER($AI$2:$AI$100,$AH$2:$AH$100=AH2))

Worked perfectly. Thanks so much!!

 

[Fluff]

Glad we could help & thanks for the feedback.