Row 10 (not fixed) gives the total of the values in that column
Number of values in a column can differ
Requirement 1 :- Fetch the total when there are blank rows in b/w [column 2]
Requirement 2 :- If total doesn’t exist then calculate the total [column 1]
Requirement 3 :- Calculate or fetch the total (if it exists) when there are no blank rows in b/w [column 3]
Is there an activity to get column letter based on column name ?
Ignore the colours - they are not fixed
Column headers are present in Row 2 and the header names are fixed but column index can change from file to file
The output of this activity will be a datatable, dtFilteredData
Check is filtered datatable has any records to confirm on empty cells in Column2
dtFilteredData.Rows.Count > 0
If empty cells exists, fetch the data from last row as total,
decTotalCol2 = dtData.Rows(dtData.Rows.Length - 1)(“Column2”).tostring
Check Column1 contains total,
decTotalCol1 = dtData.Rows(dtData.Rows.Length - 1)(“Column1”).tostring
If doesn’t contain total
if(string.Isnullorempty(decTotalCol1))
Calculate sum of first column
decTotalCol1 = Convert.ToDecimal(dtData.Compute(“SUM(Column1)”, string.Empty))
Repeat steps 2 to check column3.
Check if there are no empty cells
if(dtData.Rows.Count = 0)
Check Column3 contains total,
decTotalCol3 = dtData.Rows(dtData.Rows.Length - 1)(“Column3”).tostring
If doesn’t contain total
if(string.Isnullorempty(decTotalCol3))
Calculate sum of third column
decTotalCol3 = Convert.ToDecimal(dtData.Compute(“SUM(Column3)”, string.Empty))
When you do read range, select the Add Headers property. That will consider the first row in the data range as header. Otherwise, the default column names, Column0, Column1, Column2, so on gets assigned.
In your case as Columns can be shuffled, you have to select the Headers property.
i did select the add headers property and also my headers are in row 2 and not in row1. This is exactly why i want the column header names to be changed and tried.
Also, i believe .Length in decTotalCol1 = dtData.Rows(dtData.Rows.Length - 1)(“Column1”).tostring here should be .Count and not length, as it isnt a member of the datarow collection
Is there a way to get the index of a column name ? provided the column name is in any row other than row 1
Through the output data table i know that the column names are “XYZ Excel”, “Column1”, “Column2”. Here since row 1 is merged the column names are this way but my actual column headers are present in row 2.
Since i know the column names now (considering row 1 names), I have hardcoded the name in subsequent steps
If i get to know the column index of the actual column headers (present in row 2 as C1, C2, C3) then the solution can be made dynamic
In Simple terms, if i get the column index or cell index of say “C3” [The header name in my file], the problem is solved.
I have updated cell range in the read range activity. Now you get your column names.
Also added placeholders for the flow. Please check and implement according to the instruction. Sequence2.xaml (10.4 KB)
@aksh1yadav - Thanks man. Your solution works like a charm for the file i provided earlier. But my bad i kind of missed few important points in that file.
Am attaching a replica of the file that am working on and your solution fails for this. Although i have prepared a working solution but i feel the solution can be much more shorter when tried with “Read Column Activity”. Can you try to provide a solution by using this activity in the workflow that i have attached as am failing here.S - Requirement Sorted.xaml (17.7 KB) TestFile_Forum.xlsx (13.0 KB)
@Madhavi - Thank you for your help as well. Havent tried your complete solution but have picked few logics though to get to this stage.
Kindly ignore the colours in the file and the column indexes are not fixed but the header names in row 3 are fixed.
the thing only i am assuming now is that there will be no blank entry in that column because you are reading single column so do not know total column is having Total or not or just last value etc.
please find below solution which you asked for but be aware i am considering you know that there is no blank value.
there are more workaround i know but as you asked for it now and based on time availability check it out. S - Requirement Sorted.xaml (17.8 KB)