Counting Entries in two lists
 

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...
--
Thanks,
MarkN

Counting Entries in two lists
 

I would try using "PIVOT Tables"
One pivot table for each sheet, (this gives you a fast count of all
your different values"
Then copy the pivot table results and then use the "PASTE" VALUES
option to paste the values into a separate sheet (use 1 sheet to paste
both results of the pivot table) then create 1 more pivot table and you
should be able to get your results.
If you are not familiar with pivot tables use a small example like the
one below and try it. It is critical that you place the information in
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 82


--
wjohnson
------------------------------------------------------------------------
wjohnson's Profile: http://www.hightechtalks.com/m390
View this thread: http://www.hightechtalks.com/t2297473

Counting Entries in two lists
 

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.
--
Thanks again,
MarkN


"wjohnson" wrote:


I would try using "PIVOT Tables"
One pivot table for each sheet, (this gives you a fast count of all
your different values"
Then copy the pivot table results and then use the "PASTE" VALUES
option to paste the values into a separate sheet (use 1 sheet to paste
both results of the pivot table) then create 1 more pivot table and you
should be able to get your results.
If you are not familiar with pivot tables use a small example like the
one below and try it. It is critical that you place the information in
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 82


--
wjohnson
------------------------------------------------------------------------
wjohnson's Profile: http://www.hightechtalks.com/m390
View this thread: http://www.hightechtalks.com/t2297473