CODEWITHSUNDEEP
excelexcel-formulaexcel-2010worksheet-function
stackErr
stackErr

Reputation: 4170

remove duplicate value but keep rest of the row values

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:

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.

Upvotes: 4

Views: 36674

Answers (5)

ahem
ahem

Reputation: 81

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)
.
.
.

Upvotes: 8

Prab
Prab

Reputation: 21

Try this 

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

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

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

Upvotes: 2

SeanC
SeanC

Reputation: 15923

There is another way that doesn't involve a helper column... conditional formatting.

Highlight A2:G(whatever the last cell is)

Use a formula to decide which cells to highlight

Formula would be =AND($A2=$A1,$F2=$F1)

Set the format to be white text (or equal to whatever you have the background color)

Upvotes: 0

Stepan1010
Stepan1010

Reputation: 3136

You asked for a formula? I suppose you could do something like this. Although it might be easier to use a macro:

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

Example1

You could have a duplicate table on a separate tab using this formula to clear the rows you don't need - or however you want. Good Luck.

Upvotes: 1

JCleveland
JCleveland

Reputation: 337

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.

Upvotes: 2

Related Questions