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

[skyblue1854]

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)

[ppr (Peter)]

Variables:

Bringing dt into same format as dt2:

Grouping and summing dt1 (we remove the month Col)

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)

[skyblue1854]

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?

[ppr (Peter)]

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

[HowTo] LINQ (VB.Net) Learning Catalogue

This topic references the different Learning HowTos related to LINQ. It will be updated on new published HowTos as well LINQ Learning: Topic LINK First Start with LINQ [HowTo] - First Start with LINQ (VB.Net) Exploring the LINQ universe [HowTo] - Exploring the LINQ Universe (VB.Net) Operators Topic LINK Where [HowTo] LINQ - First Start # Where Operator Select work in progress Any [HowTo] LINQ – First Start # Any Operator All [HowTo] LINQ – First Start # All Operato…

[skyblue1854]

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!

[ppr (Peter)]

@skyblue1854

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.

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

[ahmed.abdo]

@skyblue1854

Nice work ppr

[skyblue1854]

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

[ppr (Peter)]

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