Nick's Excel Tip Of The Day - Part 1

Nick's picture
Nick's picture

40. Excel Tip Of The Day - Remove Dupes With Multiple Columns

This is a follow-on from yesterday's tip and relates to XL 2003

Suppose you have the following data and you want to remove the dupes (i.e. where both columns are the same):
Remove Duplicates Sorted List

Add a another column containing the concatenation of the previous 2 columns like so:
Remove Duplicates Sorted List
Copy it down, and proceed as with yesterday's tip

I need to extract repititive data from multiple columns

Ex:
641A is available in column A and column F and I need to extract this to a new column K
I have around 4000 similar value which are repeated in a range of A1 to I4000 please assist the easiest way to extract the repitative values.

Vishesh's picture

do u want this using macro or

do u want this using macro or this is one time activity that you want the unique values.

I need to extract repititive data from multiple columns

Ex:
641A is available in column A and column F and I need to extract this to a new column K
I have around 4000 similar value which are repeated in a range of A1 to I4000 please assist the easiest way to extract the repitative values.

I need to extract repititive data from multiple columns

Ex:
641A is available in column A and column F and I need to extract this to a new column K
I have around 4000 similar value which are repeated in a range of A1 to I4000 please assist the easiest way to extract the repitative values.

Nick's picture

39. Excel Tip Of The Day - Remove Duplicates from a sorted list

For XL 2003, there are many ways to find and remove duplicates from a sorted column of data, but this is my favourite as it doesn't involve leaving formulae on the sheet.

With a bit of practice you can become very quick at it.

Suppose you have the following data:
Remove Duplicates Sorted List
and you want to end up with the duplicates removed:
Remove Duplicates Sorted List

Step1:
Enter the following formula in the cell adjacent to the 1st data cell:
Remove Duplicates Sorted List
This is basically asking if the cell is the same as the one below. If it is, then it's a duplicate.

Step2:
Copy the formula down
Remove Duplicates Sorted List

Step3:
Select the 1st heading (B2), and press in sequence:
ALT then d then f then f.. this turns on the data filter.
Now filter for TRUE (click on the arrow at cell C2, and select TRUE)- this selects only the duplicates.
Remove Duplicates Sorted List

Step4:
Select the entire row for data that has been filtered (excluding the heading).. and right click=> Delete.. to delete.
Remove Duplicates Sorted List

Step5:
Turn off the data filter by using the shortcut: ALT then d then f then f, and clear the formulae that you added... There you have it:
Remove Duplicates Sorted List

Video Training on how to Remove Duplicates:

Use AdvancedFilter too

As my comment on previous article, you can use AdvancedFilter directly on multi column data too. It will filter out unique records with ease. :)

Nick's picture

Advanced Filter

Advanced Filter doesn't actually get rid of the duplicates, it only hides rows.
That's why I use the method as described.

Use AdvancedFilter

Frankly speaking, the method here is neither fast or convinient.

Just select the column, use AdvancedFilter, fill nothing in the Criteria, and click "Unique records only".

You can choose to copy the result to another place or filter in place.

Nick's picture

Removing Duplicates

I just timed it - 15 seconds.

Still prefer this to Advanced filter...

; - >