Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Transposing like all into the same column

  1. #1
    New Member
    Join Date
    Jun 2016
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Transposing like all into the same column

    I have a table like

    date organic referrer direct
    01.01.2019 12345 123 23
    25.01.2019 23456 234 34
    03.03.2019 34567 345 56
    15.04.2019 45678 456 78

    I want to get the following output:

    type amount date
    organic 12345 01.01.2019
    referrer 123 01.01.2019
    direct 23 01.01.2019
    organic 23456 25.01.2019
    referrer 234 25.01.2019
    direct 34 25.01.2019
    organic 34567 03.03.2019
    referrer 345 03.03.2019
    direct 56 03.03.2019
    organic 45678 15.04.2019
    referrer 456 15.04.2019
    direct 78 15.04.2019

    The "normal" transposing is pretty close to what i want - but not exactly, and i miss the point, how to pivot the data to get the goal...


    Last edited by chilly_bang; Sep 9th, 2019 at 09:57 AM.

  2. #2
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,760
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Transposing like all into the same column

    I don't know that you are going to be able use Transpose or a Pivot to get that. Is VBA an acceptable alternative?
    Will there only be these 4 columns, or might there be more?
    Last edited by Joe4; Sep 9th, 2019 at 10:03 AM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    New Member
    Join Date
    Jun 2016
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Transposing like all into the same column

    There could be any amount of rows and columns in the original document. I will be forced to adopt the solution to Google Spreadsheets, so maybe formula or pivoting would be best alternatives - otherwise there will be too much coding. I'm sure, you will be able to get a solution in all methods you mentioned: formula, pivot and VBA - but please, suggesting a solution begin with the simplest one.

  4. #4
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,760
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Transposing like all into the same column

    I am sorry, short of doing it manually, I can only think of a VBA solution. And I do not know anything about Google Sheets. So I don't think I can help you out.

    By the way, the simple example you posted could be done manually quite easily with a bunch of copying and pasting. However, if you have a lot of columns, that could get to be a bit cumbersome.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  5. #5
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,820
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: Transposing like all into the same column

    you can try with PowerQuery (Get&Transform)

    date organic referrer direct date type amount
    01.01.2019
    12345
    123
    23
    01.01.2019 organic
    12345
    25.01.2019
    23456
    234
    34
    01.01.2019 referrer
    123
    03.03.2019
    34567
    345
    56
    01.01.2019 direct
    23
    15.04.2019
    45678
    456
    78
    25.01.2019 organic
    23456
    25.01.2019 referrer
    234
    25.01.2019 direct
    34
    03.03.2019 organic
    34567
    03.03.2019 referrer
    345
    03.03.2019 direct
    56
    15.04.2019 organic
    45678
    15.04.2019 referrer
    456
    15.04.2019 direct
    78


    Code:
    // Table1
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Unpivot = Table.UnpivotOtherColumns(Source, {"date"}, "Attribute", "Value"),
        Rename = Table.RenameColumns(Unpivot,{{"Attribute", "type"}, {"Value", "amount"}})
    in
        Rename
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  6. #6
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,760
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Transposing like all into the same column

    Sandy, looks good, but I am concerned about this part of their request:
    I will be forced to adopt the solution to Google Spreadsheets
    chilly_bang,
    If that is the case, would it be fair to say that you are really looking for a Google Sheets solution, and not an Excel one?
    If so, we should move this thread appropriately.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  7. #7
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,820
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Transposing like all into the same column

    Joe, so I think the best option should be delete my posts and move this thread to appropriate forum
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  8. #8
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,760
    Post Thanks / Like
    Mentioned
    57 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Transposing like all into the same column

    Joe, so I think the best option should be delete my posts and move this thread to appropriate forum
    I want them to confirm whether or not they are really looking for a Google Sheets solution before I do anything.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  9. #9
    Board Regular steve the fish's Avatar
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,648
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Transposing like all into the same column

    Can do it with formula but they are a bit long...

    Place the table in A1:D5. Then in F2:

    =IF(COUNTIF($F$1:F1,INDEX($B$1:$D$1,1,1+MOD(ROWS($A$1:A3),3))) < ROW(OFFSET($A$1,COUNTA($A:$A)-1,0))-1,INDEX($B$1:$D$1,1,1+MOD(ROWS($A$1:A3),3)),"")

    In G2:

    =IFERROR(INDEX($B$2:$D$5,COUNTIF($F$2:F2,F2),MATCH(F2,$B$1:$D$1,0)),"")

    In H2:

    =IFERROR(INDEX($A$2:$A$5,MATCH(1,INDEX(--(G2=INDEX($B$2:$D$5,,MATCH(F2,$B$1:$D$1,0))),0),0)),"")

    There may be better solutions but it seems to produce the same data table as the original. Ranges need to be changed to suit.
    Last edited by steve the fish; Sep 9th, 2019 at 11:16 AM.
    Looking for opportunities

  10. #10
    New Member
    Join Date
    Jun 2016
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Transposing like all into the same column

    Please don't delete anything Indeed, the final suite, where the task will run, is GSheets. But with approaches from Sandy and Steve i will get the goal - it isn't a rocket science to translate things from Excel to GSheets (in case counterparts exist). From this point - this forum is as always just gre

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
  •