Read column data with there header name

Hi team,

I have multiple sheet in one workbook and all has a same column name like “Quantity” and i want to do sum of that column in all sheets using header name quantity with array method i got all column header name in one array. but i am not getting value of the column

Hi @Pramod_Sharma

You can explore to use linq expressions on your solution, here is a link you can check

Hi @Pramod_Sharma
I think u need to get sum of all quantity column values in all sheets of the workbook.

For this try this way

  1. Use excel application scope
  2. Use assign activitiy to intialise sum varaible
    sum=0
  3. Inside the excel scope do the following
    a. Use get sheets activitiy and store in string array
    b. Now loop through that string array which contains sheet names using for each activitiy
    c. Use read range activitiy to read the given excel sheet by passing sheet name there and store it in dt1
    d. Now use linq query
    sum=sum+dt1.AsEnumerable().Sum(Function (row) CDbl(row(“Quantity”))

So finally u will get the solution

Regards

Nived N :robot:

Happy Automation :relaxed::relaxed::relaxed::relaxed:

1 Like

can you pls make workflow file for this because i am not getting what would be the type variable of Sum and how to run query

New.xlsx (9.9 KB) Sum of column.xaml (8.3 KB)

Hi @Pramod_Sharma
Check this sample workflow
sample workflow.zip (39.6 KB)

Regards
Nived N
Happy Automation

Thank you so much for this but there is a one issue i want sum in every sheet of but it is giving me Grand Total of it. can you please help me how do i separate total as sheet vice on the same sheet.

Hi @Pramod_Sharma
Kindly check this workflow
sample workflow.zip (39.9 KB)

Mark it as solution if it resolves your query

Regards
Nived N
Happy Automation

Thanks Again for this you solved my 90% problem but still I am not able to write total in last cell of the column in each sheet i used write cell activity and also convert total into the .Tostring but in range it is not taking value properly so please help me in this.

Hi

@Pramod_Sharma

After getting the sum Assign activitiy

U can use write cell activitiy to write the sum

Let’s say quantity is in C column of excel (consider )

Drag the write cell activitiy and add the values as follows:

Cell : “C”+(dt1.Rows.Count+2).ToString

Value : sum.ToString

It will work

Regards

Nived N :robot:

Happy Automation :relaxed::relaxed::relaxed:

yes I got your point but I do not want use “C” here i want Total in the same column which we read for sum. in the ending cell of the column

Is the quantity is in same column in every sheet

Like I had told an example with Column C

Yup but what if quantity in sheet 1 is in “B” column and in sheet 2 is in “C” column and in sheet 3 in “D” column. In that case as we are doing sum as per the header is that any way to do the total in the same column and last row of it.

Hi @Pramod_Sharma
Check this workflow

This will resolves your query
sample workflow.zip (40.5 KB)

Mark it as solution if it resolves your query
Regards
Nived N
Happy Automation

Thank you so much you help me allot in this project. :hugs:

Hello Nived,

I got one query related with same project. The Total which we are printing in the last of row column can print that only total in new sheet?

Eg. Total of sheet1 is=2000
Total of sheet2 is = 1500
Total of sheet3 = 3000
“Newsheet”=2000
1500
3000
Like this.
We already store sum in sum variable but I am not able write in new sheet.

Hi @Pramod_Sharma

I didn’t get your point

Can u explain it well?

Here id some example of
it

We got the total of each sheet now I want to print that Total in new sheet

So u want to print the complete sum of all total in the new sheet?

yes complete total of each sheet in new new sheet
as I shown in Total image