0

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):

Table as imported

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:

enter image description here

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:

enter image description here

Improve this question

1 Answer 1

Reset to default
2

Step 1: Create a linked clone sheet

Step 2: Modify the clone formula from row 2 onwards to "fill down" if blank

=IF(ISBLANK(Main!A2),A1,Main!A2)

You will end up with something almost like this

enter image description here

Except you will have mini-headers for each store "First, Product, Amount".

Step 3: Filter the mini-headers by adding an auto filter to the top row, and text filtering the product column for "does not equal" to "product".

Step 4: Copy and paste the filtered data again to a new sheet and paste-as-value

Step 5: Apply pivot table to summarise as desired.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .