formatting data for pivot table

Hi,

I currently have a large data set with the following multiple columns:

Description, amount, date

This is then repeat every 3 columns (for example it will be Description, Amount, Date1, Description, amount, Date2, etc).

To create the pivot table i usually move all the data into 3 columns and copy/paste it one after another. So row 1 - 300 will be for the date1, then row 300-600 will be for date2, etc all in Column A, B, C.

My question is, is there an easy way in excel to highlight all the columns and move it so it repeats itself, so that i can pivot it? I have data sets with a lot of columns and want to easily move it to a pivot friendly format so that i can trend the data.

Edit: i added an example. The yellow data is typically how i receive my data but it's hundred of rows and a lot of columns, so i'm looking for the fastest way to manipulate it to the red format so i can pivot it. I usually spend a lot of time cutting and pasting it, but i think there has to be a better, faster, more efficient way to accomplish this in excel. Thanks!!!

Hello & Welcome to the Board,

With your sample data how about this.

Instead of pasting the data right below as you have it in your sample, I have it going to G1 instead. Hope this will work for you.

Select Alt + F8 and run SetupforPivot

That's exactly what i want! I have large data set so it extends over G1. Which parameters in the macro do i need to edit so that I can adjust the range. Do you mind explaining it to me. This is the coolest thing ever and will save me so much!

Or is there a macro where i could just highlight my data set, run the macro, and it will transpose it to the right of it. I'm going to be using this function often to trend data for work. Thank you so much Jeff.

Hi shawzito,

Took a look at the macro again and realized one of the steps was wrong. Didn't realize it because all the 1's were masking the problem, but on the test data, changed the 1's to reflect different numbers and seems to be running right now.

The code is commented describing what is going on so hope it helps you understand a little bit more. If you have trouble following something please let me know...could have missed something.

So you don't have to alter the output of the data, the macro has been adjusted to output to sheet2 beginning in A1.

Sure don't know how to make the data set work off of selecting a range at this moment, but something to look at later.

Just as an added feature (maybe more for my learning than yours), the macro will produce a pivot table automatically. After you run the macro the first time, delete the sheet with the pt, sheet2 clear the contents, and then sheet1 add more data to the right and re-run SetupforPivot. Pivot data and the pivot table will automatically adjust to the need data.

Pretty easy fix. We need to Dim another variable (RW) so now both columns and rows are dynamic.

Look at the .Resize part of the code...this is where the variable RW is used which resizes the copied values to the length of Sheet1 column A - 1

I updated the comments in the code and again, hope I didn't miss anything.

Please Login or Register  to view this content.

You're most welcome

If you are satisfied with the answer provided, please don't forget to mark the thread as solved.

To mark your thread solved do the following:
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save