Results 1 to 10 of 10

Thread: Formula to stack rows under each other

  1. #1
    Board Regular
    Join Date
    Apr 2013
    Location
    UK
    Posts
    219
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Formula to stack rows under each other

    Hi I’ve been thinking of a way to stack rows of text and numbers under each other but I just can’t get it. Can anyone help with the following :-
    I have an index column say A7:A27, persons name column B7:B27,and a time column C2:C27. Each of these rows can be filled with text and numbers and some of the rows can be completely empty, so eg filled rows could be rows 9,14,18, and 20 with the rest being empty. What I am looking to do next but just don’t know how is to take all of the complete 4 rows that are filled with data and stack them under each other starting with the earliest time first in column say G7, so if I have 4 different times under column C , I what the complete row of data staked in time order from G7 down wards so that the latest time would be at the bottom of the stack
    if anyone can help thank you

  2. #2
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    227
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Formula to stack rows under each other

    I'm assuming you want the three columns listed (Index, Name and Time) so here's my solution with some sample data:

    A B C D E F G H I
    1 Index Name Time
    2 X12 Bert 0:35
    3 Z99 Susan 22:33
    4 X23 Vanessa 0:35
    5
    6 Index Name Time Sorted
    7 Z78 Brandi 22:45 X23 Vanessa 0:35
    8 X12 Xavier 22:11 X12 Bert 0:35
    9 X23 Brandi 1:00
    10 Z99 Roger 1:00
    11 W98 John 2:30 X12 Charlie 1:00
    12 W98 John 2:30
    13 X34 Alf 16:25
    14 X12 Charlie 1:00 Z77 Xavier 17:00
    15 Z99 Roger 1:00 X12 Xavier 22:11
    16 X23 Brandi 1:00 Z99 Susan 22:33
    17 Z77 Xavier 17:00 Z78 Brandi 22:45
    18 X34 Alf 16:25
    MT1

    Worksheet Formulas
    Cell Formula
    G7 =IF(ROW()-ROW($G$6)-COUNTA($C$2:$C$27) > 0,"",INDEX(A$2:A$27,AGGREGATE(15,6,ROW($C$2:$C$27)-ROW($C$1)/($C$2:$C$27=I7),COUNTIF($I7:$I$26,I7))))
    H7 =IF(ROW()-ROW($H$6)-COUNTA($C$2:$C$27) > 0,"",INDEX($B$2:$B$27,AGGREGATE(15,6,ROW($C$2:$C$27)-ROW($C$1)/($C$2:$C$27=I7),COUNTIF($I7:$I$26,I7))))
    I7 =IF(COUNTA($C$2:$C$27) < ROW()-ROW($I$6),"",AGGREGATE(15,6,$C$2:$C$27,ROW()-ROW($I$6)))
    Life advice found on a book of matches: "Keep cool. Keep away from children."

  3. #3
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,665
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula to stack rows under each other

    Using the same sample data as Toadstool, but with simplified formulas.

    In G7, copied right to H7 then filled down
    Code:
    =IF($I7="","",INDEX(A:A,AGGREGATE(14,6,ROW(A$2:A$27)/($C$2:$C$27=$M7),COUNTIF($M$7:$M7,$M7))))
    In I7, filled down
    Code:
    =IFERROR(SMALL(C$2:C$27,ROWS(I$7:I7)),"")
    If there are never duplicates in the index column (column A) then the formula in H7 could be further simplified to
    Code:
    =IF($I7="","",INDEX($B$2:$B$27,MATCH(G7,$A$2:$A$27,0)))

  4. #4
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    227
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Formula to stack rows under each other

    Quote Originally Posted by jasonb75 View Post
    Using the same sample data as Toadstool, but with simplified formulas.

    In G7, copied right to H7 then filled down [CODE]=IF($I7="","",INDEX(A:A,AGGREGATE(14,6,ROW(A$2:A$27)/($C$2:$C$27=$M7),COUNTIF($M$7:$M7,$M7))))
    Jason, Where does the M7 come from?
    Life advice found on a book of matches: "Keep cool. Keep away from children."

  5. #5
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,665
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula to stack rows under each other

    Quote Originally Posted by Toadstool View Post
    Jason, Where does the M7 come from?
    Oops!
    M7 should have been I7, I copied your sample to a blank sheet, then moved your results right by 4 columns, your times were in column M, mine in column I.
    Code:
    =IF($I7="","",INDEX(A:A,AGGREGATE(14,6,ROW(A$2:A$27)/($C$2:$C$27=$I7),COUNTIF($I$7:$I7,$I7))))

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

    Cool Re: Formula to stack rows under each other

    based on example from post#2 and with PowerQuery

    Code:
    // Table1
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        RBR = Table.SelectRows(Table.TransformColumnTypes(Source,{{"Time", type time}}), each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
        Sort = Table.Sort(RBR,{{"Time", Order.Ascending}, {"Name", Order.Ascending}, {"Index", Order.Ascending}})
    in
        Sort
    Table1
    Index Name Time Index Name Time
    X12 Bert
    00:35
    X12 Bert
    00:35
    Z99 Susan
    22:33
    X23 Vanessa
    00:35
    X23 Vanessa
    00:35
    X23 Brandi
    01:00
    X12 Charlie
    01:00
    Z99 Roger
    01:00
    Z78 Brandi
    22:45
    W98 John
    02:30
    X12 Xavier
    22:11
    X34 Alf
    16:25
    Z77 Xavier
    17:00
    X12 Xavier
    22:11
    W98 John
    02:30
    Z99 Susan
    22:33
    Z78 Brandi
    22:45
    X12 Charlie
    01:00
    Z99 Roger
    01:00
    X23 Brandi
    01:00
    Z77 Xavier
    17:00
    X34 Alf
    16:25
    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
    I don't use vba in any form!

  7. #7
    Board Regular
    Join Date
    Apr 2013
    Location
    UK
    Posts
    219
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula to stack rows under each other

    Quote Originally Posted by sandy666 View Post
    based on example from post#2 and with PowerQuery

    Code:
    // Table1
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        RBR = Table.SelectRows(Table.TransformColumnTypes(Source,{{"Time", type time}}), each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null}))),
        Sort = Table.Sort(RBR,{{"Time", Order.Ascending}, {"Name", Order.Ascending}, {"Index", Order.Ascending}})
    in
        Sort
    Table1
    [COLOR=#FFFFFF ]Index[/COLOR] [COLOR=#FFFFFF ]Name[/COLOR] [COLOR=#FFFFFF ]Time[/COLOR] [COLOR=#FFFFFF ]Index[/COLOR] [COLOR=#FFFFFF ]Name[/COLOR] [COLOR=#FFFFFF ]Time[/COLOR]
    X12 Bert
    00:35
    X12 Bert
    00:35
    Z99 Susan
    22:33
    X23 Vanessa
    00:35
    X23 Vanessa
    00:35
    X23 Brandi
    01:00
    X12 Charlie
    01:00
    Z99 Roger
    01:00
    Z78 Brandi
    22:45
    W98 John
    02:30
    X12 Xavier
    22:11
    X34 Alf
    16:25
    Z77 Xavier
    17:00
    X12 Xavier
    22:11
    W98 John
    02:30
    Z99 Susan
    22:33
    Z78 Brandi
    22:45
    X12 Charlie
    01:00
    Z99 Roger
    01:00
    X23 Brandi
    01:00
    Z77 Xavier
    17:00
    X34 Alf
    16:25
    Thank you everyone, you make it look so easy

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

    Cool Re: Formula to stack rows under each other

    Quote Originally Posted by Malcolm torishi View Post
    you make it look so easy
    because it's easy

    you can do that without any formula or code

    change range to table
    in Filter uncheck : blanks
    sort ascending Time column
    Last edited by sandy666; Sep 7th, 2019 at 08:25 AM.
    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
    I don't use vba in any form!

  9. #9
    Board Regular
    Join Date
    Dec 2008
    Posts
    6,665
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula to stack rows under each other

    Quote Originally Posted by sandy666 View Post
    because it's easy
    I consider that to be offensive to anybody who doesn't find it easy.

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

    Default Re: Formula to stack rows under each other

    Quote Originally Posted by jasonb75 View Post
    I consider that to be offensive to anybody who doesn't find it easy.
    I don't think so
    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
    I don't use vba in any form!

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
  •