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

1. ## 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

2. ## 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)?

3. ## 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. ## 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. ## 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. ## 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.

7. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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.

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. ## 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

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