Get row values into column
 

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

Get row values into column
 

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Jambruins" wrote in message
...
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

Get row values into column
 

thanks a lot Max, it works perfectly.

"Max" wrote:

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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Jambruins" wrote in message
...
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

Get row values into column
 

You're welcome !
Thanks for the feedback
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Jambruins" wrote in message
...
thanks a lot Max, it works perfectly.