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.
Solved! Go to Solution.
Hi @mcha54
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.
That is genius, thank you!!