remove duplicate value but keep rest of the row values

Question

I have a excel sheet(csv) like this one:

and I want the output(tab delimited) to be like this:

Basically:

• replace duplicates with blanks but
  • if col6 value is different from the previous row for the same col1 value, all the data fields should be included.

I am struggling to create a formula which would do this. If I try to "Remove Duplicates" it removes the value and shifts the values up one row. I want it to remove the duplicates but not shift the values up.

Answer 1

Try this (note, you need a blank top row (edit: Actually, you're fine you have a header row)):

=IF(A2<>A1,A2,IF(D2<>D1,A2,""))
=IF(A2<>A1,B2,IF(D2<>D1,B2,""))
=IF(A2<>A1,C2,IF(D2<>D1,C2,""))
etc

in the top row and drag down

Edit: Noticed you needed an additional condition.