Cross Reference 2 files with No matching columns

Question

Hi,

I need to somehow combine 2 completely different files of the same data to find out a true number of transactions that occurred. I've attached 2 sample files as well.

I am trying to find the Invoice # and Amounts (Column K & M) from Report 1 on Report 2 (Column L) to show that the 2 systems are syncing.

What I have been able to figure out is Column K in Report 1 agrees to Column L in report 2. The problem is the wording or cadence is not always an exact match. There may be some letters missing at the end but is probably at least an 80% match. (See example screenshots below)

One particular one (Sprocket), I have only been able to cross reference to report 2 by Ledger Debit or Credit Amount and Date seems like.

Not sure where to even go next on this one.

Please help!

Report 2 - Sample.xlsx

Report 1 - Sample.xlsx

Gina2021 · Accepted Answer

@katonyaj it looks like you have a REGEX need and I look forward to learning along side you!

To get started, I put both reports into a textbox input so it can be within the workflow, and filtered the columns you identified down to contains "crown".  I find that limiting things down helps me see my problem more clearly.  Here's a snippet and starter workflow for the community to take over.

Qiu · Accepted Answer

@katonyaj

I had to exclude the particular one (Sprocket) first.

As a start, I am using equal or contain one by the other function, maybe it will work for you?