Find distinct records in an excel file and get a sum of all numbers from the rows and then do a comparison with a number from another excel file

Hi,

I need some help to find distinct records in an excel file and get a sum of all numbers from the rows and then do a comparison with a number from another excel file.

For example:

The first excel file is called File1.xlsx and the data looks like this:

Type Contract Year Contract Description First Name Last Name Month1 Month2 Month3 Month4 Month5 Month6 Month7 Month8 Month9 Month10 Month11 Month12
Full 2 Mike Miller 104 144 184 168 152 168 168 152 152 152 144 100
Temp 2 Mike Miller - 40 - - - - - - - - - -
Full 2 Joe Miller 104 144 184 168 152 168 168 152 152 152 144 100
Full 2 David Robinson 104 144 184 168 152 168 168 152 152 152 144 100
Temp 2 David Robinson - 60 - - - - - - - - - -

The second excel file is called File2.xlsx and the data looks like this:

Month Type Contract Year Contract Description First Name Last Name Cost
1 Full 2 Mike Miller 88
1 Temp 2 Mike Miller 40
2 Full 2 Mike Miller 16
2 Full 2 Mike Miller 144
3 Full 2 Mike Miller 160
4 Full 2 Mike Miller 152
5 Full 2 Mike Miller 160
6 Full 2 Mike Miller 216
7 Full 2 Mike Miller 152
1 Full 2 Joe Miller 88
1 Full 2 Joe Miller 40
2 Full 2 Joe Miller 16
2 Full 2 Joe Miller 144
3 Full 2 Joe Miller 160
4 Full 2 Joe Miller 152
5 Full 2 Joe Miller 170
6 Full 2 Joe Miller 216
7 Full 2 Joe Miller 152
1 Full 2 David Robinson 90
1 Temp 2 David Robinson 50
2 Full 2 David Robinson 16
2 Full 2 David Robinson 144
3 Full 2 David Robinson 160
4 Full 2 David Robinson 152
5 Full 2 David Robinson 160
6 Full 2 David Robinson 216
7 Full 2 David Robinson 152

When the user launch the bot, there should be an input prompt asking user to enter the month range that user wants to run.

So let’s say they enter 7 for the month, then the bot is going to sum the Cost value in File1.xlsx from Month1 to Month7 and sum the Cost value in File2.xlsx (disregard the month in this case, just find all rows match a person’s name and Type)

The Sum result (Month1 to Month7) of File1.xlsx should now look like this below:

Type Contract Year Contract Description First Name Last Name CostSum
Full 2 Mike Miller 1088
Temp 2 Mike Miller 40
Full 2 Joe Miller 1088
Full 2 David Robinson 1088
Temp 2 David Robinson 60

And the Sum result of File2.xlsx should now look like this below:

Type Contract Year Contract Description First Name Last Name CostSum
Full 2 Mike Miller 1088
Temp 2 Mike Miller 40
Full 2 Joe Miller 1138
Full 2 David Robinson 1090
Temp 2 David Robinson 50

We then use the CostSum value from dt_file1 and compare it with the CostSum in File2.xlsx.

The final output should show this:

Type Contract Year Contract Description First Name Last Name Comparison Result
Full 2 Mike Miller 1088 from File1 matches 1088 from File2
Temp 2 Mike Miller 40 from File1 matches 40 from File2
Full 2 Joe Miller 1088 from File1 is less than 1138 from File2
Full 2 David Robinson 1088 from File1 is less than 1090 from File2
Temp 2 David Robinson 60 from File1 is great than 50 from File2

Any idea what is the best way to do this?

I have also attached both excel files.

File1.xlsx (8.7 KB)
File2.xlsx (8.9 KB)

Variables:
grafik

Bringing dt into same format as dt2:
grafik
grafik

Grouping and summing dt1 (we remove the month Col)
grafik
grafik

with the range var we can limit

So the final report can be done by
for each row - Filter Datatable
Or
Join DataTable and processing the join Result

We do recommend to produce part result dt1Transformed… for a better traceabillity and to reduce black box processing complexity

Find starter Help here:
CompareSum_NormalAndPivot.xaml (10.4 KB)

Thank you so much for showing me the way. Do you mind explaining the code for the two AsEnumerable methods that you used to assign dt1Transformed datatable and dt2GroupSums datatable please?

Sure, will do it
For a first introduction have a look here:

I will definite study it. LINQ is very powerful indeed.

Can you please take a look at these two new input files? I added some addition columns in there and the code you provided no longer works due to newly added and shifted columns. For the final output, I just want to display Type, First Name, Last Name, and the SumCost.

When the bot starts, it needs to ask the user to enter a month range value. So let’s say if they entered 7, then the bot needs to find the sum from Month1 to Month 7 in File1.xlsx. I think you were doing the month matching for File2.xlsx file previously. I have took out the Month column in File2.xlsx since it is not needed.

File1.xlsx (8.7 KB)
File2.xlsx (9.6 KB)

Thanks again!

before we would do some reworks on the statement, are the new datacolumn structures reliable or can we expect again variants?

1 Like

Nice work ppr

I would say the column structures are not reliable. Thank you very much for taking the time to help me with this. Much appreciated!

later on my day I will have a second check. Just give me some time for this. Thanks

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.