How to use Advanced Filter to filter unique records from 2 or more columns

jeremypyle

Board Regular
Joined
May 30, 2011
Messages
166
Hi, I have a list of about 600,000 names per column. I want to copy unique fields from multiple columns (column A and column C and column E. I can't put all the names into 3 columns first because the list is too long for excel. Is there a way to copy unique fields only from 3 different columns?
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
jeremypyle,

You could use AdvancedFilter Unique:

Column A to column AA
Column C to column AB
Column E to column AC

Then count how many there are in AA, AB, and AC.

Then maybe combine AA and AB to AD, and AdvancedFilter Unique AD to AE

Then count AE and AC, and you may be able to combine AE and AC to AF, and AdvancedFilter Unique AF to AG


We may be able to use VBA's Scripting.Dictionary to find the total uniques from the three ranges, and then write the uniques to multiple columns.

I am not sure what size array the Scripting.Dictionary can handle - it may also have something to do with the RAM in your PC.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,337
Messages
5,601,026
Members
414,422
Latest member
acegreen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top