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

Thread: How to transpose(?) column to desired output

  1. #1
    Board Regular
    Join Date
    Jul 2019
    Location
    Win 10, Office Professional Plus 2016
    Posts
    113
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to transpose(?) column to desired output

    ABCDEFGH
    1INV
    2268385
    329-May-19
    4JOB: T00009719
    528-Jun-19
    61,394.52
    71,394.52
    81,394.52
    9INV
    10268466
    1129-May-19
    12JOB: T00009709
    1328-Jun-19
    141,947.08
    151,947.08
    163,341.60
    17
    18INV26838529-May-19JOB: T0000971928-Jun-191,394.521,394.521,394.52
    19INV26846629-May-19JOB: T0000970928-Jun-191,947.081,947.083,341.60

    Sheet1





    Need to get the column into desired output.
    FWIW It doesn't matter how many rows in between the data in the desired output.
    As long as I can get one invoices details into one row, I am happy.
    How best can I get about this?

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

    Cool Re: How to transpose(?) column to desired output

    try PowerQuery aka Get&Transform:

    Column1 Custom.1 Custom.2 Custom.3 Custom.4 Custom.5 Custom.6 Custom.7 Custom.8
    INV INV
    268385
    29/05/2019
    JOB: T00009719
    28/06/2019
    1394.52
    1394.52
    1394.52
    268385
    INV
    268466
    29/05/2019
    JOB: T00009709
    28/06/2019
    1947.08
    1947.08
    3341.6
    29-May-19
    JOB: T00009719
    28-Jun-19
    1,394.52
    1,394.52
    1,394.52
    INV
    268466
    29-May-19
    JOB: T00009709
    28-Jun-19
    1,947.08
    1,947.08
    3,341.60


    Code:
    // Table1
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        TypeText = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
        Index = Table.AddIndexColumn(TypeText, "Index", 0, 1),
        Divide = Table.TransformColumns(Index, {{"Index", each Number.IntegerDivide(_, 8), Int64.Type}}),
        Group = Table.Group(Divide, {"Index"}, {{"Count", each _, type table}}),
        List = Table.AddColumn(Group, "Custom", each Table.Column([Count],"Column1")),
        Extract = Table.TransformColumns(List, {"Custom", each Text.Combine(List.Transform(_, Text.From), "="), type text}),
        Split = Table.SplitColumn(Extract, "Custom", Splitter.SplitTextByDelimiter("=", QuoteStyle.Csv), {"Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5", "Custom.6", "Custom.7", "Custom.8"}),
        TypeNumber = Table.TransformColumnTypes(Split,{{"Custom.2", type number}, {"Custom.6", type number}, {"Custom.7", type number}, {"Custom.8", type number}, {"Custom.3", type datetime}, {"Custom.5", type datetime}}),
        TypeDate = Table.TransformColumnTypes(TypeNumber,{{"Custom.3", type date}, {"Custom.5", type date}}),
        ROC = Table.SelectColumns(TypeDate,{"Custom.1", "Custom.2", "Custom.3", "Custom.4", "Custom.5", "Custom.6", "Custom.7", "Custom.8"})
    in
        ROC
    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

  3. #3
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: How to transpose(?) column to desired output

    If the previous post has not resolved your issue ..

    1. Does every INV section contain exactly 8 rows like your samples?

    2. Are you looking for a particular approach (eg Formulas, Macro, PowerQuery)?

    3. About how big is the data likely to be (that is, total rows in the original data)?
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

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

    Cool Re: How to transpose(?) column to desired output

    with formula:

    A
    B
    C
    D
    E
    F
    G
    H
    I
    J
    1
    INV INV
    268385
    29/05/2019
    JOB: T00009719
    28/06/2019
    1394.52
    1394.52
    1394.52
    2
    268385
    INV
    268466
    29/05/2019
    JOB: T00009709
    28/06/2019
    1947.08
    1947.08
    3341.6
    3
    29/05/2019
    4
    JOB: T00009719
    5
    28/06/2019
    6
    1394.52
    7
    1394.52
    8
    1394.52
    9
    INV
    10
    268466
    11
    29/05/2019
    12
    JOB: T00009709
    13
    28/06/2019
    14
    1947.08
    15
    1947.08
    16
    3341.6


    C1: =OFFSET($A$1,(8*(ROW()-ROW(C$1)))+(COLUMN()-COLUMN($C1)),0,1,1)
    drag 8 to the right / and in this case 2 down
    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

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

    Cool Re: How to transpose(?) column to desired output

    or formula: =IF((8*(ROW()-ROW(L$1)))+(COLUMN()-COLUMN($L1))>=ROWS($A$1:$A$16),"",OFFSET($A$1,(8*(ROW()-ROW(L$1)))+(COLUMN()-COLUMN($L1)),0,1,1))
    can be used when the number of elements in the source column is variable but some maximum number of elements is known.
    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
    Board Regular
    Join Date
    Jul 2019
    Location
    Win 10, Office Professional Plus 2016
    Posts
    113
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to transpose(?) column to desired output

    Quote Originally Posted by Peter_SSs View Post

    1. Does every INV section contain exactly 8 rows like your samples?
    Hi Peter,
    I just had a look at the data and not every INV section is exactly 8 rows. Most of them are 8 rows but there a handful that are 9 or 10 rows.
    https://1drv.ms/x/s!AvjBsEPEq12ngSf1Y0NtNKb5St6R?e=gMv2u3

    FWIW this is the PDF of the data I copied and pasted to Excel: https://ibb.co/RNBPczT
    I suppose I could copy/paste the data which produce 8 rows (and use Sandy's formulas) and manually work on the 9 or 10 row producing entries.

    Final output is about 60 rows.
    The 'raw' data of one column has about 400 rows.

    Using formulas or Power Query would be the preferred method.

  7. #7
    Board Regular
    Join Date
    Jul 2019
    Location
    Win 10, Office Professional Plus 2016
    Posts
    113
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to transpose(?) column to desired output

    Quote Originally Posted by sandy666 View Post
    try PowerQuery aka Get&Transform
    Hi Sandy - thanks for the M code and formulas
    They work beautifully

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

    Default Re: How to transpose(?) column to desired output

    You are welcome

    Have a nice day
    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

  9. #9
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,976
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: How to transpose(?) column to desired output

    Quote Originally Posted by danhendo888 View Post
    .. not every INV section is exactly 8 rows.

    Using formulas or Power Query would be the preferred method.
    Here is a formula method that would cope with different size sections.
    To keep the formulas a bit shorter I have used two helper columns (B:C) which could be hidden once populated.
    Formulas in B1, C1 & D1 copied down as far as you might ever need (after adjusting the $400 if needed)
    Formula in E1 is copied across as far as you might ever need and down as far as the earlier formulas.
    You may need to format the date and number columns appropriately after copying the formulas.

    Transpose

    ABCDEFGHIJKLMN
    1INV18INV26838529-May-19JOB: T0000971928-Jun-191,394.521,394.521,394.52
    226838599INV26846629-May-19JOB: T0000970928-Jun-191,947.081,947.083,341.605,698.00
    329-May-19187INV26846730-May-19JOB: T000097109-Jun-192,365.22666.66
    4JOB: T00009719
    528-Jun-19
    61,394.52
    71,394.52
    81,394.52
    9INV
    10268466
    1129-May-19
    12JOB: T00009709
    1328-Jun-19
    141,947.08
    151,947.08
    163,341.60
    175,698.00
    18INV
    19268467
    2030-May-19
    21JOB: T00009710
    229-Jun-19
    232365.22
    24666.66
    25

    Spreadsheet Formulas
    CellFormula
    B1=IFERROR(AGGREGATE(15,6,(ROW(A$1:A$400)-ROW(A$1)+1)/(A$1:A$400="INV"),ROWS(B$1:B1)),"")
    C1=IF(B1="","",IF(ISNUMBER(B2),B2,COUNTA(A$1:A$400)+1)-B1)
    D1=IF(B1="","","INV")
    E1=IF($B1="","",IF(COLUMNS($D1:E1)>$C1,"",INDEX($A$1:$A$400,$B1+COLUMNS($E1:E1))))


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  10. #10
    Board Regular
    Join Date
    Jul 2019
    Location
    Win 10, Office Professional Plus 2016
    Posts
    113
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to transpose(?) column to desired output

    Quote Originally Posted by Peter_SSs View Post
    Here is a formula method that would cope with different size sections
    Hi Peter,
    Thank you for this awesome solution, I appreciate it
    This is something I will be using many times a week. Simply amazing.

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
  •