Transpose Data.....I think

[brandon16]

Hi Guy's

Any idea how to I turn my data output from this....

	201309	201310	201311	201312	201401
A	13000	18266	15300	7250	25050
B	0	15568.9904	15000	16900	47040
C	0	0	0	8750	126082.4

<colgroup><col><col><col><col span="3"></colgroup><tbody>
</tbody>

to this very quickly please?

A	201309	13000
B	201309	0
C	201309	0
A	201310	18266
B	201310	15569
C	201310	0
A	201311	15300
B	201311	15000
C	201311	0
A	201312	7250
B	201312	16900
C	201312	8750
A	201401	25050
B	201401	47040
C	201401	126082

<colgroup><col><col><col></colgroup><tbody>
</tbody>

At the moment I am having to do it manually and I have a lot of data, I am hoping there is a quick win.

Thanks

 

[VBA Geek]

use multiple consolidation range:
http://www.contextures.com/xlPivot08.html
https://support.office.com/en-us/ar...vottable-3ae257d2-ca94-49ff-a481-e9fc8adeeeb5

then double click on the pivot table total

 

[brandon16]

I can't seem to get it to work.

 

[brandon16]

Or can I do a H/Vlookup on the variable ABC and Date to get the value?

 

[VBA Geek]

on which step are you blocked

 

[brandon16]

I select the whole table and then when it come to selecting the drop down on the Pivot Wizard (Field one, field two etc) they are all blank. So I just end up with a table that looks exactly the same but in a pivot.

 

[VBA Geek]

if you got as far as the pivot table, then double click on the grand total, the cell in bottom right corner. what happens if you do?

 

[brandon16]

It is now displayed as

A 201309
A 201310
A 201311

B 201309
B 201310
B 201311

C 201309
C 201310
C 201311

 

[hiker95]

brandon16,

Here is a macro solution for you to consider that uses two arrays in memory.

With your raw data in the active worksheet range A1:F4, the results will be written beginning in the third column to the right of the last used column of your raw data.

Sub ReorganizeData()
' hiker95, 02/19/2018, ME1044107
Application.ScreenUpdating = False
Dim a As Variant, r As Long, c As Long, lr As Long, lc As Long, n As Long
Dim o As Variant, j As Long
With ActiveSheet
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  lc = .Cells(1, Columns.Count).End(xlToLeft).Column
  a = .Range(.Cells(1, 1), .Cells(lr, lc))
  n = (lr - 1) * (lc - 1)
  ReDim o(1 To n, 1 To 3)
  For c = 2 To lc
    For r = 2 To lr
      j = j + 1: o(j, 1) = a(r, 1): o(j, 2) = a(1, c): o(j, 3) = a(r, c)
    Next r
  Next c
  With .Cells(1, lc + 3).Resize(UBound(o, 1), UBound(o, 2))
    .Value = o
    .NumberFormat = "0"
  End With
  .UsedRange.Columns.AutoFit
End With
Application.ScreenUpdating = True
End Sub

 

[Rick Rothstein]

You can do this using formulas if you wish. Assuming your data example is in cells A1:F4, put these formulas in three adjacent cells (for your layout, I am using Columns H, I and J) and then copy them down...

H1: =INDEX(A$2:A$4,MOD(ROWS($1:1)-1,3)+1)

I1: =INDEX(B$1:F$1,1,INT((ROWS($1:1)-1)/3)+1)

J1: =INDEX($B$2:$F$4,MOD(ROWS($1:1)-1,3)+1,INT((ROWS($1:1)-1)/3)+1)