Group By and Concatenate Rows with Nulls

[chanmar]

Hi there, I am trying to group some rows together. Some fields can be null/empty. ID is the unique identifier and not necessarily numeric. If a field is populated and has the same ID, they should be concatenated. There can be 20+ fields. Example workflow attached.

Input:

ID	Field1	Field2	Field3	Field4	Field5	Field6	Field7	Field8
1	1	Orange			137500			Type1
1	2	Black	BRAND	SPORT	137500		N	Type2
2	1	Orange			137500			Type1
2	1	Black	BRAND	SPORT	137500		N	Type1

Expected Output:

ID	Field1	Field2	Field3	Field4	Field5	Field6	Field7	Field8
1	1,2	Orange,Black	BRAND	SPORT	137500		N	Type1,Type2
2	1	Orange,Black	BRAND	SPORT	137500		N	Type1

I seem to be able to group and concatenate them together, but then the cells have repeated values.

Any suggestions on this? I think I can strip out the repeated values via regex for each field, but if there's another approach that doesn't get repeated values that would be better.

[BrandonB · Accepted solution]

Hi @chanmar 

I think the attached worfklow should accomplish what you are looking to do. I used a transpose, text to columns (split to rows), unique, and a crosstab.

[chanmar]

That is genius, thank you!!