Results 1 to 5 of 5

Thread: Find Missing Values By Comparing Two Columns / Find Missing Data In Column

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

    Find Missing Values By Comparing Two Columns / Find Missing Data In Column

    I have a workbook containing several sheets. I would like to compare the account numbers on Sheet1 Col C with sheet imported data Col A

    I would like a formula in sheet 1 Col F to compare the account numbers on sheet1 Col C to sheet "imported data" Col A and where there is an account number on sheet "imported data" Col A and this does not appear on sheet1 Col C, then to indicate the account number in Col F and the value in Col G

    I would also like to compare imported data Col A with Sheet1 Col C and to do a similar formula in Sheet imported data Col D and indicate the value in Col E
    Attached Files Attached Files

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Sheet1!F2=IFERROR(INDEX('Imported Data'!$A$2:$A$34,SMALL(IF(ISERROR(MATCH('Imported Data'!$A$2:$A$34,Sheet1!$C$2:$C$17,0)),ROW('Import ed Data'!$A$2:$A$34)-1,""),ROW('Imported Data'!$A2)-1)),"") as array formula

    You are replicate the rest for your other cells.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    13
    Thanks for the help, much appreciated

    There are two account numbers in the sample file appearing on sheet imported data and not on sheet1. However, the formula only indicates account 1130 as not appearing on sheet1, whereas account 1130 and 1131 do not appear on sheet1 (they appear on sheet imported data)

    It would be appreciated if you could check the formula in the attached workbook and amend your formula accordingly
    Attached Files Attached Files
    Last edited by Howardc; 04-05-2013 at 09:38 AM.

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Just enter in the first cell as array. And drag down. Do not enter the formulae in one go for all the cells.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    13
    Thanks for the help

Similar Threads

  1. Replies: 8
    Last Post: 05-21-2013, 06:34 AM
  2. Check for Missing Attachment and Subject in Outlook
    By Transformer in forum Tips, Tricks & Downloads (No Questions)
    Replies: 0
    Last Post: 05-17-2013, 12:32 AM
  3. Find Last Used Row In Column
    By Rasm in forum Excel and VBA Tips and Tricks
    Replies: 2
    Last Post: 06-04-2012, 07:40 AM
  4. Find duplicate values
    By excel_learner in forum Excel Help
    Replies: 4
    Last Post: 10-24-2011, 12:10 PM
  5. Missing MSComctllib
    By Rasm in forum Excel Help
    Replies: 3
    Last Post: 04-05-2011, 09:45 PM

Posting Permissions

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