Pivot Table with blanket rows

[abc3132]

Hi all.

I would appreciate if somebody can help me


I have two issues that I would like to fix (Please have a look at the attached table):
1-Why the table is not adding the sales as figures?
2-How (and more challenging) you can aggregate the certain products under an specific customer when you have blanket spaces?

Many Thanks!

[xor]

See comments in Sheet2 of the attached.

[abc3132]

Thanks Xor. You are right .
Overall my main problem is to put the items in order when is a blank cell.
With INDEX+Match I have improved a lot but not enough.
If you have any idea is welcomed
Cheers

[xor]

What have you improved with INDEX + MATCH?
I am not sure what you want.

[abc3132]

With the customer name rows you can generate after a proper pivot table. If I have blank spaces I cannot.
In any case thanks

[xor]

Can you show manually what you want, and is it in Sheet1 or Sheet2?

[abc3132]

Hi Xor.
Thanks for your commitment.
Please have a look at the attached file

The first tab "System Layout" shows how the system report

The second tab"NO" is the pivot table generated from the first tab "System Layout". Please observe that everything is messed

The third tab "Layout to Pivot" is how Excel need the information to generate an accurate and correct Pivot table

The fourth tab "YES" is how I would like to have the report. Please observe that here the "products" belong to the right "AC" & "Customer"

So the idea is to have the fourth pivot table from the first one. If is necessary with or without intermediate steps.

Thanks, Adrian

[xor]

See the last sheet.

All the steps described in this last sheet can you of course perform on your data in sheet System Layout.
Be sure to select only the relevant data when you run the macro.

[abc3132]

WOW!.
It works perfectly!
Thanks for your time