Compare 2 Sheets to Find Different Values

**MotoDave** · 11-21-2012, 01:03 PM

Hello,

I have a workbook with 2 sheets. The first sheet is Deposits and the second is Loans. Both sheets are sorted by Tax ID number. The Deposit sheet has 5729 records and the Loan sheet has 3202. I need to compare the Deposit sheet to the Loan sheet to find customers who have a deposit account but DO NOT have a loan account. I want to get the results for customers without a loan on a third sheet named Dep_Cust_With_No_Loan. The Deposit sheet has one more column than the Loan sheet but beside that the 2 sheets are basically the same. It's been a while since I've worked with Excel. Is there a LOOKUP formula or other means to accomplish this task? Any help will be greatly appreciated.

Thank you.

Dave

**Hyperdude** · 11-21-2012, 09:05 PM

Hi Moto,

You could try this is in you 3rd sheet provided that the Tax ID number is unique. Would be great if you could upload a sample workbook.

=IFERROR(VLOOKUP(Sheet1!$A4,Sheet2!$A$1:$A$3,1,0),Sheet1!$A4)

**MotoDave** · 11-23-2012, 11:58 AM

Hi Hyperdude,

I have attached an example spreadsheet. You’ll find 10 names on the CAP_Deposit sheet and 7 on the Loans sheet. Names such as John Doe and Pete Moss are on both lists so they have a deposit account and a loan account; these 2 customers are to be ignored since I am looking for customers that have deposit accounts but no loan accounts. The other names on the CAP_Deposit list (Frank Furter, Calvin Klein, John Q. Public, Jane Doe and Arthur Fonzarelli) are all have deposit accounts but do not have any Loans with the bank. I need to bring the names above in the parenthesis over to the 3rd tab named Cap_Dep_Cust_With_No_Loan. You will notice some customers on the deposits list may be listed more than once since they have more than one deposit account. This is a common scenario.

Please let me know if you have any questions. I appreciate your assistance on this report.
Thank you.

**Fotis1991** · 11-23-2012, 12:14 PM

Take a look to the example,pls.

**MotoDave** · 11-23-2012, 01:59 PM

Hi Fotis,

It looks like your array formulas are working in the example spreadsheet. But when I try to copy and paste them into my real spreadsheet things get a little crazy. Is there a special way to copy & paste array's? If I do a simple copy & paste the formula copies over but I lose the curly brackets at the beginning and end of the array formula. Also, I cannot copy the array formula down a column. The formula does show in the formula bar but no value shows in the cell. I cannot choose "Formulas" from the paste menu because it is grayed out. I'm probably doing something wrong but I don't know what it is.

Thanks

**Fotis1991** · 11-24-2012, 03:48 AM

When we use an Array formula we need to confirm this, using Control+Shift+Enter, rather than just Enter.

Also pls, notice that in the first sheet there is a helper(hidden) column.

You need to copy and paste just the first cell and then drag down and across.

Take a look in this article for understanding the way that these formulas works.

http://www.cpearson.com/excel/ArrayFormulas.aspx