Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Transpose Data.....I think

  1. #1
    Board Regular
    Join Date
    Sep 2014
    Posts
    92
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Transpose Data.....I think

    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


    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

    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

  2. #2
    MrExcel MVP VBA Geek's Avatar
    Join Date
    Dec 2013
    Location
    Kepler 438B
    Posts
    2,706
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Transpose Data.....I think

    use multiple consolidation range:
    http://www.contextures.com/xlPivot08.html
    https://support.office.com/en-us/art...1-e9fc8adeeeb5

    then double click on the pivot table total
    Last edited by VBA Geek; Feb 19th, 2018 at 09:01 AM.

  3. #3
    Board Regular
    Join Date
    Sep 2014
    Posts
    92
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Transpose Data.....I think

    I can't seem to get it to work.

  4. #4
    Board Regular
    Join Date
    Sep 2014
    Posts
    92
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Transpose Data.....I think

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

  5. #5
    MrExcel MVP VBA Geek's Avatar
    Join Date
    Dec 2013
    Location
    Kepler 438B
    Posts
    2,706
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Transpose Data.....I think

    on which step are you blocked

  6. #6
    Board Regular
    Join Date
    Sep 2014
    Posts
    92
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Transpose Data.....I think

    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.

  7. #7
    MrExcel MVP VBA Geek's Avatar
    Join Date
    Dec 2013
    Location
    Kepler 438B
    Posts
    2,706
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Transpose Data.....I think

    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?
    Last edited by VBA Geek; Feb 19th, 2018 at 09:48 AM.

  8. #8
    Board Regular
    Join Date
    Sep 2014
    Posts
    92
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Transpose Data.....I think

    It is now displayed as

    A 201309
    A 201310
    A 201311

    B 201309
    B 201310
    B 201311

    C 201309
    C 201310
    C 201311

  9. #9
    Board Regular
    Join Date
    Apr 2009
    Location
    Northeast PA, USA
    Posts
    17,519
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Transpose Data.....I think

    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.


    Code:
    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
    Last edited by hiker95; Feb 19th, 2018 at 11:34 AM. Reason: add additional information
    Have a great day,
    hiker95

    Windows 10, Excel 2007, on a PC.

  10. #10
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    32,517
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Transpose Data.....I think

    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)
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •