I'm currently dealing with a large chunk of data that when pasted into Excel forces me to do some rearrangements before I can properly work with it, and I am looking for a way to resolve the disassociation of values.
When I paste the data into excel directly or import from a text file, it ends up in the following layout (using placeholders here):
The problem is that the "Chairs" and "Tables" under the store "First" aren't actually related to the first column data in question. Instead, Excel associates the 2nd and 3rd column data with "(blank)" in the pivot table generated from this range.
From what I understand, in order for Excel to actually correlate the data, it's meant to be laid out like this:
My issue is that I can't find a way to do this automatically for the actual table that has around 800 entries and varying amounts of entries in columns 2-3, and would would rather not need to adjust the layout manually.
For what it's worth, the desired layout for the table is as follows: