Pulling differences between columns of two worksheets

[BenLewis11]

In Excel 2007, I am working on a Loan Portfolio Report. The report compares all loans (number, officer, amounts, etc.) each Monday with the same information from the prior week.

It is important for me to be able to show which loans are new (not on the prior week's report) and which are gone (on the prior week's report and not on this week's).

Is there a way to select a column of this week's data (ex. Customer Name) and a column of that same data in last week's worksheet and have a small section of a worksheet that will tell me which Customer Names are new and which are gone? If so, what is the function and is there a tutorial or some help that will assist in adding this function to my report.

Thanks for any assistance.

 

[JoeMo]

Here's one way to do this - the formula in C2 is copied down to cover the entire list of this week's names:

Excel Workbook
	A	B	C
1	last week	this week	new this week
2	Tom	Ron	New
3	Mary	Jane
4	Jane	Tom
5	Paul	Harry	New
6	John	Alice
7	Alice	Charles	New
8		Tim	New
Sheet1

 

[BenLewis11]

Thanks JoeMo,

This worked perfectly. Now to be able to organize this, I'd like to have a seperate smaller section on a different worksheet where the important information would flow to. For example, if cell C2 is New, enter cell B2 (Ron's name) in seperate worksheet cell A2.

The problem arises though where I have about 120 lines of data to compare old versus new and fit it into a space about 10 rows high. Typically there are only 1 or 2 "new" results per report, so the "new" results would fit into the 10 spaces. For example, I might have a "new" result in row 48 and row 60, and I would need the important info from these results to get placed in cells A2 and A3, respectively, of another spreadsheet.

Hope this makes sense. Thanks again for the help.

 

[JoeMo]

Here's one way to consolidate the new names. You can easily adapt this to a new speadsheet to hold the new names. Note that I've modified the formula in C2. C2 copy down, F2 copy down to cover your 10 rows.:

Excel Workbook
	A	B	C	D	E
1	last week	this week	new this week		Consolidated list of new names
2	Tom	Ron	1		Ron
3	Mary	Jane			Harry
4	Jane	Tom			Charles
5	Paul	Harry	2		Tim
6	John	Alice
7	Alice	Charles	3
8		Tim	4
Sheet1

 

[BenLewis11]

Great Tips! This worked perfectly. Thank you so much.