Comparing Large Data Sets in Excel

J

JohnnyCJohnny

Using Excel, we are trying to compare 500 zip codes to the zip codes
for 10,000 employees. We are trying to determine which employee zip
codes match the 500 zip codes we are using for comparison.

The problem is that the OR statement in Excel is rather limited and
only allows 30 comparisons at a time. We would like to be able to
take any one of our 10,000 employee zip codes and simply compare it to
the 500 zip codes and receive a TRUE or FALSE result based on whether
there is a match between the two.

We have done extensive searching in Excel Help and online and it
appears that the OR statement is our only option for comparison. Can
anyone point out a better way to compare a two large sample sets in
Excel to determine if there are matches between the two sets?

Thanks!
 
G

Greg Glynn

Try something like this:

Define your ZipCodes as a Named Range - "ZipCodes"
Define your Employee ZipCodes as a Named Range - "EmpZips"

Then run a nested For-Next loop

For Each C in Range("EmpZips")
For Each D in Range("ZipCodes")
If c=d then
'Found a match - Do something"

'Then jump to the next Employee
Exit For
End If
Next D
Next C
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top