How to return list from values skipping blanks

**ARayburn** · 05-17-2013, 09:01 AM

I have a list of items in column A and a list of their status in Column B. I would like to return a separate list that just shows which items have a specific status. I would like this list to be just as long as items that meet the criteria, meaning no blank rows. Attached is an example. Here the status is either blank or says "ATTENTION" I would like to have a list of products that just say attention. Any help is MUCH appreciated.

List Function.xlsx

**Pepe Le Mokko** · 05-17-2013, 09:05 AM

You can sort by Col B and delete the blank rows
or
Use the filter to do the same

**Melvinrobb** · 05-17-2013, 09:10 AM

Is the attached what you are looking for?

**Tony Valko** · 05-17-2013, 09:29 AM

Enter this array formula** in D2:

=IFERROR(INDEX(A:A,SMALL(IF(B$2:B$19="Attention",ROW(B$2:B$19)),ROWS(D$2:D2))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down until you get blanks.

**ARayburn** · 05-17-2013, 09:37 AM

Thank you so much everyone, solved!

**Tony Valko** · 05-17-2013, 09:41 AM

You're welcome. Thanks for the feedback!

**RFonteyn** · 05-23-2013, 12:04 PM

Hello Tony,

could you do the same but for values along a row rather than down a column?

**Tony Valko** · 05-23-2013, 04:35 PM

The forum owner would prefer that you start your own thread rather than asking your question in someone elses thread.

If you start a new thread and I see it I will gladly assist you.