+ Reply to Thread
Results 1 to 16 of 16

Index Match (multiple criteria) Skipping Duplicates & Returning Only Unique Values

  1. #1
    Registered User
    Join Date
    10-07-2014
    Location
    escondido
    MS-Off Ver
    2016
    Posts
    10

    Index Match (multiple criteria) Skipping Duplicates & Returning Only Unique Values

    Hi, I have been desperately trying to figure this one out. I have assigned a unique value to a set of numbers. I am trying to return the unique value based on two criteria (Dollar amount & Period). The problem i am have is that there could be more that one occurrence where the dollar amount and the period are the same. When this is the case the unique value comes back the same (the first occurrence). I need the formula to skip if the unique value that has already been used and return the next based on the same criteria. I have attached my document. Very simple spreadsheet and very intuitive. Thanks
    Attached Files Attached Files
    Last edited by AliGW; 09-04-2019 at 01:19 AM. Reason: Irrelevant section of title removed: this is a HELP forum!!!

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,748

    Re: HELP! Index Match (multiple criteria) Skipping Duplicates & Returning Only Unique Valu

    It seems to me that you are getting things all mixed up here?

    You have a list of unique ID's (1-6) based on something.

    Then you have a table of data that you use to find those unique ID's, but if a duplicate match is found, you want to use the next unique ID? From your file...
    Need to return Unique Identifer 4, because 2 & 3 has been used
    So what happens if (orginal) ID 3 or 4, seeing as you have now already used them?

    (I would also add a space between each criteria so they are kept separate - how would you tell the difference bwt 35012 (350&12) and 35012 (3051&2)?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    10-07-2014
    Location
    escondido
    MS-Off Ver
    2016
    Posts
    10

    Re: HELP! Index Match (multiple criteria) Skipping Duplicates & Returning Only Unique Valu

    I assigned the unique identifier myself. Yes if the unique identifier, based on the two criteria, has already been used then i want it to find the next unique identifier that satisfies the same two criteria. I think the excel document explains this better than this reply

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,748

    Re: HELP! Index Match (multiple criteria) Skipping Duplicates & Returning Only Unique Valu

    OK, then I am still not understanding? If it doesnt matter that the ID is a dup, then why even bother with ID's in the 1st place>

  5. #5
    Registered User
    Join Date
    10-07-2014
    Location
    escondido
    MS-Off Ver
    2016
    Posts
    10

    Re: HELP! Index Match (multiple criteria) Skipping Duplicates & Returning Only Unique Valu

    So this is a massive bank reconciliation. Lets say my system shows that there are four $400 deposits in March. Lets say the bank has 3 $400 deposits in March. The result of my index match, as it stands now, will return the same unique identifier for each four system deposits. The only way for me to know that there is an outstanding deposit, not hitting the bank, is to not reuse unique identifiers. this will expose that there is four system deposits but only 3 bank deposits. Thanks for your help Signing off for the night

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,875

    Re: HELP! Index Match (multiple criteria) Skipping Duplicates & Returning Only Unique Valu

    Try a different tack. Use this in F4 copied down:

    =IF(COUNTIFS(B$4:B4,B4,C$4:C4,C4)>COUNTIFS($G$15:$G$20,B4,$D$15:$D$20,C4),"Not Reconciled","Reconciled")

    Excel 2016 (Windows) 32 bit
    B
    C
    D
    E
    F
    3
    Amount
    Month Date Deposit Detail Unique Identifier
    4
    350.00
    5
    31/05/2019
    DEPSIT0000473
    Reconciled
    5
    350.00
    6
    15/06/2019
    DEPSIT0000103
    Reconciled
    6
    350.00
    6
    15/06/2019
    DEPSIT0000097
    Reconciled
    7
    350.00
    6
    15/06/2019
    DEPSIT0000103
    Reconciled
    8
    350.00
    6
    18/06/2019
    DEPSIT0000112
    Not Reconciled
    9
    350.00
    7
    18/06/2019
    DEPSIT0000112
    Reconciled
    10
    350.00
    7
    18/06/2019
    DEPSIT0000112
    Reconciled
    11
    350.00
    8
    18/06/2019
    DEPSIT0000112
    Not Reconciled
    Sheet: Sheet1

    Please update your user profile to the version of Excel that you are now using. Thanks.
    Last edited by AliGW; 09-04-2019 at 01:26 AM.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  7. #7
    Registered User
    Join Date
    10-07-2014
    Location
    escondido
    MS-Off Ver
    2016
    Posts
    10

    Re: HELP! Index Match (multiple criteria) Skipping Duplicates & Returning Only Unique Valu

    Thanks AliGW. This is a great approach. The only issue is that i need to also return the bank, the account #, and the deposit detail. There is over 8 bank accounts with statements spanning from jan - june, so i need to be able to have some clue as to which statement to reference in relation to the corresponding deposits. Also, i update my Excel version per your direction. Thanks for your help!!

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    50,748

    Re: Index Match (multiple criteria) Skipping Duplicates & Returning Only Unique Values

    If you use Ali's approach to create the unique ID's then you could then use INDEX/MATCH to pull the data based on those ID's?

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,875

    Re: Index Match (multiple criteria) Skipping Duplicates & Returning Only Unique Values

    Why don't you show us what you want instead of these two columns of 'unique' IDs? What and where is the output meant to be?

  10. #10
    Registered User
    Join Date
    10-07-2014
    Location
    escondido
    MS-Off Ver
    2016
    Posts
    10

    Re: Index Match (multiple criteria) Skipping Duplicates & Returning Only Unique Values

    What I want is for excel to know that when a unique number has already appeared in a column and move on to the next result(unique identifier) that satisfies the same two criteria. This way I can reference which bank and which account. In my original spreadsheet, attached above, my accounting system has four $350 cash receipts, while the bank has only three. I have assigned each bank deposit a "unique identifier". When the period and dollar amount, from the accounting system, matches the first occurrence of the same, in the bank statement, then it needs to return the first unique identifier. That UI should then be taken out of inventory so to speak. when the same amount and period appears again, excel should not pull the same unique identifier, but the next one that satisfies the same two criteria

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,875

    Re: Index Match (multiple criteria) Skipping Duplicates & Returning Only Unique Values

    You are missing my point entirely. You said this:

    The only issue is that i need to also return the bank, the account #, and the deposit detail.
    Never mind your unique identifiers for now. Just show us what you are aiming for ultimately.

  12. #12
    Registered User
    Join Date
    10-07-2014
    Location
    escondido
    MS-Off Ver
    2016
    Posts
    10

    Re: Index Match (multiple criteria) Skipping Duplicates & Returning Only Unique Values

    for index match with two criteria not to return the same result each time when there are multiple matches that meet the criteria. I want something unique that differentiates. I guess a sequential designator or "First Occurrence " or "Second Occurrence" Identifier

  13. #13
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,875

    Re: Index Match (multiple criteria) Skipping Duplicates & Returning Only Unique Values

    I give up. You clearly aren't open to looking at this in a different and probably more efficient way.

  14. #14
    Registered User
    Join Date
    10-07-2014
    Location
    escondido
    MS-Off Ver
    2016
    Posts
    10

    Re: Index Match (multiple criteria) Skipping Duplicates & Returning Only Unique Values

    I am. Obviously I am struggling. My last comment was very clear. No need to keep commenting if you don't understand what I am looking for. Perhaps this is an accounting disconnect. Thanks for your help

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    46,875

    Re: Index Match (multiple criteria) Skipping Duplicates & Returning Only Unique Values

    Ouch!

    Well, I do understand what you want - completely. I just think there will be an easier way to do it than the one you are trying to make work, but unless you show us the OUTCOME you want AFTER getting the unique IDs working, then we can't really advise.

    As I said, if you aren't open to different solutions, then I can't help you. Your comments are very clear, but they don't answer the question.

    Please don't make the assumption that anyone suggesting you do things differently must be an idiot.

    Anyway - I have to go to work now.

  16. #16
    Registered User
    Join Date
    10-07-2014
    Location
    escondido
    MS-Off Ver
    2016
    Posts
    10

    Re: Index Match (multiple criteria) Skipping Duplicates & Returning Only Unique Values

    I am certainly not calling any one an idiot here. I am the one who can't figure this thing out. If there is an easier way, then i am all for it. I appreciate your help. I have attached another example of current state and how i wish my future state could be. I am certainly not attached to array/index/match formulas, as they slow down the process, so any workaround would suffice. Thanks again. sorry if i offended...wasn't my intention
    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)

Similar Threads

  1. index match multiple unique values based on one criteria
    By Clooney003 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-10-2019, 10:03 PM
  2. Replies: 6
    Last Post: 08-21-2018, 05:45 PM
  3. [SOLVED] Index & Match Formula for Unique Values On Multiple Criteria
    By JenMasters84 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-10-2018, 03:01 PM
  4. [SOLVED] Index & Match Formula for Unique Values On Multiple Criteria
    By Neilesh Kumar in forum Excel General
    Replies: 2
    Last Post: 03-24-2017, 10:13 AM
  5. Index/match for multiple criteria when duplicates exist
    By dasranch in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-26-2015, 12:05 AM
  6. [SOLVED] Two criteria Index Match on multiple sheets returning multiple values
    By Joak in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-21-2014, 10:03 AM
  7. [SOLVED] Index match with multiple criteria and returning last value
    By L.Steele in forum Excel General
    Replies: 2
    Last Post: 05-30-2012, 02:27 PM

Tags for this Thread

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