+ Reply to Thread
Results 1 to 14 of 14

Vlookup and subtract when second occurrence of ID

  1. #1
    Registered User
    Join Date
    04-19-2010
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    23

    Vlookup and subtract when second occurrence of ID

    Hello

    I am trying, and failing, to combine two codes.

    The first is a vlookup I got from here, it subtracts from a time value the time value next to the unique ID to its left matched in the data on the other sheet.

    Whilst this is good, and I could probably use it to complete most of the work, I am trying (mainly out of curiosity) to go further and have it automatically go to the next instance of the ID if the ID is not unique. For this I found a code on the other forum linked below, which seems to return the next match. It's a little beyond what I know and I'm struggling to understand it enough to alter it's use.

    If anyone feels like giving this a shot, many thanks in advance.

    http://www.excelforum.com/excel-gene...-function.html
    http://www.mrexcel.com/forum/showthread.php?t=11137
    Attached Files Attached Files
    Last edited by NBVC; 06-15-2010 at 10:15 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Vlookup and subtract when second occurrence of ID

    Here is a way to find Second Match without volatile OFFSET function:

    Please Login or Register  to view this content.
    This will return 0 if there is no 2nd match.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    04-19-2010
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Vlookup and subtract when second occurrence of ID

    Apologies, I'm sure that what you've put should answer my query but I'm stumped.

    I've re-uploaded with your code in place, it does occasionally return some values that I want it to - but it does so in the wrong cell. For example, F2 should be in F3, F5 in F7, and F7 in F8...

    If it did this I could then do the difference calculation in the next column along - the main bit doesn't really have to do anything more than return the time from the next matching ID... though doing even that is apparently beyond me.

    Anyway, thanks for the effort - I'll keep reading tutorials, but if you are able to help me further it would be wonderful.
    Attached Files Attached Files

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Vlookup and subtract when second occurrence of ID

    In that case, try instead:

    Please Login or Register  to view this content.
    confirmed with CTRL+SHIFT+ENTER not just ENTER and copy down.

    This will also look for 3rd or 4th match, if it exists...
    Last edited by NBVC; 06-08-2010 at 12:09 PM. Reason: Fixed formula

  5. #5
    Registered User
    Join Date
    04-19-2010
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Vlookup and subtract when second occurrence of ID

    That is some kind of arcane magic I don't understand, but it appears to work... (had to google 'ctrl+shift+enter')

    May I ask, what part of that formula indicates that it will only work for up to 4 repeats? It may be the case that over a day there are 10 or even 20 repeats... this formula is fantastic 99% of the time if it does up to 4, I'm just trying to understand how it works.

    Thanks very much.

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Vlookup and subtract when second occurrence of ID

    The last part: COUNTIF($A$2:$A2,A2)

    is what determines the "match number".

    So what it does is count how many times, the value found in A2 is found in the range from A$2:A2 and that is the k value for the SMALL function.. so it looks for the kth match.... and expects to find one in your original list.. if there are less matches in the original database than there are in your summary, then you will get errors.

  7. #7
    Registered User
    Join Date
    04-19-2010
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Vlookup and subtract when second occurrence of ID

    Update;

    I was able to make the code suggested by NBVC work on a small sample of the data, but doing the exact same steps and then trying again multiple times and attempting to add it in different ways (just pasting and then editing and ctrl+shift+enter, or writing the whole thing out manually) I just get either #NUM where I'm not expecting data or #VALUE where I am expecting data.

    I've attached the dataset I'm working on, I've used a more basic code to go through the data and then I'm manually going through where it's not matched to the correct instance of the ID. It's not exactly essential that I learn how to do this using the ctrl+shift+enter code, but I'm quite interested and it would save me time in the future.

    Any further help greatly appreciated.
    Attached Files Attached Files

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Vlookup and subtract when second occurrence of ID

    When you enter the formula to grab the first instance, you need to start your databases at the first row... so instead of $A$2:$A2500 (which is the last row), you need to start by entering $A$2:$A2

    like so:

    Please Login or Register  to view this content.
    then confirm with CSE and copy down.

  9. #9
    Registered User
    Join Date
    04-19-2010
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Vlookup and subtract when second occurrence of ID

    Aha! I wasn't 100% sure about what bit did which, what you posted above didn't work so I had a look at the one that did to compare and then got this;

    =ABS(B2-INDEX(Primark!$B$2:$B$2500,SMALL(IF(Primark!$A$2:$A$2500=A2,ROW(Primark!$A$2:$A$2500)-ROW(Primark!$A$2)+1),COUNTIF($A$2:$A2,A2))))

    Which does work.

    Thank you very much. That's a genius code... gonna have to try and learn to understand it fully.

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: [SOLVED] Vlookup and subtract when second occurrence of ID

    You are correct.. I got carried away with the range reductions....

  11. #11
    Registered User
    Join Date
    04-19-2010
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Vlookup and subtract when second occurrence of ID

    Actually, looking through it - doesn't seem to get every single one. Very useful for quickly doing most of what I had left to manually do, but it misses some. Will reupload for your interest, take a look at rows 1010, 1012, and 1014.

    It's done 1010 correctly, it's done 1012 correctly where the basic calculation didn't (and I would have missed the difference due to it's size - actual time to compare to is 13.05), it's missed 1014 and compared to the wrong value.

    Also it seems to trail off and work less efficiently in the last thousand rows?

    (Or you might have to quickly stick the code down yourself if you're interested - uploader isn't currently working)

    Whatever the case, this is useful to use and has been an education - thanking you kind sir.

  12. #12
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Vlookup and subtract when second occurrence of ID

    I am not sure I understand.. those three rows don't have matches in the other sheet.. so you should not get results...

    In order to remove errors and invalid entries, try this fomula instead:

    Please Login or Register  to view this content.
    CSE confirmed and copied down.

    Perhaps a more efficient way to do this based on the size of your database is to add a helper column and eliminate CSE array formulas...

    so in F2 of the Primark sheet add:
    Please Login or Register  to view this content.
    and copy down, then in the Harvey sheet in row 2, use formula:

    Please Login or Register  to view this content.
    copied down.

  13. #13
    Registered User
    Join Date
    04-19-2010
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    23

    Re: Vlookup and subtract when second occurrence of ID

    Ah, I think I'm a little confused - would there be any chance of you uploading your version of the document to show me what you mean? Those 3 rows I mentioned (in the Harvey sheet) definitely have matches in the Primark sheet... I copied the IDs and then searched using Ctrl + F...

    WX06OMY is done correctly by the code you provided. (Row 1010)
    3733ML is done correctly by the code you provided. (Row 1012). This is brilliant as otherwise it showed an 8 minute difference that I wouldn't have spotted as wrong.
    WX59BZJ is not done correctly by the code... (Row 1014). It should be a 5 minute journey time, whereas the array formula shows me 1 hour 21 minutes.

    It's best not to remove the errors, as it's quite easy for me to go through and find where data has been incorrectly entered and then amend it... the array formula seems to handle this fine and updates when to correctly reflect the situation (where it works properly). Or is it not meant to be used with data that is then being altered?

    Thanks

  14. #14
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Vlookup and subtract when second occurrence of ID

    See attached
    Attached Files Attached Files

+ 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