Forum Discussion
Solved
Fuzzy lookup multiple criteria
Hi,
First entry for a sales record is 26,695 rupees.
Later, when I receive the money, I record a receipt entry for the whole amount (as in sales) or a smaller, roughly equivalent amount. But never more than the number of sales.
Second, my client has three customers with the same name, namely:
29AACFT3901F1ZD THEBIGMARKET
The big market - 29AAIFT8492R1ZU
The Big Market - K1ZW
The Big Market R1Z0
THEBIGMARKETTHINDLU
Now, my match criteria is:
1. NEFT-THE BIG MARKET/N093221900306639-- with
THEBIGMARKET - 29AACFT3901F1ZD
The big market - 29AAIFT8492R1ZU
The Big Market - K1ZW
The Big Market R1Z0
THEBIGMARKETTHINDLU
Sales amount with receipt.
This method needs to be repeated for more than 2,000 items. I've provided this sample file for you.
P.S.: I use office 365
Thanks in Advance.
- there are a few options. I just copy useful formulas to a single workbook so I know where to find them.
- a) no file attached - if it won't let you then try using online sharing like OneDrive, SharePoint or the like
b) there are lots of ways to do approximate matches but they need more criteria. For example I have one that will do the best match of the first x number of characters and return the answer with the most characters matched. That could be tweaked to remove spaces to help in this case
c) You say "First entry for a sales record is ... Later, when I receive the money, I record a receipt entry" and then you give an example that the correct match in that example based on amount and date is a receipt BEFORE the sales record.I have attached the requisite file.
I'm trying to say that the sale entry occurs first. Take May 5 as an example; however, I will receive payment (a receipt entry) on May 11. It's possible that the amount I receive will be the same as or even less than the sale price. because stock loss may cause the debtor to forfeit some money. In any case, it will be a single payment rather than several smaller ones.
Second, parties with the same name will exist. Date and sum, however, will be the requirements to match.
g_avinash But my point is that in your example:
the highlighted desired Output is a RECEIPT that happens on 28-04-2023 which is BEFORE the sales item you were searching for which happened on 01-05-2023. Was this a bad example or is the explanation incorrect?
As for the NearMatch I have attached your workbook with it added BELOW (note I edited this post and remove the file to prevent confusion). Here is an example how it works:
notice how it found the "ve" and then "smo" in "wave smooth". It would find that even if the text in C11 was "vesmoxyz123" since that was the 'best' match of all the options. you can also add the min_match parameter to say it must match at least X characters otherwise ignore the results. Play with it and see if that helps.
