Reconciling Irregular or Uneven records of data

[sowjanyayinti]

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.

[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.

[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.

[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

[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.

[alexnajm · Accepted solution]

@sowjanyayinti here's what we worked on together 😊