Counting Entries in two lists

[MarkN]

Hello,

I have two lists on two sheets in one book (two lists were required because
I have about 85,000 records). One column in each list contains 'Class Codes'.
I want to produce a report that counts the combined total of each class codes
across both lists. The problem is that while most class codes occur in both
lists, there are some unique class codes in each list.

Is there a straight forward way of doing this? At the moment I am extracting
the unique values from each list, using conditional formats to find items
that occur in one list an not the other and then using the countif function
to sum each item. This works but it seems like the long way around this
problem.

Any help would be appreciated...

 

[wjohnson]

I would try using "PIVOT Tables"
One pivot table for each sheet, (this gives you a fast count of al
your different values"
Then copy the pivot table results and then use the "PASTE" VALUE
option to paste the values into a separate sheet (use 1 sheet to past
both results of the pivot table) then create 1 more pivot table and yo
should be able to get your results.
If you are not familiar with pivot tables use a small example like th
one below and try it. It is critical that you place the information i
the correct location of the pivot table to get the results you want
Pivot Table is located under "DATA" on the Menu bar.

Part 1 - here is what the table looks like
Item Quantity
A 10
B 12
C 15
D 20
C 25

Pivot Table Results
A 10
B 12
C 40
D 20
Grand Total 8

 

[MarkN]

Thanks very much for this, I am familiar with pivot tables, have tried it and
it works. I seem to have a knack of finding quite complex solutions to
relatively simple problems.