Help on matching columns with many identical data

[blizzstorm]

Hi all,

Need your kind help in this problem, not sure where to start.

to describe my issue in a simple way: I have two columns with tons of identical data and i need to match each datacell in column A with an identical datacell in column b.

The thing is there are many identical data such as "1000" each "1000" needs to match with another "1000" in column B and i need to display whats left unmatched eg. 5 "1000" in column A and 6 "1000" in column B should show a leftover of 1 "1000" for column B and vice versa.

Hope you guys can point me a direction where i can follow.

Appreciate it alot!

 

[blizzstorm]

Another example:

Column A
--------
1000
1000
1000
999
999
444
444

Column B
---------
444
999
444
1000
1000
1000
1000

So display should be showing unmatched "Column A: 999" & "column B:1000"

Doing this i would like to preserve the raw data as well

 

[blizzstorm]

any ideas on this for me to start on?

 

[Joe4]

I would probably actually do this in Microsoft Access.

I would do an Aggregate Query of each table which counts the instances of each value.

I would then perform a few queries against these two Aggregate Queries:
1. An unmatched query of the values from one table to the other;
2. An unmatched query like the first going the other way;
3. A matched query between the two (matching on values) and then subtracting the number of one instances in one table from the number of instances in the other (and adding criteria to only display this if the difference is something other than 0).

I believe that you may be able to do something similar in Excel using Pivot Tables (or Subtotals) and maybe some VLOOKUPs (though Pivot Tables really aren't my forte). So you may want to investigate Excel's built-in help on Pivot Tables and or Subtotals.