Check data & delete in excel?

I have file input and file report.
*File input have data row 10K

But I want to check column status in file report if value = Cut

Delete no. (Lot) in file input sheet Summary (Delete Col A-I)
and cut row in sheet (check sheet name in column Period) to new sheet file name error.

*I want column A-I in row red text

And cut row to sheet Error.
And write Column A = Sheet Name before cut.

File as attached.
output.xlsx (43.5 KB)
Report.xlsx (9.8 KB)
input.xlsx (43.0 KB)

*I don’t create new file (file output = output in file input)

Please guide me about it.

Hi @fairymemay ,

Firstly create a dictionary of DictReport(String, String) with the Report.xlsx.

Next read the data of input file and loop through the records, if “No” in record matches with the DictReport(“No”) you can build the logic to read the Period column (and update the sheet with Name, Account Name, Account Number and Amount.)

Use read range for eg(C_APR) and get the data Table of that sheet in memory, append the records in data table.
continue updating all the values in datatable on both the sheets.

after you have finished looping all the records in input sheet, write bothe the data table on respective sheets.

@ANSHUL And I have create sheetname Error for keep data that delete (C_APR , A_May)

For the Error sheet, create a datatable say ErrorDT and append the values those map to the values “Cut” same as above, except in above case the mapping will be to “NoCut”. Append it to the Error.xlsx file.

@ANSHUL You can share flow to me for guide ?

Take a look at the logic, you can use it as a guide.
Please take Note: I had to remove empty rows from input and ensure the Results.xlsx file which is used as mapping has same value as input, I noted Lot62(13) was different then Lot062(13)

Hope you have a guide now, you can continue to add the logic to add the records in respective sheets of that period.

Main.xaml (18.4 KB)
InputFileAsDictionary.xaml (8.6 KB)
Report.xlsx (9.3 KB)
Error.xlsx (9.1 KB)