Looking Up Specific Non-Blank Text Data in a Range

  • Thread starter Thread starter msinex
  • Start date Start date
M

msinex

I have data in a specific column in which some cells have text data i
them and some are blank. Is there a function in which I can have in
cell the first datapoint in the range that is not blank, then th
second cell would have the second datapoint that is not blank and so o
and so forth? Thanks, this has been driving me crazy
 
Hi
you may try the following array formula (entered with CTRL+SHIFT+ENTER)
=INDEX($A$1:$A$100,SMALL(IF($A$1:$A$100<>"",ROW($A$1:$A$100)),ROW(1:1))
)
and copy this down

If you want only numbers try
=INDEX($A$1:$A$100,SMALL(IF(ISNUMBER($A$1:$A$100),ROW($A$1:$A$100)),ROW
(1:1)))
 
Let A2:A8 house the data of interest including a label like...

{"Data";1;"";3;"x";"";"z"}

meaning A2 = Data, A3 =1, A4 is empty, etc.

In B2 enter: 0 [ this 0 is required ]
In B3 enter & copy down:

=IF(A3<>"",LOOKUP(9.99999999999999E+307,$B$2:B2)+1,"")

In C1 enter:

=LOOKUP(9.99999999999999E+307,B:B)

In C2 enter: NoBlanks Data [ which is just a label ]

In C3 enter & copy down:

=IF(ROW()-ROW(C$3)+1<=$C$1,INDEX(A:A,MATCH(ROW()-ROW(C$3)+1,B:B)),"")

Column C will now house the desired result.
 
Hi Frank,

Your array formula works like a charm. Can the formula be modified so that
it returns the data in ascending or decending order.

Thanks
Andy
HK, China


Frank Kabel said:
Hi
you may try the following array formula (entered with CTRL+SHIFT+ENTER)
=INDEX($A$1:$A$100,SMALL(IF($A$1:$A$100<>"",ROW($A$1:$A$100)),ROW(1:1))
)
and copy this down

If you want only numbers try
=INDEX($A$1:$A$100,SMALL(IF(ISNUMBER($A$1:$A$100),ROW($A$1:$A$100)),ROW
(1:1)))
Click to expand...
 
Back
Top