How To: Deleting multiple duplicate values in each column compared to first row values

[Czaggy]

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

[Ladarthure · Accepted 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 😉 )

[danilang · Accepted solution]

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