Transpose Data.....I think

[brandon16]

Hi Guy's

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

[TABLE="width: 436"]
<colgroup><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD] [/TD]
[TD="align: right"]201309[/TD]
[TD="align: right"]201310[/TD]
[TD="align: right"]201311[/TD]
[TD="align: right"]201312[/TD]
[TD="align: right"]201401[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]13000[/TD]
[TD="align: right"]18266[/TD]
[TD="align: right"]15300[/TD]
[TD="align: right"]7250[/TD]
[TD="align: right"]25050[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]15568.9904[/TD]
[TD="align: right"]15000[/TD]
[TD="align: right"]16900[/TD]
[TD="align: right"]47040[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]8750[/TD]
[TD="align: right"]126082.4[/TD]
[/TR]
</tbody>[/TABLE]


to this very quickly please?

[TABLE="width: 244"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD="align: right"]201309[/TD]
[TD="align: right"]13000[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]201309[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]201309[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]201310[/TD]
[TD="align: right"]18266[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]201310[/TD]
[TD="align: right"]15569[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]201310[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]201311[/TD]
[TD="align: right"]15300[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]201311[/TD]
[TD="align: right"]15000[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]201311[/TD]
[TD="align: right"]0[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]201312[/TD]
[TD="align: right"]7250[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]201312[/TD]
[TD="align: right"]16900[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]201312[/TD]
[TD="align: right"]8750[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]201401[/TD]
[TD="align: right"]25050[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]201401[/TD]
[TD="align: right"]47040[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]201401[/TD]
[TD="align: right"]126082[/TD]
[/TR]
</tbody>[/TABLE]

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)