Filtering based the list and after filtering copy the result to a new sheet using the filtered condition

[prasath17 (Guru)]

Hi All, Looking some assistance in filtering:

1. First, I have copied the unique state values to copiedSheet (completed).
2. Then using the values in the copiedsheet, i have apply the filter by state
3. copy the results to the new workbook with the sheet name corresponding to each states (“AL”, NY", NJ" etc…).

I have attached my xaml and sample workbook for your referrence.

Main.xaml (6.3 KB) Practice.xlsx (9.4 KB)

[ppr (Peter Preuss)]

@prasath17
find starter help here:
MainV2.xaml (9.1 KB)

After readin the excel we can iterate over a on the fly constructed datatabel with the distinct state names
inside the for each:

• log the state name which is processed
• filter the data for the current state which is looped
• write the result to a worksheet with the worksheet name same as the state

for the distincts: InputDT.DefaultView.ToTable(True,“State”)
true - remove duplicates, State - column to keep

Let us know your feedback

[prasath17 (Guru)]

Thank you so much ppr. This is exactly what i was looking for, but since i am beginner i could not able to achieve it.

[prasath17 (Guru)]

@ppr … I have tried to apply the solution to my project scenario. I just tried with one input file(Report) and try to write into output file (Summary). Below are the issues i am facing:

1. Write range is throwing an exception error. I think its run time error. May be because of the memory. My input file contains close to 36K rows. Attached the error screenshot.![Error|690x277]. But when i verified the counts, output has all the expected rows. Not sure why its throwing the error.

   

2. I am expecting to write the All the states alphabetically after the sheet1. ie. Sheet1, AL, GA, ID, IL etc…if you see the below screenshot it’s other way around.

3. After fixing the above issues, my final goal is to read only the report files and write it to corresponding summary file. How to do this dynamically?

4. My current xaml file attached. MainV2.xaml (14.3 KB)

[prasath17 (Guru)]

1. I have fixed this error by removing the Excel application scope.
2. Sheet name order has been fixed. The moment i removed the excel application scope, order has been fixed.

[prasath17 (Guru)]

@ppr Any help? highly appreciated…