Hi guys,

I have a table of about 30 columns. In each row, only one cell of the 30 has
text in it. What I would like to do is add a column (say AE) and have a
formula that looks through column AA1:AD1, finds the cell with text and
returns it in AE1.

I thought it might be something simple, but I have gottne myself turned
around and before I craft some frankenstein spreadsheet, I thought I would
ask for help.

Thanks,
P

Using your example range (which is no where near 30 columns wide), you can
use this array-entered formula AE1...

=IF(COUNTA(AA1:AD1)=0,"",INDEX(AA1:AD1,1,MATCH(1,--(AA1:AD1<""),)))

**Commit formula using Ctrl+Shift+Enter and not just Enter by itself

--
Rick (MVP - Excel)


"PGiessler" wrote in message
...
Hi guys,

I have a table of about 30 columns. In each row, only one cell of the 30
has
text in it. What I would like to do is add a column (say AE) and have a
formula that looks through column AA1:AD1, finds the cell with text and
returns it in AE1.

I thought it might be something simple, but I have gottne myself turned
around and before I craft some frankenstein spreadsheet, I thought I would
ask for help.

Thanks,
P

I have a table of about 30 columns. In each row, only one cell of the
30 has text in it. What I would like to do is add a column (say AE)
and have a formula that looks through column AA1:AD1, finds the cell
with text and returns it in AE1.

Maybe this will help:
=LOOKUP(2,1/(AA1:AD1<""),AA1:AD1)

I'm not clear about rows vs. columns in the problem statement, so modify
"AA1:AD1" in the formula as needed.

I like to work closer to home (cell A1) when testing.
So I used cells A1:K1. Please modify as needed
In A1:K1 I entered the values 1 thru 11; then I deleted B1 and changed E1
to the word "donkey"
So had: 1, <blank, 3, 4, donkey, 6, 7, 8, 9, 10, 11

In L1 I entered the formula
=INDEX(A1:K1,SUMPRODUCT(ISTEXT(A1:K1)*COLUMN(A1:K1 )))
and it returned the value "donkey"

The formula returns a #VALUE! error if there is no text in the range and a
totally wrong answer if there are two or more cells with text. This is a fix
for that
=IF(SUMPRODUCT(--ISTEXT(A1:K1)),INDEX(A1:K1,SUMPRODUCT(ISTEXT(A1:K1 )*COLUMN(A1:K1))),
"None")


Either formula can cope with multiple blank cells. Cells which return
numeric values are also OK

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"PGiessler" wrote in message
...
Hi guys,

I have a table of about 30 columns. In each row, only one cell of the 30
has
text in it. What I would like to do is add a column (say AE) and have a
formula that looks through column AA1:AD1, finds the cell with text and
returns it in AE1.

I thought it might be something simple, but I have gottne myself turned
around and before I craft some frankenstein spreadsheet, I thought I would
ask for help.

Thanks,
P

Assuming you made a typo and intended your range to be A1:AD1 (30 cells),
try this:

=INDEX(A1:AD1,MATCH("*",A1:AD1,0))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"PGiessler" wrote in message
...
Hi guys,

I have a table of about 30 columns. In each row, only one cell of the 30
has
text in it. What I would like to do is add a column (say AE) and have a
formula that looks through column AA1:AD1, finds the cell with text and
returns it in AE1.

I thought it might be something simple, but I have gottne myself turned
around and before I craft some frankenstein spreadsheet, I thought I would
ask for help.

Thanks,
P

BTW -

The formula returns the *first* text entry -
*BUT*, you must remember that a null (zero length string, "" ) is considered
as TEXT by XL.
Therefore, if you have formulas in your range that you have configured to
return nulls in case of absent values, this formula wouldn't work unless you
replaced your null returns with perhaps zeroes.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"RagDyer" wrote in message
...
Assuming you made a typo and intended your range to be A1:AD1 (30 cells),
try this:

=INDEX(A1:AD1,MATCH("*",A1:AD1,0))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"PGiessler" wrote in message
...
Hi guys,

I have a table of about 30 columns. In each row, only one cell of the 30
has
text in it. What I would like to do is add a column (say AE) and have a
formula that looks through column AA1:AD1, finds the cell with text and
returns it in AE1.

I thought it might be something simple, but I have gottne myself turned
around and before I craft some frankenstein spreadsheet, I thought I
would
ask for help.

Thanks,
P

Hi try

=HLOOKUP(REPT("Z",255),A1:AD1,1)

this will return the text in the range A1: AD1

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"PGiessler" wrote:

Hi guys,

I have a table of about 30 columns. In each row, only one cell of the 30 has
text in it. What I would like to do is add a column (say AE) and have a
formula that looks through column AA1:AD1, finds the cell with text and
returns it in AE1.

I thought it might be something simple, but I have gottne myself turned
around and before I craft some frankenstein spreadsheet, I thought I would
ask for help.

Thanks,
P

This works very well and seems to be a pretty straightforward approach. Thanks!

"MyVeryOwnSelf" wrote:

I have a table of about 30 columns. In each row, only one cell of the
30 has text in it. What I would like to do is add a column (say AE)
and have a formula that looks through column AA1:AD1, finds the cell
with text and returns it in AE1.

Maybe this will help:
=LOOKUP(2,1/(AA1:AD1<""),AA1:AD1)

I'm not clear about rows vs. columns in the problem statement, so modify
"AA1:AD1" in the formula as needed.

I had a little trouble with the second formula, but the first one did the
trick. Thanks for your help!

"Bernard Liengme" wrote:

I like to work closer to home (cell A1) when testing.
So I used cells A1:K1. Please modify as needed
In A1:K1 I entered the values 1 thru 11; then I deleted B1 and changed E1
to the word "donkey"
So had: 1, <blank, 3, 4, donkey, 6, 7, 8, 9, 10, 11

In L1 I entered the formula
=INDEX(A1:K1,SUMPRODUCT(ISTEXT(A1:K1)*COLUMN(A1:K1 )))
and it returned the value "donkey"

The formula returns a #VALUE! error if there is no text in the range and a
totally wrong answer if there are two or more cells with text. This is a fix
for that
=IF(SUMPRODUCT(--ISTEXT(A1:K1)),INDEX(A1:K1,SUMPRODUCT(ISTEXT(A1:K1 )*COLUMN(A1:K1))),
"None")


Either formula can cope with multiple blank cells. Cells which return
numeric values are also OK

best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"PGiessler" wrote in message
...
Hi guys,

I have a table of about 30 columns. In each row, only one cell of the 30
has
text in it. What I would like to do is add a column (say AE) and have a
formula that looks through column AA1:AD1, finds the cell with text and
returns it in AE1.

I thought it might be something simple, but I have gottne myself turned
around and before I craft some frankenstein spreadsheet, I thought I would
ask for help.

Thanks,
P

Thanks ... this works perfectly for what I need! Much appreciated!

"RagDyer" wrote:

Assuming you made a typo and intended your range to be A1:AD1 (30 cells),
try this:

=INDEX(A1:AD1,MATCH("*",A1:AD1,0))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"PGiessler" wrote in message
...
Hi guys,

I have a table of about 30 columns. In each row, only one cell of the 30
has
text in it. What I would like to do is add a column (say AE) and have a
formula that looks through column AA1:AD1, finds the cell with text and
returns it in AE1.

I thought it might be something simple, but I have gottne myself turned
around and before I craft some frankenstein spreadsheet, I thought I would
ask for help.

Thanks,
P

Thanks for the formula ... it does exactly what I need it to do. I
appreceiate your help.

"Francis" wrote:

Hi try

=HLOOKUP(REPT("Z",255),A1:AD1,1)

this will return the text in the range A1: AD1

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"PGiessler" wrote:

Hi guys,

I have a table of about 30 columns. In each row, only one cell of the 30 has
text in it. What I would like to do is add a column (say AE) and have a
formula that looks through column AA1:AD1, finds the cell with text and
returns it in AE1.

I thought it might be something simple, but I have gottne myself turned
around and before I craft some frankenstein spreadsheet, I thought I would
ask for help.

Thanks,
P

You're welcome, and thanks for the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"PGiessler" wrote in message
...
Thanks ... this works perfectly for what I need! Much appreciated!

"RagDyer" wrote:

Assuming you made a typo and intended your range to be A1:AD1 (30 cells),
try this:

=INDEX(A1:AD1,MATCH("*",A1:AD1,0))
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

"PGiessler" wrote in message
...
Hi guys,

I have a table of about 30 columns. In each row, only one cell of the
30
has
text in it. What I would like to do is add a column (say AE) and have a
formula that looks through column AA1:AD1, finds the cell with text and
returns it in AE1.

I thought it might be something simple, but I have gottne myself turned
around and before I craft some frankenstein spreadsheet, I thought I
would
ask for help.

Thanks,
P