Hi,
does anyone what workflow to use in order to get rid of duplicates of the first row of a new Project (defined by ProjectID). In this input table i have the first row of a project with the main data, now i want the following rows to only show the values in each column, that are different from the first row values (they could also be replaced by 0 or NULL). I found the multiple row formula to work for only one column each, but id have to add a new formula for every column i have - which is 53 columns in the original input table.
I would be super thankful if anyone can help me out or give me some mental inputs that will help me solve this problem.
Input table:
ProjectID | Info1 | Info2 | Info3 | Info4 | Info5 |
1 | x1 | x2 | x3 | x4 | x5 |
1 | x1 | x2 | y | x4 | x5 |
1 | x1 | x2 | x3 | z | x5 |
1 | x1 | x2 | x3 | x4 | o |
2 | a1 | a2 | a3 | a4 | a5 |
2 | a1 | b | a3 | a4 | a5 |
2 | a1 | a2 | c | a4 | a5 |
2 | a1 | a2 | a3 | d | a5 |
2 | a1 | a2 | a3 | a4 | e |
Output:
ProjectID | Info1 | Info2 | Info3 | Info4 | Info5 |
1 | x1 | x2 | x3 | x4 | x5 |
1 | y | ||||
1 | z | ||||
1 | o | ||||
2 | a1 | a2 | a3 | a4 | a5 |
2 | b | ||||
2 | c | ||||
2 | d | ||||
2 | e |
Solved! Go to Solution.
hi @Czaggy,
I achieved to do so using transpose and cross tabs and compare the values, there is a sample workflow attached, it does what you asked for (and you can add as many info as you want it will still work 😉 )
Hi @Czaggy
Good solution from @Ladarthure
Here's a different one that uses the unique tool to pull out only the changed rows for each column and then applies a Cross tab to get the original structure back.
The Unique tool looks at ProjectID, Name and Value of the transposed data. The results are
Dan