Delete the duplicate value but keep the rest of the row values

Question

I have an excel sheet (csv) like this:

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

Basically:

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

I am struggling to create a formula that will do this. If I try to "Delete duplicates", it deletes the value and shifts the values ​​to one row. I want it to remove duplicates, but not change the values.

Answer 1

Try this (note you need an empty top line (edit: in fact, you have a header line in order)):

=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 that you need an additional condition.

Answer 2

Given that duplicate data cells are next to each other

and the data is in column A with an empty top row, this should work. It will remove duplicates except the first one.

= IF (A1 = A2 "" A2)

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

.

.

.

Answer 3

try it

 =IF((COUNTIF(A1:A$203,A1))=1,A1,"") 

It will count duplicates, and for the last count, it will keep the value.

Try COUNTIF(A1:A$203,A1) and you should understand the logic well.

Answer 4

Did you ask for a formula? I suppose you could do something like this. Although it would be easier to use a macro:

 =IF(COUNTIF($A$2:A6,"=" & A7),"",A7) 

You can have a duplicate table in a separate tab, using this formula to clear rows that you don't need, or if you want. Good luck.

Answer 5

There is another way that does not include an auxiliary column ... conditional formatting.

Highlight A2: G (regardless of the last cell)

Use the formula to determine which cells emit

The formula will be =AND($A2=$A1,$F2=$F1)

Set the format for white text (or equal to the background color)