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

Thread: Split single column (NO delimiters) into multiple columns

  1. #1
    Seeker abaker77's Avatar
    Join Date
    Oct 2011
    Location
    Toronto, Canada
    Posts
    5
    Articles
    0
    Excel Version
    Office 365

    Split single column (NO delimiters) into multiple columns



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello,
    I have a worksheet with a single column of items, e.g. item001 - item100.
    The number of items in this single column could vary, anywhere from 10 to 500 items.
    The objective is to split that column into multiple columns.
    I can't use Text-to-columns because there's no delimiter and I'm not splitting the item descriptions at all.
    I was hoping there was a way that Power Query could do the trick without too much complication.
    To illustrate, if I had 100 items and wanted to split it into 5 columns of 20 rows each, the resulting 5 columns should start with:
    item001, item021, item041, item061, item081
    The numer of split columns and rows might vary, e.g. instead of 5 columns of 20 rows, I might need 3 columns of 33 rows (with 1 spilling into a 4th column.)
    I believe I can do this with a formula, for instance:
    my original column starts in A6 (for 400 items) and I want to start my split in cell C6, and the # of rows I want in each column = num_rows
    =INDEX($A$6:$A$406,ROW(C6)-5+(num_rows*(COLUMNS($C$6:C$6)-1)))
    This formula apparently works but I have to subtract 5 (since I'm starting on row6) *and* I have to replicate the formula manually just num_rows down and to the right enough to accommodate all my items.
    I have attached a simple worksheet to illustrate what I'm trying to achieve.
    THANK YOU !
    Attached Files Attached Files

  2. #2
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    305
    Articles
    0
    Excel Version
    2019
    I am close. Would this presentation work for you

    Data Range
    A
    B
    C
    D
    E
    1
    Custom
    Custom.1
    Custom.2
    Custom.3
    Custom.4
    2
    item001
    item002
    item003
    item004
    item005
    3
    item006
    item007
    item008
    item009
    item010
    4
    item011
    item012
    item013
    item014
    item015
    5
    item016
    item017
    item018
    item019
    item020
    6
    item021
    item022
    item023
    item024
    item025
    7
    item026
    item027
    item028
    item029
    item030
    8
    item031
    item032
    item033
    item034
    item035
    9
    item036
    item037
    item038
    item039
    item040
    10
    item041
    item042
    item043
    item044
    item045
    11
    item046
    item047
    item048
    item049
    item050
    12
    item051
    item052
    item053
    item054
    item055
    13
    item056
    item057
    item058
    item059
    item060
    14
    item061
    item062
    item063
    item064
    item065
    15
    item066
    item067
    item068
    item069
    item070
    16
    item071
    item072
    item073
    item074
    item075
    17
    item076
    item077
    item078
    item079
    item080
    18
    item081
    item082
    item083
    item084
    item085
    19
    item086
    item087
    item088
    item089
    item090
    20
    item091
    item092
    item093
    item094
    item095
    21
    item096
    item097
    item098
    item099
    item100


    Code:
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"orig_col", type text}}),
        #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
        #"Inserted Modulo" = Table.AddColumn(#"Added Index", "Modulo", each Number.Mod([Index], 5), type number),
        #"Removed Columns" = Table.RemoveColumns(#"Inserted Modulo",{"Index"}),
        #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each if[Modulo]=0 then [orig_col] else null),
        #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if[Modulo]=1 then[orig_col] else null),
        #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each if[Modulo]=2 then[orig_col] else null),
        #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Custom.3", each if [Modulo]= 3 then[orig_col] else null),
        #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Custom.4", each if [Modulo]=4 then [orig_col] else null),
        #"Filled Up" = Table.FillUp(#"Added Custom4",{"Custom.1", "Custom.2", "Custom.3", "Custom.4"}),
        #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Custom] <> null)),
        #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows",{"orig_col", "Modulo"})
    in
        #"Removed Columns1"

  3. #3
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    104
    Articles
    0
    Excel Version
    Office 365
    For a dynamic solution, one parameter for the number of columns is sufficient. From this, the number of required rows results automatically.

  4. #4
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    104
    Articles
    0
    Excel Version
    Office 365
    @alansidman

    Your solutin shows a sequence Col A to E and Row 1 to 20 insteasd of Row 1 to 20 and Col A to E. Also it seems this solution is not dynamic in definition of required columns.
    Last edited by pinarello; 2021-03-28 at 10:44 AM.

  5. #5
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,625
    Articles
    0
    Excel Version
    Office 365 Subscription
    @pinarrllo

    The way I read the requirements, I think the number of ROWS is more important than the number of columns, so your solution isn't quite what's required, either.
    Ali
    Enthusiastic self-taught user of MS Excel!

  6. #6
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    104
    Articles
    0
    Excel Version
    Office 365
    Hello Ali,

    that may be the case, of course. I had also realized it in the First Approach for a dynamic number of rows. But for me personally I came to the conclusion that the prirority is on the number of columns and therefore I changed it again.

    But in the end it doesn't matter whether the prioritization lies with rows or columns, because I can create a solution for both questions without any problems. Especially since my first solution was a bit easier to create.
    Last edited by pinarello; 2021-03-28 at 11:59 AM.

  7. #7
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,625
    Articles
    0
    Excel Version
    Office 365 Subscription
    Can you do that, please? I'm curious to see how it's done. Using parameters with PQ is something I'm still learning to do. Thanks!
    Ali
    Enthusiastic self-taught user of MS Excel!

  8. #8
    Conjurer alansidman's Avatar
    Join Date
    Oct 2018
    Location
    Steamboat Springs
    Posts
    305
    Articles
    0
    Excel Version
    2019
    Code:
    Your solutin shows a sequence Col A to E and Row 1 to 20 insteasd of Row 1 to 20 and Col A to E. 
    I am aware of this which is why I asked the OP if it was an acceptable solution. I was unable to think of a way to offer it up in the manner asked. I, too, am anxious to see your dynamic solution.

  9. #9
    Conjurer pinarello's Avatar
    Join Date
    Jun 2019
    Location
    Germany
    Posts
    104
    Articles
    0
    Excel Version
    Office 365
    I have now inserted a second query into the workbook, in which the number of desired rows is specified. In both queries, however, the control is only by quasi parameters, since these can only be used in the respective query. Real parameters are created separately and can then be addressed and used in all queries of a workbook.

    By the way, there is nothing wrong with presenting solutions that do not represent the optimum. Because those who deal with a question and develop their own solution learn much more in the end than those who just remain passive.

    When I started to develop formula solutions in a German forum some years ago, at the beginning all other solutions were much better than mine. But I was able to learn from the better solutions every time in the process.

    Then in December 2018 I came, more or less by chance to Power Query and at first I had only understood train station. But because I tried from the beginning to develop solutions to all kinds of questions with Power Query, I had the first success relatively quickly.

  10. #10
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,913
    Articles
    0
    Excel Version
    365
    You're using Office 365? Then in any cell:
    =TRANSPOSE(INDEX(A6:A105,SEQUENCE(D3,D2)))

Page 1 of 2 1 2 LastLast

Posting Permissions

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