This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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 @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.
That is genius, thank you!!