Results 1 to 7 of 7

Thread: Comparing Account Number

  1. #1
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    13

    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
    Attached Files Attached Files

  2. #2
    Member Charles's Avatar
    Join Date
    Aug 2012
    Posts
    63
    Rep Power
    13
    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","")
    If you like the answer, please click " * " below Member name to say thanks.

  3. #3
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    13
    Quote Originally Posted by Charles View Post
    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

  4. #4
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    13
    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
    Attached Files Attached Files

  5. #5
    Junior Member
    Join Date
    Mar 2013
    Posts
    3
    Rep Power
    0
    Hi,

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

  6. #6
    Member Charles's Avatar
    Join Date
    Aug 2012
    Posts
    63
    Rep Power
    13
    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?
    If you like the answer, please click " * " below Member name to say thanks.

  7. #7
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    13
    Hi Charles

    Thanks for the help, formula works perfecly

Similar Threads

  1. Comparing Mortgage Loans
    By Flupsie in forum Excel Help
    Replies: 3
    Last Post: 02-20-2020, 11:48 AM
  2. Replies: 3
    Last Post: 04-05-2013, 08:24 AM
  3. Log In To Email Account Using VBA
    By Excel Fox in forum Download Center
    Replies: 1
    Last Post: 12-14-2012, 11:59 PM
  4. Replies: 0
    Last Post: 09-07-2012, 09:10 PM
  5. Replies: 4
    Last Post: 07-17-2012, 12:49 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •