Delete Cell Numbers of Column B From Column A (Column B Small and Column A too big)

Hi,

Are you really saying that you have over 5 million records in your 5 sheets?

If so are there duplicates amongst them? If so the first step would be to reduce these to a unique list. So extract the unique numbers from each of the 5 sheets and compile a separate list by stacking the unique numbers from each sheet underneath each other, then finally when you've done that extract a unique list from that new list.

When you've done that, and lets's assume this final unique list is now in column E, in F1 enter =MATCH(E1,B$2:B$302,false) and copy this down column F as far as you need. Then filter column F for the #N/A values since these are those numbers that aren't in your B2:B302.

I can't help but think a Dictionary or Collection object might make really quick work of this. Unfortunately I'm not very good at coding them. Leith coded one for me a year or so ago and it reduced my multi-replacement macro by about 99% in time.

Hello it_electronics2000 ,

This is the method Paul was was describing. The macro uses the Dictionary Object to identify and remove duplicate values. Each column of numbers is first read into an array before being processed. This greatly speeds up execution time. Add a new standard VBA Module to your workbook. Copy and paste this code into the module. You can the add a button on a worksheet to call the macro.

Please Login or Register  to view this content.