• 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.

Pivot Table Source Data

Yandeez

Yandeez

Member
I have product data and when it's imported into excel, the products are laid out horizontally in the very first row. I need a way to categorize those products by "Product" so that I can use it as a report filter in PT. There's way too many products to list them in a PT individually. Any help is greatly appreciated. Thanks,
 
Can you give an example of what the data currently looks like, and what the desired layout would look like?
 
Thanks for your response. I've uploaded some sample data. This is how it is exported from some program. I'm looking for the best way to visually represent this data and would like to have the option to do analysis by rep, region, store name on a Year over Year or Year over Year by Month. Any help or advice on how to proceed would be immensely helpful.
 

Attachments

Can you confirm that the data gets created like this?
upload_2014-8-8_8-48-25.png

It looks like the headers in col E:AT cover 3 rows, but the data in col A:D start at row 2...seems like the 742 in E4 should line up with Marcus in row 2. Otherwise, not sure what the data in A2:D3 is there for. Can you confirm if the data really looks like this, and if so, my assumption is correct that the data is shifted, and needs to be re-aligned?
 
Thanks so much for your help Luke! This is the way the data gets exported. As you can see, it's not in any type of format for PT, charts, etc. Aside from the the format, given the data, how would you advise I represent this data? Should I use many different Pivottables? I'd like to experiment with dynamic charts but I'm an excel newbie. :(
 
The first problem is to get your data in a better layout. As I said before, there appears to be a disconnect between rows 2 and 4, but we can adjust for that. The attached workbook has a macro which takes data from "Sheet1" and prepares an Export sheet, in proper data format. It takes a little while to run as it's just copying data from one sheet to another, but it gets it done (takes about 30 secs).

Now that it's in our new layout, we can create a PivotTable. This lets us easily analyze our data. You can move the fields around to create whatever type of analysis you want, and filter down to look at different franchises/Reps.
 

Attachments

Hey thanks for taking the time to put this together Luke! I have to play around with this. Another quick question: I have rows one spreadsheet that I need to separate into individual tabs in the workbook. Any way to do this other than copying each row, inserting a new sheet tab...?
 
Hey Luke - I wasn't able to locate where I can take rows and separate them into worksheet tabs in the same workbook. :( Can you point me in the right direction? Again, thanks so much for all your help.
 
You must log in or register to reply here.
Back
Top