"Reverse " Pivot Table

[Pepe Le Mokko]

Hi,
I am trying, starting from a list that has the same structure as a Pivot Table, to recreate the list it was derived from.

I added a sample to show what I mean.
Many thanks for your help

[martindwilson]

that wont open!

[Pepe Le Mokko]

Martin, I uploaded the file again

[martindwilson]

struggling!!!!!

[DonkeyOte]

If you create a running total you should be able to do it relatively easily I think.

eg

E2: =SUM(C1,E1)
copy down for all rows (ie to E28)

Formula to generate list then:

J2: =LOOKUP(ROWS(J$2:J2)-1,$E$2:$E$28,A$2:A$28)
copy to K2
copy J2:K2 down as far as required

[Pepe Le Mokko]

Thx, Donkey
I'll try it out tomorrow and let you know

[DonkeyOte]

No problem - post back if you have any problems.

Incidentally - to avoid confusion - the E2: =SUM(C1,E1) is deliberate (ie referencing row above)

[Pepe Le Mokko]

Hi Donkey,
works perfectly ! Brilliant as always !
I'll just have to add a condition to stop the results when needed which shouldn't be hard.

Thanks again

[DonkeyOte]

Yes - you could precede the test with a calculation based on ROWS processed versus SUM of items to be listed, ie

J2: =IF(ROWS(J$2:J2)>MAX($E$2:$E$28),"",LOOKUP(ROWS(J$2:J2)-1,$E$2:$E$28,A$2:A$28))

but obviously you're then repeating the MAX test over and over so would be better I think to store the MAX calc in one cell (say J1) and refer to it subsequently in the list formulae so as to reduce number of calcs being performed, eg:

J1: =MAX($E$2:$E$28)
J2: =IF(ROWS(J$2:J2)>$J$1,"",LOOKUP(ROWS(J$2:J2)-1,$E$2:$E$28,A$2:A$28))

[VKS]

Dear Experts ,
Better to ask dumb questions rather do dumb mistake. So my question
Table on the left has ACC appearing 9 times in column B and value sum is 10
Now table on the right ACC count is 8 something is not right with original pivot table it seems
Best Regards/VKS

[arlu1201]

VKS,

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

[VKS]

VKS,

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

My apologies Arlu,
I don’t think I have posted any personal question on someone elses thread……. except asking for some clarification on original data and output data. I consider myself ok with pivot tables and wanting to learn more about them as I spend lot of time on pivot tables so I was looking at the problem from a different prespective.
Please excuse me if I was not able to put my question in simple language as english is not my first language.
Best Regards/VKS

[arlu1201]

Sorry VKS. I have reversed the infraction.

[VKS]

Thanks Arlu.
Best Regards/VKS