First post - so hello people!
I've had some problems trying to convert tabular data back into "raw" data.
Best metaphor I can come up with is that some sent me the results of a pivot table that they'd done Copy, Paste Special, Values to and dumped the raw data (which is something I do with BIG files).
First attempts involved array formulas, concatenation, complex lookups, etc, but I ran into quite severe problems. VERY difficult to describe without showing the data, but I was attempting to iterate all possible combinations of text characteristics, then doing =sumif against the columns with values in to decompose the table to a flat list. But I ran out of rows. Erm, I need millions. Yup, multiple sheets, but even so, crazy file sizes. Data, Filter, Advanced can't quite do what I want either...
I eventually solved my problem by using Access to sort the data out, and using a query as the data source for a Pivot Table. Which is fine for me. But... For various reasons in my workplace this is an "unsupported" solution. Work gets crabby if I write VBA for Excel etc...
Does anyone have any suggestions/experience re this sort of thing?
peter
I've had some problems trying to convert tabular data back into "raw" data.
Best metaphor I can come up with is that some sent me the results of a pivot table that they'd done Copy, Paste Special, Values to and dumped the raw data (which is something I do with BIG files).
First attempts involved array formulas, concatenation, complex lookups, etc, but I ran into quite severe problems. VERY difficult to describe without showing the data, but I was attempting to iterate all possible combinations of text characteristics, then doing =sumif against the columns with values in to decompose the table to a flat list. But I ran out of rows. Erm, I need millions. Yup, multiple sheets, but even so, crazy file sizes. Data, Filter, Advanced can't quite do what I want either...
I eventually solved my problem by using Access to sort the data out, and using a query as the data source for a Pivot Table. Which is fine for me. But... For various reasons in my workplace this is an "unsupported" solution. Work gets crabby if I write VBA for Excel etc...
Does anyone have any suggestions/experience re this sort of thing?
peter