Comparing three separate reports

[Ty Davis]

I'm trying to take three separate reports and extract the data that is the
same in column a and b. This report will pull the entire line of data based
on a and b being the same on all three reports.

Example is a financial institution looking for customers by name and account
# that appear delinquent for three consecutive months.

 

[Bernie Deitrick]

Ty,

Create a new sheet. We will use columns A through C will be used in the process for finding the
duplicates, so we'll set those up first.

Enter "Table" in cell A1, and enter "Key Value" in cell B1, and enter "Appears in 3" in C1.

Copy all three tables, with the first starting in cell D1. (Only copy the headers of the first
table.) Then copy the second and third tables below the first, also starting in column D.

In column A, enter the month name (for example) that corresponds to the data in those rows.

In cell B2, enter the formula

=D2&E2

and copy down. (This combines the values from the old columns A and B).

Then in C2, enter the formula

=COUNTIF(B:B,B2)=3

and copy down to match your table. This will return TRUE if the key value appears three times (which
should be once per month).

Then select everything, use Data / Filter / Autofilter, and choose TRUE from the drop down on column
C. Then you can select the values from column B's dropdown to view specifics for each of the names
and accounts that appeared in all three reports.

HTH,
Bernie
MS Excel MVP