Comparing Account Number

I have a spreadsheet where the data is imported into sheet "TB Consolidation". I have tried to set up a formula on Sheet1 (existing account numbers) to compare these to TB Consolidation , and if new account numbers have been added in the "TB Consolidation" then to indicate "New Account numbers"

However the formula returns "New account" even though the account number" appears in both sheet1 and TB Consolidation" I am trying to see what account numbers have been added in TB Consolidation. I also need to determine which account numbers are existing i.e sheet1 that is not on Sheet TB Consolidation.

Your assistance in this regard is most appreciated

Howard,

Try this formula in Sheet1 B1 and fill down.

Code:

---

=IF(ISNA(VLOOKUP(A1,'TB Consolidation'!$A$1:$A$20,1,FALSE)),"New Account","")

---

Quote:

---

Originally Posted by Charles

Howard,

Try this formula in Sheet1 B1 and fill down.

Code:

---

=IF(ISNA(VLOOKUP(A1,'TB Consolidation'!$A$1:$A$20,1,FALSE)),"New Account","")

---

---

Hi Charles

Thanks for the reply and your assistance. Will this formula determine whether tere are account numbers that appear onTB Consolidation that are not on sheet 1 as well as account numbers that are on TB Consolidation , but not on Sheet1. If not, kindly amend your formula

Regards

Howard

Hi Charles

I have set up a Vlookup to ccompare account numbers, but it is not working. The reason it is not working is that the data on the sheets has an ' before the numbers. It would be appreciated if you could amend the formula to incorporate this or set up a macro to remove the ' from the numbers

Attached please find some sample data

Hi,

Try this =IF(ISERROR(VLOOKUP(TEXT(B1,0),'TB Consolidation'!A:A,1,FALSE)),"NEW","")

Hi,

I'm not sure what you mean by "From" and "Before"?
Are you wanting to delete the number if it's in the TB sheet, but not in Sheet1?
Delete sheet1 info?

Hi Charles

Thanks for the help, formula works perfecly