Matching transactions where one data set includes required duplicates

Question

I have two data sets that are based on the same underlying information but I unfortunately do not own them so cannot write a report to get all the elements I need in a single report. Data A shows transactional sales details which include a breakdown of sales and taxes. Data B shows transactional sales details by tender type.  I need to be able to match transactions from data set A to data set B so I can ascribe the tender type to each transaction and the split between sales and sales tax.  I am able to do this in most cases using a join function except when multiple tender types were utilized.  For example someone paid for their order with both credit and cash. In those cases, Set A duplicates in the join so I end up with too many transactions that will require significant manipulation on the back end.

Sample data and the desired output are attached.

Sample Data.xlsx

Dsmithhisler · Answer

Thank you for the reply but the summarize unfortunately loses the description for tender type which I need to be able to retain.  I think I have it figured out now though using a Unique tool to remove the duplicates, joining what is left, and then appending with the duplicate values.

Dtownboyerk · Answer

Joining the 2 sets of data together on - Date to Tender_date / Store_id to Tender store id / check-num to Check number - creates the file format you are looking for then chasing that with a multi row formula on each fields you want to reflect the second row to reflect null on will do the trick.

kelsey_kincaid · Answer

Hi @Dsmithhisler ,

Have you tried using a summarize tool after Set B to sum the transaction amount by Date, Store ID & Check Number? I think that would fix your problem. Something like this: