How would you do this? isolate an array of cells of dynamic size and position

Scenario: Excel file is transmitted to me once a month, it includes the production forecast for the year, past and future. Its been through the ringer, data is very dirty.

Top is a header and column labels, this is consistent.

Hidden by default at the top of the sheet (cells 11-200 currently) is the dirty data, filled with blank rows all over the place for products removed in the past.

below that is the same data with the blank rows removed. This is the data I want to grab.


So the header is easy, reference rows 1-10 across the page and I'm in business.

but the bottom... How do I grab an unknown length of rows, starting at an unknown row?

There is no reference point that I could hope to use, the top left cell varies should the product change. The only possible way for this to work is for the user to identify the proper cell.

I could have the user highlight the cells and paste it over. It is easy, but not elegant.

Or perhaps they could type the starting and ending row number of the range that is desired, use the address function to reference from the anchor point? Since this anchor point does not always change, most months the user would not even need to update this address. Better...

Any other, better ideas that I should consider?

Any chance you could attach your file? It make it easier to see what needs to be done.

Pete

unfortunately not :/ its pretty much all proprietary information.

I guess you could say I want B75:B100(row labels, colA is blank) N4:Z4(column headers,row 1-4 have title info, not a big deal), and then the data in N75:Z100.

Column are always the same, as is row 4 for the headers, but the 75 and 100 can change.

Okay, what determines that the data that you are interested in starts on row 75? Is it that cells before this are empty, and if so is this always the case for column N (or some other column)? Similarly, how do we know that the data finishes on row 100? Is that because cells N101 and below are empty?

Pete

sort of.

So in the first XXX rows of dirty data column A has lots of gaps, rest of the columns are filled with 0's.

then 3 blank rows, and a bunch of summary data.

following that is 7 blank rows and the data repeats.

Its easily identifiable because the file comes in with rows 5-74 hidden. Could also identify the row as the first row label that repeated... Maybe I could identify that position as a VBA loop looking for if cell.A75 = the cells above it? First time that is true, break the loop and save that position as a starting point?

the data runs down the page in succession till the next blank row, so that's relatively easy to identify.

You said before that column A is blank, now you say that it has lots of gaps (implying that there is something in it).

Can you please attach a sample file? It can contain dummy data but should be of the same format, i.e. if you have dates then leave them as dates, numbers as numbers etc.

I'm not a mind-reader, so I don't want to waste time coming up with a solution that doesn't match your exact layout.

Pete

sorry, A should be B. column A is blank, just how the author made it, B are the labels.

alright, heres the data wiped of sensitive info. where I put product name, those would all be unique identifiers, blank lines remain blank.

the data I want is AB211:AY265, As well as the row and column headers that correlate to that data.

So you don't want the data from columns G to P or from U to Y ?

But do you still want to show the data in its same column, i.e. column AB in column AB (in another sheet) ?

Are the row headers just in column B, or in C as well ?

Pete

B and C or copies of each other(dont know why its duplicated), I only need it once.

All the stuff in G-Y are sums of the monthly data that follows. If I ever need it, it is easily recreated. The monthly data is the real information, quarterly info just clutters the page if anything.

The attached file shows how you can do this using a single formula copied into the appropriate cells, plus a bit of copy/pasting.

First of all, put the numbers 1 and 2 in the first two cells of column A where the data starts - in your case this is A211 and A212. Then select both cells and drag the fill handle (the small black square in the bottom right corner of the cursor) down to the bottom of your data. I've coloured this yellow in the sample file.

Then highlight the header rows (in this case rows 1 to 10) and copy/paste them into Sheet2 at cell A1. I've also set up Freeze Panes from cell A11, and adjusted the zoom on the sheet so that it looks the same as Sheet1.

Then put this formula in B11 of Sheet2:

=IFERROR(INDEX(Sheet1!B:B,MATCH(ROWS($A$1:$A1),Sheet1!$A:$A,0)),"")

This can then be copied across into cells AB11:AY11.

Then highlight row 11, click <copy>, and paste into the subsequent rows as far as you need to (I've copied 50 rows' worth, though you only need 43 in this instance - it doesn't matter if you copy too far, as the formulae will just show blanks).

If you wish to, you can then highlight from row 11 to the bottom and do copy/paste special/values/OK/Esc to fix the values of the formulae, and then you could delete the entries in column A of Sheet1.

Hope this helps.

Pete

I appreciate the input, Im gonna play with it, I think itll point me in the right direction.

Out of curiosity, what are you doing with the data once you've grabbed it?

its for inventory forecasting of items in transit at EOM as a % of monthly production based on historical averages.

Ok, if it's for reporting purposes only you can always go to a pivot table.

well its interesting you say that, because that was one of my first thoughts...

but the pivot table seems to want the data in 1 neat little chunk with labels adjacent.

Since I love the getpivotdata function, I may very well take the resulting sheet, and run it into a pivot table.

Is it possible to do it directly? making a pivot table select 3, non connected, ranges?