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

jeremypyle

Board Regular
Joined
May 30, 2011
Messages
174
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?
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

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
Back
Top