Team,
I have a series of 100+ excel sheets in 100+ separate workbooks. This folder will grow, so I want to future-proof with a Query, but I have an issue.
Each of these sheets always have a variable number of rows (typically around 250 rows, somewhere between 200 and 300 or so), and I am trying to grab 4 separate rows of data from each sheet, the first 3 rows are fixed:
Row 1, Row 26, and Row 75,
and then the 4th row is the one which is 75% of the way down the list (if there were 250 rows in a sheet, it would be row 188, ie 0.75 X 250).
At first I thought of Importing a folder, and adding an index column and then filtering this column with OR criteria to grab rows 1, 26, and 75, but during the merge of course this will only work for the sheet (as the index will then be starting from about 251 for the second sheet).
I’ve briefly looked at the M reference and re-read Puls and Escobar, but can’t see a solution.
My guess is that I need to access a function which looks at both individual rows of worksheets, as well as something similar to COUNTROWS to then grab a PERCENTILE to figure out the 75th percentile, and then grab the resulting row.
I’d gratefully appreciate any help,
rod
Hi Rod,
Very interesting scenario.
It’s very difficult to imagine a solution here without seeing everything you are looking at.
Are you able to input images here in the post and then maybe actually mock something up and add it so that it can be tested?
There’s a lot to getting this right (if at all) but can only really see a solution by breaking it down into it’s simpliest form and then step by step working through solutions that could work.
Thanks
Not a bad solution for something so unique. Nice one.