Compare two large lists of data

[Peter_Alan]

Every week I have a report generated and handed to me with more than 60,000 entries. I need to isolate which items are different than the week before. At the moment I have a macro that opens the previous weeks report, deletes all but four columns then copies that sheet in the book the macro is running from. The same is repeated for the most current report. The sheets are named "old" and "new" respectively.

I thought the simplest way to determine which items were new would be to have a formula in (for example) E2 =A2 & B2 & C2 & D2 down the whole column on both sheets. The only way to tell an item has been changed is to review all four column entries.

Up to this point excel does great, it isn't really slow.

My problem arises when I try to run a =match() with E2 against column E from the "old" sheet. Excel doesn't handle it so well. Any suggestions on how to do a quick column comparison?

The idea, after the column had ran the match function, is I could do a filter and delete out the items that had a numerical value returned. Returning unique items only.

 

[jbeaucaire]

Your method is sound, I don't see the problem.

In sheet2, in F2, this formula:

=MATCH(E2, Sheet1!$E:$E, 0)

...then copied down will give you numbers and errors.

Highlight column F, press F5, select Special > Formulas > Numbers (uncheck the rest) > OK and you've selected all the matches. Delete those rows.

 

[Peter_Alan]

I have been using a countif() function. That works although still very slow.

 

[Peter_Alan]

jbeaucaire, for the match function, excel is basically reviewing 60,000 items, in 60,000 items, which is 3,600,000,000 checks and then it does it again for another page. Excel hasn't made it through them all yet.

 

[brandonrlz]

Ironically I just answered someone else with a similar situation...here's what I've done in the past.

=IF(ISERROR(MATCH(A2,$C$2:$C$8,0)),"Not Found",A2)

Here's the post I responded to with all the cell references.

http://www.mrexcel.com/forum/showthread.php?t=576942

 

[brandonrlz]

jbeaucaire, for the match function, excel is basically reviewing 60,000 items, in 60,000 items, which is 3,600,000,000 checks and then it does it again for another page. Excel hasn't made it through them all yet.

OH SNAP!!! Good call....I didn't even think about that one....hmmmm.....

 

[jbeaucaire]

Yeah, on my computer it took about 3 minutes. You do have a large dataset, you do want the comparison to be done, so...