Matt_in_CA
New Member
- Joined
- Feb 4, 2015
- Messages
- 5
Hello,
Does anyone have a good solution, or know a function, that will deploy VLOOKUP but match each value only once?
For example, List 1 has these items: BOB, JANE, MARY, BILL, SAM.
List 2 has these items: BOB, JANE, MARY, BOB, JANE, MARY.
I want to see which items in List 2 have a match in List 1. (Exist on List 2 and also on List 1.) But, I want to match each List 2 item only once. That is, because there is only one "BOB" on List 1, you can only use "BOB" once to match with List 2.
If I use VLOOKUP, it will show a match for all the items on List 2 because they each do have a match over in List 1.
I can solve this by getting the initial result with VLOOKUP, and then working those results with ISNUMBER or MATCH to manually sort out the duplicates.
Or, I could write a VBA loop that will find each match, and then cross the matched List 1 value off the list as I match it.
But, I'm wondering if someone knows a function that might do this "exclusive" "only once" match easily.
Background: This is for reconciling cash bank statements. First step is that you find out what transactions you have at the bank that you also have in your books. Whatever is left over is the difference you have to reconcile. Problem is, if you use VLOOKUP, Excel might match it two times, thus missing transactions that have to be reconciled.
Thanks,
Matt
Does anyone have a good solution, or know a function, that will deploy VLOOKUP but match each value only once?
For example, List 1 has these items: BOB, JANE, MARY, BILL, SAM.
List 2 has these items: BOB, JANE, MARY, BOB, JANE, MARY.
I want to see which items in List 2 have a match in List 1. (Exist on List 2 and also on List 1.) But, I want to match each List 2 item only once. That is, because there is only one "BOB" on List 1, you can only use "BOB" once to match with List 2.
If I use VLOOKUP, it will show a match for all the items on List 2 because they each do have a match over in List 1.
I can solve this by getting the initial result with VLOOKUP, and then working those results with ISNUMBER or MATCH to manually sort out the duplicates.
Or, I could write a VBA loop that will find each match, and then cross the matched List 1 value off the list as I match it.
But, I'm wondering if someone knows a function that might do this "exclusive" "only once" match easily.
Background: This is for reconciling cash bank statements. First step is that you find out what transactions you have at the bank that you also have in your books. Whatever is left over is the difference you have to reconcile. Problem is, if you use VLOOKUP, Excel might match it two times, thus missing transactions that have to be reconciled.
Thanks,
Matt
