Formula wanted

  • Thread starter Thread starter George
  • Start date Start date
G

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.
 
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))))
 
Thank you but didn’t work. But I get some ideas from this and I am close to
a solution.
 
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.)
 
Back
Top