thanks, and this workflow will work every time with a new set of invoices....
And if you can..confirm co-pilot is correct in breaking down the steps you advise?
Recommended Step-by-Step Approach
- Assign a Unique Record ID
Each row in your primary dataset (e.g., from <File>Proprietary data.xlsx) should be given a unique recordid. This allows you to track and group results accurately throughout the workflow.
- Split the 'Map' Column into Words
Take the text from the first column (e.g., “GB Unitary Fees 2 Country Related Fees”) and split it into individual words. This transforms each record into multiple rows—one for each word—making it easier to match against your word mapping source.
- Prepare the Word Mapping Source
From your mapping file (e.g., <File>Split_Mappings for discussion .xlsx), extract all relevant words or phrases that represent standardized categories. Assign a recordid to each entry in this source as well.
- Perform a Join
Use a Join tool to match each word from your exploded dataset against the word mapping source. This identifies which words from your raw data appear in the mapping reference.
- Summarize: Count Matches per Record
Use a Summarize tool to:
- Group by recordid from your original dataset.
- Count how many words matched from the mapping source. This gives you a match score for each record.
- Identify the Best Match
Use another Summarize tool to:
- Find the recordid with the maximum match count. This is the record that most closely aligns with your mapping source.
