Get data from non-blank cells in a range

[Josiah]

I have 6 cells...Let's say A1 - A6. In cell A1, I want it to show the data from the first NON-BLANK cell in the range B1-B6. Cell A2 needs to show the data from the SECOND NON-BLANK cell in B1-B6. A3 shows data from 3rd non-blank cell in B1-B6...and so on...

Sounds simple (and probably is) but it's kicking my butt. Any help?

 

[Ron Coderre]

It's important to post the version of Excel you're using so we can offer appropriate solutions.

If you have Excel 2007 or later, try this regular formula:

A1: =IFERROR(INDEX(B:B,SMALL(INDEX(ROW($B$1:$B$6)*($B$1:$B$6<>""),0)
,ROWS($1:1)+COUNTBLANK($B$1:$B$6))),"")

otherwise..this regular formula

A1: =IF(ROWS($1:1)<=COUNTA($B$1:$B$6),INDEX(B:B,SMALL(INDEX(ROW($B$1:$B$6)
*($B$1:$B$6<>""),0),ROWS($1:1)+COUNTBLANK($B$1:$B$6))),"")

Copy either formula down through A6

Does that help?

 

[Josiah]

Oh heck yes that works (first one, sorry!). Thanks a TON!!

 

[Ron Coderre]

Oh heck yes that works (first one, sorry!). Thanks a TON!!

Glad to help...
Here's another regular-formula approach to consider...

A1: =IFERROR(INDEX($B$1:$B$6,MATCH(TRUE,INDEX($B$1:$B$6<>"",0),0)),"")

A2: =IFERROR(INDEX($B$1:$B$6,MATCH(1,INDEX(($B$1:$B$6<>"")
*(COUNTIF(A$1:A1,$B$1:$B$6)=0),0),0)),"")

Copy A2 down through A6

 

[adamb22]

This set of formulas does almost exactly what I am looking for, but I am having trouble adapting it for my own use. I am using Excel 2012 and have a sheet that IDs certain locations based on specific criteria. These locations are tagged with "Watch List" in column O.

I would like to use a formula to return only the store ID# (column D) of the stores flagged with "Watch List" in column O.

I did not want to start a new string if it wasn't necessary, but I can't seem to modify the formulas to meet my needs. Any help/insights you could provide would be greatly appreicated.

Thanks!