Display list based on specific criteria

Question

I have a data set where each data point has a specific property attached. Now I wish to create an auto-updating list of data points based on the property value of the data point.

	A	B	C	D
1	Property	Value	[=IF($A$1:$a$7="Phase";OFFSET($B$2;;;COUNTA($B$2:$B$7));"")]	[=INDEX($C$2:$C$7;MATCH(SMALL(IFERROR(SEARCH(;$C$2:$C$7);2^32)+ROW($C$2:$C$7)/1000;ROWS($C$2:$C$7));IFERROR(SEARCH(;$C$2:$C$7);2^32)+ROW($C$2:$C$7)/1000;0))]
2	Task	Task A1		Phase B
3	Phase	Phase B	Phase B	Phase C
4	Task	Task B1
5	Task	Task B2
6	Phase	Phase C	Phase C
7	Task	Task C1

So while I wish to keep the original data set untouched, I now want to create a list that displays only values where property = Phase.

The list has to update automatically and cannot be a table. So far I've got a solution that consists of two steps, but I'd like it to be just one step to avoid having a middle stage that needs to be hidden somewhere in the worksheet.

So step one is to identify all values where property = Phase. For that I've created this formula in cell C2:

=IF($A$1:$a$7="Phase";OFFSET($B$2;;;COUNTA($B$2:$B$7));"")

This creates a list that only displays values where property = Phase. However, the list contains blanks in the lines where property ≠Phase. So to create a list where the blanks are omitted and the displayed values are put together, I've created a second formula that search out cells with data and create a unique number value based on the row number for for each of those cells, while creating a different value for empty cells. Then it matches the unique value with the matching value in the specific cell. This creates a list that condenses the result in column C into one auto updating list in column D.

=INDEX($C$2:$C$7;MATCH(SMALL(IFERROR(SEARCH(;$C$2:$C$7);2^32)+ROW($C$2:$C$7)/1000;ROWS($C$2:$C$7));IFERROR(SEARCH(;$C$2:$C$7);2^32)+ROW($C$2:$C$7)/1000;0))

So in those two steps I got to what I wanted. Now I just want to somehow combine the two steps into one single formula. But for the life of me, I can't figure it out...

Answer 1

Hi there

You may try the formula as in the picture below

=IFERROR(INDEX(B:B,AGGREGATE(15,3,1/(A:A="Phase")*ROW(A:A),ROW(1:1))),"") and copy/drag it down

Note:

I noticed you are using a semi-colon as a formula separator.

So adapt the formula and ranges according to your real scenario.

I hope this helps you and gives a solution to your problem

Do let me know if you need more help

Regards

Jeovany