CODEWITHSUNDEEP
excelexcel-formulaautomationfiltering
Dan
Dan

Reputation: 1

EXCEL: Extracting parts of a list based on one criterion

apologies if this is a repeat question. I don't think I came across the right solution for my case.

I have a list of students who attended different programs and some additional performance info. I'm looking to extract a list with all the info included, based on the programs that attended.

Example

I'd like the filtering to be automatic and update automatically if more students are added to the original list.

From what I read there are a number of solutions, so to provide additional context on my limitations:

I hope I gave you enough info.

Thanks a lot in advance!

Dan

Upvotes: 0

Views: 82

Answers (1)

P.b
P.b

Reputation: 11415

You can turn your data into a table (insert table) and list your values to "filter" on. That way you can use table references inside a INDEX/AGGREGATE function:

=IFERROR(
         INDEX(Table2,
               AGGREGATE(15,6,                                                                                                                         
                              (ROW(Table2[[program]:[program]])
                              /COUNTIF($A$11:$A$12,Table2[[program]:[program]]))
                              -ROW(Table2[#Headers])
                        ,ROW(1:1)),
               COLUMN(Table2[name])-COLUMN(Table2[[name]:[name]])+1)
         ,"")

AGGREGATE calculates the row meeting the conditions (where COUNTIF returns a positive value in return from the listed programs in range A11:A12 in my example)

Since the row number will not equal the indexed table row number I substracted the header row number this also takes into account if the table is not starting at row 1, for instance.

ROW(1:1) is a counter to give the nth smallest value returned (AGGREGATE function 15 stands for SMALL where the 6 stands for Ignore Error Values).

The column calculation is made dynamic just like the row for the indexed values.

And last the IFERROR returns blank value if no more values are found. This formula will not automatically expand like a spill in Officer 365, but you can make it semi-automatic by dragging the formula down up to where you expect future values to reach. If the table expands the values will expand up to where you dragged down.

Since this is an array-formula you need to enter it with ctrl+shift+enter

enter image description here

Upvotes: 3

Related Questions