Duplicates in same Column

[XAE12]

Hi,

I want to remove duplicates in the same column from my data along with the whole row. However the other columns contain unique data, so I am unable to filter them. For example in the sample data, I want only the row where 1,2,3 appear for the first time.

For Eg: Charan, 1 and actor should stay but Chuck, 1 and singer should be removed.I have tried conditional formatting but it just shows me the rows to delete. Is there any way of deleting the rows where the numbers repeat? Thank you so much.

 

[Chihiro]

Just use Data -> Remove Duplicates.

Found in Data ribbon tool, under Data Tools section.

 

[XAE12]

Just use Data -> Remove Duplicates.

Found in Data ribbon tool, under Data Tools section.

Hi Chihiro,

Thanks for your reply.

However, I have already used the Alt + A +M function. With this the data set remains the same - Charan, 1 and actor as well as Chuck, 1 and singer.

I want to keep only that row where 1 occurs for the first time and remove the others where 1 repeats.

Thanks once again.

 

[Marc L]

Hi,​

​

use the easy Excel feature Remove Duplicates just well answering to its assistant according to the Logic …​

​

 

[Chihiro]

You need to select only the Rank column as criteria. Otherwise, there are no duplicates.

 

[XAE12]

Hi Chihiro and Marc,

I have found a way around this problem.

First, I swapped the name and rank columns in the data. Then I took only unique values of the rank in a separate sheet and used VLOOKUP for that rank in the original array. This returned only the first instance of that row that I wanted while ignoring the others - basically, my desired objective. After pasting the results as values, i swapped back the rank and name columns to their original positions and my data was ready.

Thank you so much for your time - Chihiro and Marc. And stay safe.