Excel - Iterate through each sheet and move certain columns to a new Excel

Help
, ,
#1

Hi all,

Wondering if there is someone who can help me with an Excel challenge.
I have an Excel with multiple sheets, where I will have some columns with specific status.
I want to go to each sheet, find the columns with the specified status and move them to a new temporary excel.Test (2).xlsx (31.2 KB)
The available status will be Created, On Hold and Ready. I want to extract only the ones with Ready status. The info’s from the new excel will be used in another process

Any ideas what’s the best method to use? Any help will be much appreciated.

Many thanks.

#2

Can you share a sample Excel file (with dummy data in place of confidential information)?

#3

Test (2).xlsx (29.4 KB)
Hi Anthony, here’s the file, I’m interested in getting in a new excel only the columns that have the status on Ready for Creation (each sheet, row 4). Thanks

#4

For your use case, are you trying to get the entire column if one row contains “Ready for Creation”, do you want to write that column to a new workbook? Also, if that is the case, do all of these columns go in the same sheet in the output workbook?

#5

@Anthony_Humphries yes, I want to get the whole column with the Ready status and move to the new Excel. Also, all columns from all sheets with the same status can be places in the same new workbook. thanks

#6

Is “Ready for Creation” in a specific row, or could it be in any row of each sheet?

#7

Ready for Creation will always be in row no. 4. on all sheets

#8

The Best Approach will be ,

  1. If you are sure about the no of columns , Put those columns in an array.

  2. create a DT, say Master DT.

  3. Get the Workbook variable in the Excel application scope,say wb

  4. Use For each with wb.GetSheets Iterate Through each Sheet,

    4.1) use read range and pass the sheet name dynamically. and assign it to ReadDT.

    4.2) Then use for each to Iterate through the column Array
4.2.1) 
      Use filter wizard "column name" , value = Ready for creation , assign it to Temporary DT.

    4.2.2 ) If temporaryDt.rows.count > 0 , means that particular column contains Ready for creation

then

4.2.2.1 ) Use filter wizard , column filter mode and give the Column name ,(the only column which you want) and assign it to TemporarycolumnDT.

4.2.2.2 ) Use Join Datatables activity and use Full joint , pass the MasterDT and temporary DT.

This will ensure all the contents are there in the single DT.

  1. once you are out of the loop, check if the DT contains any rows and then write it in the new excel.

regards
Vishnu

#9

Thanks @vishnuvarthanp. Will try your solution

#10

@vishnuvarthanp had tried to apply your advice, but looks a too complicated for my knowledge. Forgot to mention that I’m new user and don’t have such advanced bots created. Any chance to get a sample of those explanations if there not too much trouble? Many thanks