How to extract rows from a worksheet with more than 100 sheets when the value of two cells is within a given threshold?

[fede_vx]

In this spreadsheet I need to copy in a new sheet all the rows when column D and column E are within a given range I can arbitrarily decide.

Anybody can please help?

.

 

[bluesky63]

Hi, if you are using Excel 2016, Power Query can do this transformation work for you, if you can attach a sample of worksheets and indicate what you want to achieve in a worksheet

 

[fede_vx]

Hey thanks for the reply, here the file
I need to extract/isolate/copy in a new sheet... all the rows when the values in column D and E are within a range I can specify.
As you can see there are more than 100 sheets so I really need to go through all of them and then have the rows I need sorted.

Thanks a lot

 

[bluesky63]

usually for workbook that consist of more than 10 (or 50....), we will open up a blank workbook, Data > New Query > From Other Sources > blank query, then input the M Code
= Excel.Workbook(File.Contents("C:\Users\JO_HOME\Documents\Seowkian\Technology & IT\chandoo\log.xlsx"),null,true)

you must change your path that save log.xlsx

then continue transforming till all the 100 worksheets structurally appended in one single table, then make use of this consolidated table to reference for any outcome that you want, reach certain range then action etc

However, your original worksheets are too dirty, a lot of invalid data type, I got difficulty transforming for you as I don't understand all your data fields

Probably other forum guy can advise you VBA

 

[bluesky63]

Hi fede,

Anyway managed to transform and cleansed