Autosort a list removing spaces

[St Jimmy]

I am trying to create a dynamic column in excel that will serve as an index for a graph.
I have a list of 4000 items that have been registered over the past 10 years.
I'd like to keep the list intact, but create a new list in a column that meets criteria.
Easy so far, but I'd like there to be no gaps between criteria meeting cells and I'd like to use formulas to get it done. Not VBA.

ID	Use	Age	List
A100	Valid	60	A103
A101	Valid	45	A104
A102	Invalid	28
A103	Valid	25
A104	Valid	21

<tbody>
</tbody>

Based on the example above, if the Use column = Valid AND if the Age column is less than 30 the List column will populate the ID associated with the item meeting criteria. A103 and A104 in the example above.

Any thoughts?

Jimmy

 

[Fluff]

Based on the Formula that @FormR supplied here https://www.mrexcel.com/forum/excel...matic-resorting-list-out-vba.html#post5166104

try

Excel 2013/2016
	A	B	C	D
1	ID	Use	Age	List
2	A100	Valid	60	A103
3	A101	Valid	45	A104
4	A102	Invalid	28
5	A103	Valid	25
6	A104	Valid	21
Sheet1

Cell Formulas
Range	Formula
D2	=INDEX($A$2:$A$6,AGGREGATE(15,6,((ROW($A$2:$A$6)-ROW($A$2)+1)/(($B$2:$B$6="Valid")*(C$2:C$6<30))),ROWS($H$2:$H2)))

 

[St Jimmy]

Thank you Fluff!
I still can't wrap my head around how the denominator (($B$2:$B$6="Valid")*(C$2:C$6<30)) works.
Is it making an array? A list? An explanation would be greatly appreciated.

Thanks again!

 

[Fluff]

It returns an array with the data shown above

this ($B$2:$B$6="Valid") returns
TRUE;TRUE;FALSE;TRUE;TRUE

this (C$2:C$6<30) gives
FALSE;FALSE;TRUE;TRUE;TRUE

when multiplied together true gives 1 & false give 0 so you get
0;0;0;1;1

As that's the divisor you end up with
#DIV/0!;#DIV/0!;#DIV/0!,4;5

As the aggregate function is set to ignore errors you end up with
4;5

I find the easiest way of trying to understand this sort of formulae s to use "Evaluate Formula" on the formula tab

HTH

 

[St Jimmy]

It did help a lot, thank you!!!

 

[Fluff]

You're welcome