Formula wanted

[George]

Hi.
I have two columns in Excel. In first column A1:A100 i have some integers
and empty cells and in second B1:B100 I have some stings and also some empty
cells. I would like to apply a formula in C1:C100 somehow in a way:

a) to include all stings Bi that the corresponding Ai are numbers
b) with non blank cells in C1:C100 (That means all the strings in C column
will be in successive order. For example C1=ABC, C2=GEORGE, C3=CCD e.g.)

Any idea?

Thank you.

 

[Bob Phillips]

Try this array formula

=IF(ISERROR(SMALL(IF($A$1:$A$20<>"",ROW($A$1:$A$20)),ROW(A1))),"",
INDEX(B:B,SMALL(IF($A$1:$A$20<>"",ROW($A$1:$A$20)),ROW(A1))))

 

[George]

Thank you but didn’t work. But I get some ideas from this and I am close to
a solution.

 

[Bob Phillips]

Did you array enter it?

It worked in my tests, so in what way did it not work?

 

[George]

Yes I did. It appears only the A1 cell data across the array A1:A20.
(If I replace the form ROW(A1) with ROW(A1:A20) then all the Bi data are
extracted but appears the 0 value in the Bi cells where the corresponding Ai
cells are empty.)

 

[Herbert Seidenberg]

Excel 2007, Tables
Advanced Filter
Filter blank/empty cells and sort.
With macro.
http://www.mediafire.com/file/izlz1mweglm/03_30_10.xlsm
Pdf preview:
http://www.mediafire.com/file/nwtzdojlmnj/03_30_10a.pdf