Extract variable rows to different sheets in excel

I would like to copy specific value from the excel which is containing different rows and different columns every times.Sheet 1 is the original part, sheet 2 and sheet 3 are the expected result. My logic is using for each row to detect the pass column does it contains pass, if yes, write to a new sheet. But I don’t know how to seperate the expected value into two worksheets.

Attached is for your reference.

Thank you very much.testing.xlsx (11.2 KB)

@raymondhui Check below workflow once.

Main.xaml (15.3 KB)

@Manjuts90 Thanks for your solution and it gives me some ideas how to make the flow more generic. However the excel format is not consistent therefore I need to read the all range into dt and read each rows inside. I tried and out of range error occurred.

Also if the row contains some symbol like =,–, error occurred. Are there any ways to trim out these symbol or dont take it into the dt? Tahnks.

@raymondhui you can use string replace or replace option of regex, u can replace those symbols easily

Thanks! I can solve it.

But sometimes I can read the whole worksheet of it and it says out of range. Do you know in what kind of condition this error will occurred?

@raymondhui I dont have idea about it, might be because of large amount of data.

hi @Manjuts90 I still have a problem on read range.

If I want to choose the first column and only select the cell starting with integer and choose the row, how do I write that?

I found some possible solution like using start with and assign a variable in read column like

Dt=Dt.Select(“Convert(columnvariable,System.String) like’9%’”).CopyToDataTable()

But it doesnt work and it says columnvariable is object type and connot convert to datatable.

Please help.

Many thanks.


Instead of above one try below once.

Dt=Dt.Select(“Convert([columnvariable],System.String) like ’9%’”).CopyToDataTable()

Hi Manjuts, how about I don’t have a column name or header and it is the first column?

@raymondhui while reading excel uncheck column headers property in read range activity, then datatable contains headers like column0, column 1 etc depending on columns you have in excel.

You can also for each datarow activity after reading excel. With if condition you can check whether value in each row of particular column starts with 9 or not, if it is true add that row to new datatable if not check with next row of data.

Thanks for your answer mate. Yes i dun know there are default header and thanks for your reminder.

As i am doing a flow that I dun know how many different value in column0, for every different value, i need to write into a new worksheet, I know I should use counting variable, once find a value, +1 and move to new sheet+1, but I dun know how to assign a new variable into the value found different in column0, and then assign another variable that found different from pervious 2 values in column0, etc.

@raymondhui I think I have created the workflow keep ur requirement, please check the workflow once, if you still have any doubts message me.