Apply Excel Autosum at end of Excel Col

[Rubot]

I have excel data which is not structured for creating DT. There are columns with same name under different parent names. Also columns are spread across top 3 rows.
Therefore making DT is difficult, unless someone know how to handle the duplicate names.
Also moving and getting column in one line is another difficult task, as these columns are not fixed and they vary from report to report.

If you see the screenshot, columns are not in one line. Also caclucations to be done only on those columns which has figure. Columns like Invoice Number, Client Name and Invoice date should not be caculated.

Only those from Debt, Vat and so on should be caculated.

Please let me know if anyone has done them before.

Thanks in advance.

[Srini84 (Srinivas)]

@Rubot

In Read Range activity, remove the check mark on Add Headers

So now you can go with Index

Also if the columns are fixed then you can use Write Cell activity and write as =SUM(“Range”)

Hope this will help you

Thanks

[Rubot]

@Srini84 thanks for your reply.
Read Range Activity - minus Remove Add Header works and first column is removed.
But I am left with second column that starts with Client Name and has *1000 multiple time.

When I am trying to read from that state, I keep getting error message *1000 exists in column name exists.

And also different reports have different column names, but fixed column numbers.

First Cut Page range of headers to another sheet with name header, create DT and Sum all the columns.
Move back headers from Header sheet and insert at the top.

Another way I am thinking is by using excel macros to get the Total Sum and Move to New Sheet.

This Sums will be from 2 different enviroments, and I need to check if there is any difference but subtracting Production from Pre Prod enviroment data.

[Rubot]

I have reached stage where I am able to remove all the headers from the top rows.

Now I need to do sum of those columns which has the values and not the one with string or date in them.
So we have to calculate everything after invoice date column and the end

[Rubot]

I used the following to get the total of every column mentioned above.

DTGrandTotal.AsEnumerable.Sum(Function(x) If(Double.TryParse(x.item(“Column”+foreachCounter.ToString).ToString, Nothing),Double.Parse(x.Item(“Column”+foreachCounter.ToString).ToString),0))