Reconciling Irregular or Uneven records of data

After days of debating whether to bring this here for discussion or not, decided to give it a try. Thank you in advance for reading through this. I have close to 25 different versions of workflow to get the output i want but none of them give me all the results i want. hence the plea for help.

Simply put - we need to compare rows of data between two excel sheets and provide output as one excel sheet with two different tabs and identify how the records match with each other.

I am attaching the sample test data with input tabs and what the final output should look like along with the metrics that I want to calculate. The hard part so far for me has been finding a simplest way to do matching of rows where the totals match even if it is not a one to one match.

Thank you again in advance.

Recon Test Data.xlsx

Help

Accepted answers

alexnajm

@sowjanyayinti here's what we worked on together 😊

Marsh Recon Use Case.yxzp

All comments

albert_alaluf

Hi @sowjanyayinti

Did you add duplicate rows on purpose or is this your nature of data?

albert_alaluf

This is just a start how to reach to final? I can't continue that much, because I don't know your data, I don't know if dups is how your data looks. Let me know maybe I can take it from here.
Screenshot 2024-06-03 231257.png

Bren_Spill2

@sowjanyayinti - here's an approach that works for A1. You would need to tweak it to work it for the rest of the groups.

Reconciling Irregular Records.yxmd

sowjanyayinti

I added duplicate rows o purpose as that is how the data will be. It is not going to be considered duplicate.

albert_alaluf

this creates complexity, because of cross joins. In UK Data for example, row 2 and row 3 is identical as well as in US data row 2,3, and 4. When you joined them you get 6 rows instead of 1. I'm not sure if this is you're looking for.

sowjanyayinti

Agreed. Definitely adds a complexity. Hence me trying to throw in the towel..lol. When there are duplicates then it still has to match one to one row and then after that check for any sum. Like the below example

Risk Reference	Assured	Currency	SDD	OSA	Metric	Policy Num	Insured Name	Currency	PDD	APIA	Metric
A1	Test1	USD	5/1/2024	10	M2	A1	Test1	USD	5/1/2024	10	M2
A1	Test1	USD	5/1/2024	10	M2	A1	Test1	USD	5/1/2024	10	M2
A1	Test1	USD	5/1/2024	20	M5	A1	Test1	USD	5/1/2024	10	M5
A1	Test1	USD	5/1/2024	30	M3	A1	Test1	USD	5/1/2024	10	M5
A1	Test1	USD	5/1/2024	50	M3

I tried adding a compare key by concatenating the values but i still cannot get the final output the way i want it.

Yoshiro_Fujimori

Hi @sowjanyayinti

I am not sure what you mean by "M5 : Not an exact one to one match but totals match".

So I tried to follow the rest.

I hope this helps.

Workflow

1280618.yxmd

sowjanyayinti

I am attaching an updated test input file and output file together. If you check the color coded rows in the 'What should be done' tab - Green color rows - easy to get to. The yellow color rows are what I meant when I said they are not exact match as in number of rows do not match but the total amount matches.

Recon Test Data.xlsx

alexnajm

@sowjanyayinti here's what we worked on together 😊

Marsh Recon Use Case.yxzp

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15881

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703