Solved

Desired Format Pivot

Posted on 2013-02-04
10
247 Views
Last Modified: 2013-02-04
Hello ,
Current Format shows the data structure it’s in currently.  Somehow, someway it has to be pivoted to get it in the format I need where time stamp columns transform to the rows instead.  The format I need is shown in Desired Format sheet. The fastest way to o this on 35000-40000 rows
Thanks
pivotDesired.xlsx
0
Comment
Question by:Rayne
  • 4
  • 3
  • 3
10 Comments
 
LVL 24

Assisted Solution

by:Steve
Steve earned 100 total points
ID: 38852607
the following code will do exactly what is required in the example workbook

Sub Normalise()
Dim myRng As Range
Dim myArr()
Dim x as long
Dim y as long
Dim z as long

Set myRng = ThisWorkbook.Sheets("Current").Range("A2:CX108")
Z = 1
For x = 2 To 107
    For y = 1 To 100
        ReDim Preserve myArr(1 To 4, 1 To Z)
        myArr(1, Z) = myRng(x, 1)
        myArr(2, Z) = myRng(x, 2)
        myArr(3, Z) = myRng(1, y + 2)
        myArr(4, Z) = myRng(x, y + 2)
        Z = Z + 1
    Next y
Next x
ThisWorkbook.Sheets("desired").Range("A3:D" & Z + 1) = Application.Transpose(myArr)
End Sub

Open in new window


If the code needs to be more "flexible" can change the code if required.
pivotDesired.xlsm
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 400 total points
ID: 38852633
Hello,

this can be done with a "reverse pivot", but first we need to change the source a bit:

Insert a column after column B, put a label in row 2 and in row 3 enter the formula

=A3&"-"&B3

Copy down. Now for the reverse Pivot.

1.  click a cell in the table and then use Alt - D - P to bring up the good, old Pivot Table wizard (yes, it's still there!)
2. in the first step, click "Multiple consolidation ranges" and Next
3. in Step 2a click "I will create the page fields" and Next
4. in Step 2b select the range, starting with the newly inserted column. Do not include columns A and B.  -- then click Next
5. in Step 3 select "new Worksheet" and click Finish

You will now see a pivot table. In the Pivot table field pane, drag all the fields from the Column Labels and the Row Labels areas back up into the field list. You will see the pivot table reduced to one cell with a number and a label above.

Double-click the number.

This will create a sheet with the raw data. Now you only need to split the previously concatenated columns A and B. Insert a new column between A and B and perform a Text to Columns on column A, using the "-" as the delimiter.

cheers, teylyn
0
 

Author Comment

by:Rayne
ID: 38852799
Thanks everyone for your help

Teylyn, Please check attached. This is what I got so far.
I don’t understand what to do here?-
"In the Pivot table field pane, drag all the fields from the Column Labels and the Row Labels areas back up into the field list"

Can you explain? I dragged all the three fields out of there, after which I didn’t see anything
pivotSoFar.png
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 38852860
See the four panels at the bottom of the field list in the pivot pane? The upper right has the "Column" field in it. The lower left has the "Row" field in it. Click and drag these two items up into the field list, so that the two panes are empty and the only thing left is "Sum of Value" in the lower right hand box.

That will transform the pivot table into a one cell affair with a label. Then double click the number in the one pivot cell.
0
 
LVL 24

Assisted Solution

by:Steve
Steve earned 100 total points
ID: 38852863
Cool method Teylyn:

Rayne, all you now do is drag the row and column off the column and row label areas..
So as to just leave the "Sum Of"

Then double click the large number that is left.
This will create the same data as the macro.
Cool.

I am sure that this would be a great topic for an article. ;)
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 38852894
>> I am sure that this would be a great topic for an article. ;)

John Walkenbach already wrote it up. http://spreadsheetpage.com/index.php/tip/creating_a_database_table_from_a_summary_table/
0
 
LVL 24

Expert Comment

by:Steve
ID: 38852912
Ahha... "good old" John,

His is are some of the few books I have in actual book form, and would heartily recommend.
(that and a dictionarry, as I am sure my spelling is getting worse as I get older)

But this is a great example of returning pivot data back to normal form.
Cheers,
Steve.
0
 

Author Comment

by:Rayne
ID: 38852952
Thanks Everyone for chipping in
Teylyn
All Hats off to you :)
Thanks a million
0
 

Author Comment

by:Rayne
ID: 38852973
Here's the follow up question...if anyone is interested :)
0
 

Author Comment

by:Rayne
ID: 38852974
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now