# Thread: Formula to stack rows under each other Thanks: 0 Likes:  1 Post #5338621 (1)

1. ## 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. ## 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)))

3. ## 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. ## Re: Formula to stack rows under each other

Originally Posted by jasonb75
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?

5. ## Re: Formula to stack rows under each other

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. ## 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

7. ## Re: Formula to stack rows under each other

Originally Posted by sandy666
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. ## Re: Formula to stack rows under each other

Originally Posted by Malcolm torishi
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

9. ## Re: Formula to stack rows under each other

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

10. ## Re: Formula to stack rows under each other

Originally Posted by jasonb75
I consider that to be offensive to anybody who doesn't find it easy.
I don't think so