Get row values into column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a row (A:BI) with text or numbers in each cell. However, there are
some cells that are blank. B,D,F,H,J,... are the number cells (unless they
are blank) and A,C,E,G,I,K,... are the text cells (unless they are blank).

I would like to have all the cells that have text in them to be entered into
cell A5 and down. Same thing with the number cells except in cell B5 down.

Example:
A B C D E F
1 PHI 2.3 OTT 3.6

I would like the following:
A5 = PHI B5 = 2.3
A6= OTT B6 = 3.6

Anyone know how to do this? Thanks
 
One play, using non-array formulas

Using 2 empty rows, say rows 2 & 3
Put in A2: =IF(A1="","",IF(ISTEXT(A1),COLUMN(),""))
Put in A3: =IF(A1="","",IF(ISNUMBER(A1),COLUMN(),""))
Select A2:A3, copy across as many cols as required,
say to IV3 (the max extent)

Then

Put in A5:
=IF(ISERROR(SMALL($2:$2,ROW(A1))),"",
INDEX($1:$1,MATCH(SMALL($2:$2,ROW(A1)),$2:$2,0)))

Put in B5:
=IF(ISERROR(SMALL($3:$3,ROW(A1))),"",
INDEX($1:$1,MATCH(SMALL($3:$3,ROW(A1)),$3:$3,0)))

Select A5:B5, and copy down 256 rows to B260

A5:B260 will return the desired results,
all neatly bunched at the top
 
thanks a lot Max, it works perfectly.

Max said:
One play, using non-array formulas

Using 2 empty rows, say rows 2 & 3
Put in A2: =IF(A1="","",IF(ISTEXT(A1),COLUMN(),""))
Put in A3: =IF(A1="","",IF(ISNUMBER(A1),COLUMN(),""))
Select A2:A3, copy across as many cols as required,
say to IV3 (the max extent)

Then

Put in A5:
=IF(ISERROR(SMALL($2:$2,ROW(A1))),"",
INDEX($1:$1,MATCH(SMALL($2:$2,ROW(A1)),$2:$2,0)))

Put in B5:
=IF(ISERROR(SMALL($3:$3,ROW(A1))),"",
INDEX($1:$1,MATCH(SMALL($3:$3,ROW(A1)),$3:$3,0)))

Select A5:B5, and copy down 256 rows to B260

A5:B260 will return the desired results,
all neatly bunched at the top
Click to expand...
 
Back
Top