remove duplicate value but keep rest of the row values

stackErr picture stackErr · Jul 19, 2013 · Viewed 35.3k times · Source

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

Excel data

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

excel data 2

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

ahem picture ahem · Jan 16, 2014

Given that duplicate data cells are next to each other

and data are on column A with blank top row, this should work. It will remove duplicates except the first occurrence.

=IF(A1=A2,"",A2)
=IF(A2=A3,"",A3)
.
.
.