Formula to stack rows under each other

[Malcolm torishi]

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

 

[Toadstool]

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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

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))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[jasonb75]

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

In G7, copied right to H7 then filled down

=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

=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

=IF($I7="","",INDEX($B$2:$B$27,MATCH(G7,$A$2:$A$27,0)))

 

[Toadstool]

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

In G7, copied right to H7 then filled down

=IF($I7="","",INDEX(A:A,AGGREGATE(14,6,ROW(A$2:A$27)/($C$2:$C$27=$M7),COUNTIF($M$7:$M7,$M7))))[/QUOTE]

Jason, Where does the M7 come from?

 

[jasonb75]

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.

=IF($I7="","",INDEX(A:A,AGGREGATE(14,6,ROW(A$2:A$27)/($C$2:$C$27=$I7),COUNTIF($I$7:$I7,$I7))))

 

[sandy666]

based on example from post#2 and with PowerQuery

[SIZE=1]// 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[/SIZE]

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​

 

[Malcolm torishi]

based on example from post#2 and with PowerQuery

[SIZE=1]// 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[/SIZE]

Table1

[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Index[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Name[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Time[/COLOR]		[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Index[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]Name[/COLOR]	[COLOR=[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFFFF]#FFFFFF[/URL] ]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​

<tbody>
</tbody>

Thank you everyone, you make it look so easy

 

[sandy666]

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

 

[jasonb75]

because it's easy

I consider that to be offensive to anybody who doesn't find it easy.

 

[sandy666]

I consider that to be offensive to anybody who doesn't find it easy.

I don't think so