Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Comparing three separate reports
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. |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Comparing three separate reports
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 "Ty Davis" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing Data from three separate reports | New Users to Excel | |||
comparing two separate spreadsheets | Excel Discussion (Misc queries) | |||
Print separate reports with same column heading | Excel Discussion (Misc queries) | |||
Creating separate reports for each Project Manager | Excel Discussion (Misc queries) |