Forum Discussion

g_avinash's avatar
g_avinash
Copper Contributor
Jun 21, 2023

Fuzzy

In Excel, how can I find the corresponding purchase transaction in the purchases table that occurs within the same month as the transaction in the Bank table, where the bank account name is "Catholic Syrian Bank A/c 190001," even if the amounts are not equal?

 

P.S. I have tried Fuzzy add-in, but I get duplicate results.

 

File attached.

 

Thanks in Advance.

excel
office 365
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor
    Jun 23, 2023

    Hi g_avinash 

     

    This makes no sense to me. Did it purely for the exercise with Power Query. Amazingly slow due to custom Fuzzy matching that necessarily leads to inaccurate match

    381 records reconcilied, less than 30% perfectly match...

     

    EDIT: There as still potential duplicates

     

    Rules implemented for records that don't perfectly match:
    #1 [Purchase Date] and [Bank Date] in same MonthYear (as for records that perf. match) AND
      [Purchase Date] <= [Bank Date] AND
      [Puchase Amount] = [Bank Amount] +/- 2%
    #2 If more than 1 record is found, keep the 1st with the smallest difference between [Puchase Amount] and [Bank Amount]

     

    If you want to change the +/- 2% that's in query FuzzyReconcilied:

     

        SelectedRows = Table.AddColumn(Source, "Almost",
        (Bank)=> Table.SelectRows(PurchaseUnrec, each
            ([MonthStart] = Bank[MonthStart]) and
            ([Date] <= Bank[Date]) and
            ([Credit] >= Bank[Debit]*0.98) and ([Credit] <= Bank[Debit]*1.02)
        ),
        type table [Date=date, Purchases = text, Credit=number, IdPurchase=Int64.Type]
    ),

     

     

Resources