• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Transposing multiple tables to 1 pivot [SOLVED]

B

brems

Member
Hi,


On a daily basis I'm putting in some data in an excel sheet.

I've made an example file to clarify...


We have 10 categories

For each category there's an amount of emails that comes in daily: IN

For each category there's an amout of emails that gets processed daily: OUT

And of course, not everything gets processed immediately and must wait until the next day: WIP


I just paste the data in the excel every day but the format isn't very good in order to use for a pivot table.

So I want to transpose the original data to another sheet and in another format (tab pivot in the excel sheet example) so it's much easier to work with it.


I've been using the post (14 may) on transposing as a basis but it isn't sufficient because the date and each category is mentionned at least 3 times a day. So, I don't think transposing only will do the trick.


I would be very thankfull to have some help/advice on this issue.

Feel free to offer a complete solution but I also would appreciate a few hints on which way I can proceed (formulas,...) so there's a least a challenge left.


Thanks in advance


https://docs.google.com/spreadsheet/ccc?key=0Av-NhSp9ULywdGVPRVFWSDlCTldQZlR1VllxWTlySFE&usp=sharing
 
Hi brems,


The solution is partly manual but works:


1. You will have to copy the categories and dates manually.

2. You will be using a formula to fetch manually the values from the three tables.


Is that ok so i can work out the simple SUMPRODUCT() formula? or you can ask some other members for VBA solution as well! as you like.


Regards,
 
Hi Faseeh,


The solution you offer seems fine to me.

VBA isn't necessary at this stage. For the future, I may be looking back in the way I need to organise my data in order to work with it more easily.


Thanks in advance.
 
Hi Brems,


Please see this. You will have to copy dates and category.


https://dl.dropboxusercontent.com/u/60644346/transpose.xlsx


Regards,
 
You must log in or register to reply here.
Back
Top