+ Reply to Thread
Results 1 to 17 of 17

Find missing values - based on looking up a list, comparing against index values

  1. #1
    Registered User
    Join Date
    09-03-2012
    Location
    Online
    MS-Off Ver
    Excel 2016
    Posts
    48

    Find missing values - based on looking up a list, comparing against index values

    Hi all,

    I've got a particularly curly problem that I've been unable to solve so far. Was hoping someone here might be able to shed a bit more light.

    I have 2 lists that I need to compare - with a twist.
    List A contains a name in the first column and a code in the second column. The name may be repeated multiple times, and each time it is repeated it may be assigned a different code.
    List B contains a list of all the possible codes that may be used in list A column 2.

    What I'd like to be able to do is discover which codes that exist in List B have not been used for each name in List A.
    Essentially List B could be treated like a VLookup table (in my mind) - I'm just not sure how to write this up.

    Let me illustrate for clarities sake - notice how in the REUSLTS section only the missing codes are displayed

    MissingCodesV2.jpg

    Thhank you very much for all your help - and please don't be afraid to let me know if I've missed out on something.
    Attached Images Attached Images

  2. #2
    Valued Forum Contributor smuzoen's Avatar
    Join Date
    10-28-2011
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2003/2007/2010
    Posts
    610

    Re: Find missing values - based on looking up a list, comparing against index values

    This is one method to generate missing combinations - see attached workbook
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Hope this helps.
    Anthony
    Pack my box with five dozen liquor jugs
    PS: Remember to mark your questions as Solved once you are satisfied. Please rate the answer(s) by selecting the Star in the lower left next to the Triangle. It is appreciated?

  3. #3
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Find missing values - based on looking up a list, comparing against index values

    an option
    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    09-03-2012
    Location
    Online
    MS-Off Ver
    Excel 2016
    Posts
    48

    Re: Find missing values - based on looking up a list, comparing against index values

    You're both awesome!

    Thank you very much

  5. #5
    Registered User
    Join Date
    09-03-2012
    Location
    Online
    MS-Off Ver
    Excel 2016
    Posts
    48

    Re: Find missing values - based on looking up a list, comparing against index values

    nilem: You'r script is very elegant, but I'm having trouble modifying two small things.

    How would I place the name next to every code? (In my initial post there's actually 2 pictures - sorry)

    An also - lets say that I added in 3 more columns (gps coords) in columns C, D, E. Is it possible to show the missing codes AND copy the GPS cords from Columns C, D, E across too? (I should have asked this first up - thought it would be a simple edit)

    \1
    Last edited by anakaine; 01-08-2013 at 09:47 PM. Reason: Extra questions

  6. #6
    Valued Forum Contributor smuzoen's Avatar
    Join Date
    10-28-2011
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2003/2007/2010
    Posts
    610

    Re: Find missing values - based on looking up a list, comparing against index values

    Nice succinct code nilem!

  7. #7
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Find missing values - based on looking up a list, comparing against index values

    try
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    09-03-2012
    Location
    Online
    MS-Off Ver
    Excel 2016
    Posts
    48

    Re: Find missing values - based on looking up a list, comparing against index values

    nilem,

    You're a star! Thank you very much

  9. #9
    Registered User
    Join Date
    09-03-2012
    Location
    Online
    MS-Off Ver
    Excel 2016
    Posts
    48

    Re: Find missing values - based on looking up a list, comparing against index values

    just to let you know nilem - in case you find the issue in the future - This script seems to be running in to a problem where after 836 lines of source data (or 1704 lines of results data) it stops processing. No errors are displayed.

    I was able to identify this by removing some lines from my source data and observing that more data at the bottom of the list was processed.

    Possibly a memory related issue with the amount of data being transformed at once?

  10. #10
    Valued Forum Contributor smuzoen's Avatar
    Join Date
    10-28-2011
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2003/2007/2010
    Posts
    610

    Re: Find missing values - based on looking up a list, comparing against index values

    Just out of curiosity does my script run into the same problem (assuming it actually generates the results you require)

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Find missing values - based on looking up a list, comparing against index values

    Try this
    Please Login or Register  to view this content.
    Attached Files Attached Files

  12. #12
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Find missing values - based on looking up a list, comparing against index values

    Quote Originally Posted by anakaine View Post
    ... This script seems to be running in to a problem where after 836 lines of source data (or 1704 lines of results data) it stops processing...
    Anakaine, could you show the file in which the error occurs?

  13. #13
    Registered User
    Join Date
    09-03-2012
    Location
    Online
    MS-Off Ver
    Excel 2016
    Posts
    48

    Re: Find missing values - based on looking up a list, comparing against index values

    Sorry this took a while to reply to Nilem, Smuzoen.

    Nilem, seems as though your script was counting the number of cells that had values in them, and was not counting cells that did not have values. Thus if I had 400 cells to look through, but only 380 contained text - then the resulting output would finish 20 lines too short.

  14. #14
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Find missing values - based on looking up a list, comparing against index values

    Anakaine, please attach your file

  15. #15
    Registered User
    Join Date
    09-03-2012
    Location
    Online
    MS-Off Ver
    Excel 2016
    Posts
    48

    Re: Find missing values - based on looking up a list, comparing against index values

    ertert_broken_data.xlsmnilem,

    I had to reconstruct the broken data to demonstrate for you. See sheet 2 of the attached list.
    Note how if you remove lines that have no "Seam code" the macro will finish on Name 816 instead of 790

  16. #16
    Forum Expert nilem's Avatar
    Join Date
    10-22-2011
    Location
    Ufa, Russia
    MS-Off Ver
    2013
    Posts
    3,377

    Re: Find missing values - based on looking up a list, comparing against index values

    Hi Anakaine,
    I hope all is well now
    Note there are different values ​​of "Elevation" in the same "Name" (see for example row 53-56)
    Attached Files Attached Files

  17. #17
    Registered User
    Join Date
    09-03-2012
    Location
    Online
    MS-Off Ver
    Excel 2016
    Posts
    48

    Re: Find missing values - based on looking up a list, comparing against index values

    Thank you nilem,

    Also thank you for pointing out the discrepancy in elevation - that would have proved to be a problem down the track!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1