Dataset organisation

[antsrik]

Hi All,

I have a dataset in the following format

-------WholesaleKP RepairsKP
1999Q1 29,935,196 2,753,918
1999Q2 28,822,636 2,661,801
1999Q3 27,588,453 2,668,333
1999Q4 30,280,338 2,774,898
2000Q1 30,803,697 2,903,654
2000Q2 29,384,567 2,986,720
2000Q3 27,896,545 3,030,622
2000Q4 30,642,902 3,034,591



I would like to organise as

WholesaleKP
---------------Q1______ Q2
1999
2000

RepairsKP
---------------Q1______ Q2
1999
2000
I am using office 2007

Thanks

 

[antsrik]

correct to question 2 & 3

248 columns
167 rows
in the raw dataset

 

[hiker95]

antsrik354,

248 columns
167 rows
in the raw dataset

Beginning with your first reply, reply #1, you only showed 3 columns.

I just finished the coding for the beginning dataset of 3 columns.

Per your beginning raw data dataset:

Excel 2007
	A	B	C	D
1		WholesaleKP	RepairsKP
2	1999Q1	1,496,760	137,696
3	1999Q2	1,441,132	133,090
4	1999Q3	1,379,423	133,417
5	1999Q4	1,514,017	138,745
6	2000Q1	1,540,185	145,183
7	2000Q2	1,469,228	149,336
8	2000Q3	1,394,827	151,531
9	2000Q4	1,532,145	151,730
10	2001Q1	1,544,047	151,236
11	2001Q2	1,459,776	151,539
12	2001Q3	1,389,803	152,542
13	2001Q4	1,389,803	152,542
14
Sheet1

After the macro in a new worksheet Results:

Excel 2007
	A	B	C	D	E	F	G	H	I	J	K
1	WholesaleKP	Q1	Q2	Q3	Q4		RepairsKP	Q1	Q2	Q3	Q4
2	1999	1,496,760	1,441,132	1,379,423	1,514,017		1999	137,696	133,090	133,417	138,745
3	2000	1,540,185	1,469,228	1,394,827	1,532,145		2000	145,183	149,336	151,531	151,730
4	2001	1,544,047	1,459,776	1,389,803	1,389,803		2001	151,236	151,539	152,542	152,542
5
Results

In order to continue I will have to see your actual beginning dataset per your above quote.

You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.


If you are not able to provide your workbook, per the above, then:

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[antsrik]

you can assume that is the complete dataset, i just need a start. I will be able to adjust the coding to accommodate the number of columns and rows because it it fluid and changes regularly.

 

[hiker95]

antsrik354,

you can assume that is the complete dataset, i just need a start. I will be able to adjust the coding to accommodate the number of columns and rows because it it fluid and changes regularly.

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ReorgData()
' hiker95, 01/07/204
' http://www.mrexcel.com/forum/excel-questions/747957-dataset-organisation.html
Dim oa As Variant, wr As Variant
Dim r As Long, lr As Long, rr As Long, i As Long, n As Long
Application.ScreenUpdating = False
With ActiveSheet
  If .Range("B1") <> "WholesaleKP" Then
    MsgBox "The 'active sheet' cell 'B1' does not contain 'WholesaleKP' - macro terminated!"
    Exit Sub
  End If
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  oa = .Range("A1:D" & lr)
  .Columns(4).ClearContents
  n = Application.Ceiling(lr, 4)
  ReDim wr(1 To n / 4, 1 To 11)
  With .Range("D2:D" & lr)
    .FormulaR1C1 = "=LEFT(RC[-3],4)"
    .Value = .Value
  End With
  For r = 2 To lr
    n = Application.CountIf(.Columns(4), .Cells(r, 4).Value)
    i = i + 1
    For rr = r To r + n - 1
      If InStr(.Cells(rr, 1), "Q1") Then
        wr(i, 1) = .Cells(rr, 4)
        wr(i, 2) = .Cells(rr, 2)
        wr(i, 7) = .Cells(rr, 4)
        wr(i, 8) = .Cells(rr, 3)
      ElseIf InStr(.Cells(rr, 1), "Q2") Then
        wr(i, 1) = .Cells(rr, 4)
        wr(i, 3) = .Cells(rr, 2)
        wr(i, 7) = .Cells(rr, 4)
        wr(i, 9) = .Cells(rr, 3)
      ElseIf InStr(.Cells(rr, 1), "Q3") Then
        wr(i, 1) = .Cells(rr, 4)
        wr(i, 4) = .Cells(rr, 2)
        wr(i, 7) = .Cells(rr, 4)
        wr(i, 10) = .Cells(rr, 3)
      ElseIf InStr(.Cells(rr, 1), "Q4") Then
        wr(i, 1) = .Cells(rr, 4)
        wr(i, 5) = .Cells(rr, 2)
        wr(i, 7) = .Cells(rr, 4)
        wr(i, 11) = .Cells(rr, 3)
      End If
    Next rr
    r = r + n - 1
  Next r
  .Range("A1:D" & lr) = oa
End With
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add().Name = "Results"
With Sheets("Results")
  .UsedRange.ClearContents
  .Cells(1, 1).Resize(, 11) = Array("WholesaleKP", "Q1", "Q2", "Q3", "Q4", "", "RepairsKP", "Q1", "Q2", "Q3", "Q4")
  .Cells(2, 1).Resize(UBound(wr, 1), UBound(wr, 2)) = wr
  .Range("B2:E" & UBound(wr, 1)).NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
  .Range("H2:K" & UBound(wr, 1)).NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
  .Columns.AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.