Find the first & subsequent cells to contain text in a column & re

T. Valko,
Thanks. Sorry for the wrong Subject above.
I did exactly as per the your instruction but if I copy the cells down
appear blank except F1. Is there something to do more?
Cn you please see it again?

With regards
narnimar,

"T. Valko" wrote:

Try this...

You're subject line says find TEXT but your data is numeric assuming "blank"
means empty/blank cells.

B11 = column headers Valx
B27 data you want to extract

H1 = Valx

Create this defined range name...
Goto the menu InsertNameDefine
Name: Table
Refers to: =$B$2:$D$7

Enter this formula in I1. It will return the count of records.

=COUNTA(INDEX(Table,,MATCH(H1,B11,0)))

Enter this array formula** in F1:

=IF(ROWS(F$1:F1)=I$1,INDEX(Table,SMALL(IF(INDEX(T able,,MATCH(H$1,B$1$1,0))"",ROW(Table)),ROWS(F $1:F1))-MIN(ROW(Table))+1,MATCH(H$1,B$1$1,0)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy down to a number of rows that is at least equal to the maximum count of
entries in any of your Val columns. For example, the Val column with the
maximum number of entries is Val3 with 5 entries. So, you'd have to copy the
formula down to at least 5 rows.

--
Biff
Microsoft Excel MVP


"Narnimar" wrote in message
...
Hi Luke, I really amazed today when I look at here your solution wondering
about my unresolved issue which is closer to it. I dont know if it is
right
on my part to put the question in between this discusion.
The data base is like this example:-
A B C D E F H
Item Val1 Val2 Val3 Bus 2 Val2
Car 2 blank 5 Cycle 35
Bus 1 2 1 Horse 1
Train blank blank 3
Flight blank blank 1
Cycle 20 35 Blank
Horse Blank 1 2

if I type Val2 in H1 the formula in column F should return a nonblank list
and its corresponding row value from column from column A as shown above.
The
formula in F should be able to guess and return nonblank values from
columns
B or C or D based on the value in H1 which would be header name of these
columns! This is possible? Any question please revert back. Please help
me.


"Luke M" wrote:

Hmm. One more attempt he
=IF(F87="","",IF(IF(AND(LEN(INDEX($F$1:$F$57,F87)) =4,COUNTIF($F$87:F87,F87)2),F87,MIN(IF(ISTEXT(OF FSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE)))=0,"",IF(AND(LEN(INDEX($F$1:$F$57,F8 7))=4,COUNTIF($F$87:F87,F87)2),F87,MIN(IF(ISTEXT (OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE)))))

Again, an array formula. I tried to setup my sheet as you described, data
is
in rows 37-57. Ron's VBA solution is probably more elegant, but at least
now
if you want a formula answer, you have one.

I must admit, this was an interesting challenge.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"M" wrote:

Luke, this is very close to what I want but there are a couple of
errors.
Unfortunately after a lot of head scratching I can't alter it myself.

1)
When I replace all the references to the correct ones the formula
returns a
"#REF!" error. I assume it is something to do with the offset but can't
work
it out.

Ref A1:A15 should be F37:F57.
This formula is entered into column M. My formula to find the first
entry is
in row 87 and this formula starts in row 88 onwards

2)
If there are more then 4 characters in the last entry it does not
repeat
(The repeat works fine as long as the last entry is less than 4
characters. I
am not sure if this can be solved or if I need to add a dummy entry
that is
less than 4 characters??

Thanks again for your help. If there is a solution to the first point I
can
start using the formula. Great!


"T. Valko" wrote:

That doesn't repeat the 11.


--
Biff
Microsoft Excel MVP


"Luke M" wrote:

Hi M!

Use Biff's formula in C1 (or mine, if you prefer), and then in C2:
=IF(OR(C1="",C1=MAX(IF(ISTEXT($A$1:$A$12),ROW($A$1 :$A$12),FALSE))),"",IF(AND(LEN(INDEX($A$1:$A$12,C1 ))=4,COUNTIF($C$1:C1,C1)2),C1,MIN(IF(ISTEXT(OFFS ET($A$1,C1,0,ROWS($A$1:$A$12)-C1)),ROW(OFFSET($A$1,C1,0,ROWS($A$1:$A$12)-C1)),FALSE))))

Again, this is an array formula. Not a very elegant formula but it
works.
Note that there are several callouts to $a$1:$a$12 which you will
need to
adjust as necessary.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"M" wrote:

Thanks for your help however is there a solution to include
repeating the row
number if there is more than 4 characters??

I have just tried Biff's reply. I enter the formula as an array
in 1 cell
and then copy it down. This correctly lists the row numbers but
does not
repeat (see the last bit of my first post)

Thanks again.


"T. Valko" wrote:

You're welcome!

--
Biff
Microsoft Excel MVP


"Mike H" wrote:

Ah i see, I was happy to produce NUM errors with my formula
when the matches
ran out but see this is tidier. Thank you.

Mike

"T. Valko" wrote:

=IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"),

That's an error trap.

It's more efficient than:

=IF(ISERROR(SMALL(IF(ISTEXT(A$1:A$12),ROW(A$1:A$12 )),ROWS(C$1:C1))),

COUNTIF(A$1:A$12,"*")

Returns the total number of *text* entries in the range and
compares that
number to number of rows the formula is copied to:

=IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"),

If the number of rows the formula is copied to is greater
than the total
number of text entries in the range then the formula
returns a blank ("").
Otherwise, SMALL(...,n) would return a #NUM! error.

--
Biff
Microsoft Excel MVP


"Mike H" wrote:

Biff,

Just curious but what are you testing for in the first
part of your formula,

=IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"),

why not just

=SMALL(IF(ISTEXT(A$1:A$12),ROW(A$1:A$12)),ROWS(C$1 :C1))

what am I missing?

Mike

"T. Valko" wrote:

Assume you want the results starting in cell C1.

Try this array formula** entered in C1 and copied down
until you get blanks:

=IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"),SMALL(IF(I STEXT(A$1:A$12),ROW(A$1:A$12)),ROWS(C$1:C1)),"")

This assumes your *REAL* data range does in fact start
on row 1. If it
doesn't start on row 1 then we'll need to make a slight
adjustment.

** array formulas need to be entered using the key
combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the
CTRL key and the SHIFT
key then hit ENTER.

As for your "complication", I don't think that can be
done with a single
formula. If it can I'd love to see it!

--
Biff
Microsoft Excel MVP


"M" wrote:

I have a column that contains empty cells and text. I
want to return the row
reference of the cells that contain text. For
example.

A2 = empty cell
A3 = empty cell
A4 = 2L
A5 = empty cell
A6 = 2W
A7 = empty cell
A8 = 2L2W
A9 = empty cell
A10 = empty cell
A11 = 2L2W
A12 = empty cell

The formula would return 4. Thanks to Luke I have.

{=MIN(IF(ISTEXT(A1:A12),ROW(A1:A12),FALSE))}

However I need to copy this formula down a column.
The formula should return
4 then 6 then 8 then 11 etc. The blank cells are
randomly placed. I can't
sort the data as I have other equations in the sheet.

To add to this problem I need to repeat the row
number if the original cell
contains 4 or more characters. So the formula should
return 4 then 6 then 8
then 8 then 11 then 11 etc.

Thanks for your help.

Im going to take a break for a few hours. When I return I'll put together a
sample file that does what you want.

--
Biff
Microsoft Excel MVP


"Narnimar" wrote in message
news
T. Valko,
Thanks. Sorry for the wrong Subject above.
I did exactly as per the your instruction but if I copy the cells down
appear blank except F1. Is there something to do more?
Cn you please see it again?

With regards
narnimar,

"T. Valko" wrote:

Try this...

You're subject line says find TEXT but your data is numeric assuming
"blank"
means empty/blank cells.

B11 = column headers Valx
B27 data you want to extract

H1 = Valx

Create this defined range name...
Goto the menu InsertNameDefine
Name: Table
Refers to: =$B$2:$D$7

Enter this formula in I1. It will return the count of records.

=COUNTA(INDEX(Table,,MATCH(H1,B11,0)))

Enter this array formula** in F1:

=IF(ROWS(F$1:F1)=I$1,INDEX(Table,SMALL(IF(INDEX(T able,,MATCH(H$1,B$1$1,0))"",ROW(Table)),ROWS(F $1:F1))-MIN(ROW(Table))+1,MATCH(H$1,B$1$1,0)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

Copy down to a number of rows that is at least equal to the maximum count
of
entries in any of your Val columns. For example, the Val column with the
maximum number of entries is Val3 with 5 entries. So, you'd have to copy
the
formula down to at least 5 rows.

--
Biff
Microsoft Excel MVP


"Narnimar" wrote in message
...
Hi Luke, I really amazed today when I look at here your solution
wondering
about my unresolved issue which is closer to it. I dont know if it is
right
on my part to put the question in between this discusion.
The data base is like this example:-
A B C D E F
H
Item Val1 Val2 Val3 Bus 2 Val2
Car 2 blank 5 Cycle 35
Bus 1 2 1 Horse 1
Train blank blank 3
Flight blank blank 1
Cycle 20 35 Blank
Horse Blank 1 2

if I type Val2 in H1 the formula in column F should return a nonblank
list
and its corresponding row value from column from column A as shown
above.
The
formula in F should be able to guess and return nonblank values from
columns
B or C or D based on the value in H1 which would be header name of
these
columns! This is possible? Any question please revert back. Please help
me.


"Luke M" wrote:

Hmm. One more attempt he
=IF(F87="","",IF(IF(AND(LEN(INDEX($F$1:$F$57,F87)) =4,COUNTIF($F$87:F87,F87)2),F87,MIN(IF(ISTEXT(OF FSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE)))=0,"",IF(AND(LEN(INDEX($F$1:$F$57,F8 7))=4,COUNTIF($F$87:F87,F87)2),F87,MIN(IF(ISTEXT (OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE)))))

Again, an array formula. I tried to setup my sheet as you described,
data
is
in rows 37-57. Ron's VBA solution is probably more elegant, but at
least
now
if you want a formula answer, you have one.

I must admit, this was an interesting challenge.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"M" wrote:

Luke, this is very close to what I want but there are a couple of
errors.
Unfortunately after a lot of head scratching I can't alter it
myself.

1)
When I replace all the references to the correct ones the formula
returns a
"#REF!" error. I assume it is something to do with the offset but
can't
work
it out.

Ref A1:A15 should be F37:F57.
This formula is entered into column M. My formula to find the first
entry is
in row 87 and this formula starts in row 88 onwards

2)
If there are more then 4 characters in the last entry it does not
repeat
(The repeat works fine as long as the last entry is less than 4
characters. I
am not sure if this can be solved or if I need to add a dummy entry
that is
less than 4 characters??

Thanks again for your help. If there is a solution to the first
point I
can
start using the formula. Great!


"T. Valko" wrote:

That doesn't repeat the 11.


--
Biff
Microsoft Excel MVP


"Luke M" wrote:

Hi M!

Use Biff's formula in C1 (or mine, if you prefer), and then in
C2:
=IF(OR(C1="",C1=MAX(IF(ISTEXT($A$1:$A$12),ROW($A$1 :$A$12),FALSE))),"",IF(AND(LEN(INDEX($A$1:$A$12,C1 ))=4,COUNTIF($C$1:C1,C1)2),C1,MIN(IF(ISTEXT(OFFS ET($A$1,C1,0,ROWS($A$1:$A$12)-C1)),ROW(OFFSET($A$1,C1,0,ROWS($A$1:$A$12)-C1)),FALSE))))

Again, this is an array formula. Not a very elegant formula but
it
works.
Note that there are several callouts to $a$1:$a$12 which you
will
need to
adjust as necessary.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"M" wrote:

Thanks for your help however is there a solution to include
repeating the row
number if there is more than 4 characters??

I have just tried Biff's reply. I enter the formula as an
array
in 1 cell
and then copy it down. This correctly lists the row numbers
but
does not
repeat (see the last bit of my first post)

Thanks again.


"T. Valko" wrote:

You're welcome!

--
Biff
Microsoft Excel MVP


"Mike H" wrote:

Ah i see, I was happy to produce NUM errors with my
formula
when the matches
ran out but see this is tidier. Thank you.

Mike

"T. Valko" wrote:

=IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"),

That's an error trap.

It's more efficient than:

=IF(ISERROR(SMALL(IF(ISTEXT(A$1:A$12),ROW(A$1:A$12 )),ROWS(C$1:C1))),

COUNTIF(A$1:A$12,"*")

Returns the total number of *text* entries in the range
and
compares that
number to number of rows the formula is copied to:

=IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"),

If the number of rows the formula is copied to is
greater
than the total
number of text entries in the range then the formula
returns a blank ("").
Otherwise, SMALL(...,n) would return a #NUM! error.

--
Biff
Microsoft Excel MVP


"Mike H" wrote:

Biff,

Just curious but what are you testing for in the first
part of your formula,

=IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"),

why not just

=SMALL(IF(ISTEXT(A$1:A$12),ROW(A$1:A$12)),ROWS(C$1 :C1))

what am I missing?

Mike

"T. Valko" wrote:

Assume you want the results starting in cell C1.

Try this array formula** entered in C1 and copied
down
until you get blanks:

=IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"),SMALL(IF(I STEXT(A$1:A$12),ROW(A$1:A$12)),ROWS(C$1:C1)),"")

This assumes your *REAL* data range does in fact
start
on row 1. If it
doesn't start on row 1 then we'll need to make a
slight
adjustment.

** array formulas need to be entered using the key
combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both
the
CTRL key and the SHIFT
key then hit ENTER.

As for your "complication", I don't think that can
be
done with a single
formula. If it can I'd love to see it!

--
Biff
Microsoft Excel MVP


"M" wrote:

I have a column that contains empty cells and
text. I
want to return the row
reference of the cells that contain text. For
example.

A2 = empty cell
A3 = empty cell
A4 = 2L
A5 = empty cell
A6 = 2W
A7 = empty cell
A8 = 2L2W
A9 = empty cell
A10 = empty cell
A11 = 2L2W
A12 = empty cell

The formula would return 4. Thanks to Luke I have.

{=MIN(IF(ISTEXT(A1:A12),ROW(A1:A12),FALSE))}

However I need to copy this formula down a column.
The formula should return
4 then 6 then 8 then 11 etc. The blank cells are
randomly placed. I can't
sort the data as I have other equations in the
sheet.

To add to this problem I need to repeat the row
number if the original cell
contains 4 or more characters. So the formula
should
return 4 then 6 then 8
then 8 then 11 then 11 etc.

Thanks for your help.

Here's a small sample file that demonstrates this.

xNarnimar.xls 14kb

http://cjoint.com/?hAdCaKigG6

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Im going to take a break for a few hours. When I return I'll put together
a sample file that does what you want.

--
Biff
Microsoft Excel MVP


"Narnimar" wrote in message
news
T. Valko,
Thanks. Sorry for the wrong Subject above.
I did exactly as per the your instruction but if I copy the cells down
appear blank except F1. Is there something to do more?
Cn you please see it again?

With regards
narnimar,

"T. Valko" wrote:

Try this...

You're subject line says find TEXT but your data is numeric assuming
"blank"
means empty/blank cells.

B11 = column headers Valx
B27 data you want to extract

H1 = Valx

Create this defined range name...
Goto the menu InsertNameDefine
Name: Table
Refers to: =$B$2:$D$7

Enter this formula in I1. It will return the count of records.

=COUNTA(INDEX(Table,,MATCH(H1,B11,0)))

Enter this array formula** in F1:

=IF(ROWS(F$1:F1)=I$1,INDEX(Table,SMALL(IF(INDEX(T able,,MATCH(H$1,B$1$1,0))"",ROW(Table)),ROWS(F $1:F1))-MIN(ROW(Table))+1,MATCH(H$1,B$1$1,0)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

Copy down to a number of rows that is at least equal to the maximum
count of
entries in any of your Val columns. For example, the Val column with the
maximum number of entries is Val3 with 5 entries. So, you'd have to copy
the
formula down to at least 5 rows.

--
Biff
Microsoft Excel MVP


"Narnimar" wrote in message
...
Hi Luke, I really amazed today when I look at here your solution
wondering
about my unresolved issue which is closer to it. I dont know if it is
right
on my part to put the question in between this discusion.
The data base is like this example:-
A B C D E F H
Item Val1 Val2 Val3 Bus 2 Val2
Car 2 blank 5 Cycle 35
Bus 1 2 1 Horse 1
Train blank blank 3
Flight blank blank 1
Cycle 20 35 Blank
Horse Blank 1 2

if I type Val2 in H1 the formula in column F should return a nonblank
list
and its corresponding row value from column from column A as shown
above.
The
formula in F should be able to guess and return nonblank values from
columns
B or C or D based on the value in H1 which would be header name of
these
columns! This is possible? Any question please revert back. Please
help
me.


"Luke M" wrote:

Hmm. One more attempt he
=IF(F87="","",IF(IF(AND(LEN(INDEX($F$1:$F$57,F87)) =4,COUNTIF($F$87:F87,F87)2),F87,MIN(IF(ISTEXT(OF FSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE)))=0,"",IF(AND(LEN(INDEX($F$1:$F$57,F8 7))=4,COUNTIF($F$87:F87,F87)2),F87,MIN(IF(ISTEXT (OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE)))))

Again, an array formula. I tried to setup my sheet as you described,
data
is
in rows 37-57. Ron's VBA solution is probably more elegant, but at
least
now
if you want a formula answer, you have one.

I must admit, this was an interesting challenge.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"M" wrote:

Luke, this is very close to what I want but there are a couple of
errors.
Unfortunately after a lot of head scratching I can't alter it
myself.

1)
When I replace all the references to the correct ones the formula
returns a
"#REF!" error. I assume it is something to do with the offset but
can't
work
it out.

Ref A1:A15 should be F37:F57.
This formula is entered into column M. My formula to find the first
entry is
in row 87 and this formula starts in row 88 onwards

2)
If there are more then 4 characters in the last entry it does not
repeat
(The repeat works fine as long as the last entry is less than 4
characters. I
am not sure if this can be solved or if I need to add a dummy entry
that is
less than 4 characters??

Thanks again for your help. If there is a solution to the first
point I
can
start using the formula. Great!


"T. Valko" wrote:

That doesn't repeat the 11.


--
Biff
Microsoft Excel MVP


"Luke M" wrote:

Hi M!

Use Biff's formula in C1 (or mine, if you prefer), and then in
C2:
=IF(OR(C1="",C1=MAX(IF(ISTEXT($A$1:$A$12),ROW($A$1 :$A$12),FALSE))),"",IF(AND(LEN(INDEX($A$1:$A$12,C1 ))=4,COUNTIF($C$1:C1,C1)2),C1,MIN(IF(ISTEXT(OFFS ET($A$1,C1,0,ROWS($A$1:$A$12)-C1)),ROW(OFFSET($A$1,C1,0,ROWS($A$1:$A$12)-C1)),FALSE))))

Again, this is an array formula. Not a very elegant formula but
it
works.
Note that there are several callouts to $a$1:$a$12 which you
will
need to
adjust as necessary.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"M" wrote:

Thanks for your help however is there a solution to include
repeating the row
number if there is more than 4 characters??

I have just tried Biff's reply. I enter the formula as an
array
in 1 cell
and then copy it down. This correctly lists the row numbers
but
does not
repeat (see the last bit of my first post)

Thanks again.


"T. Valko" wrote:

You're welcome!

--
Biff
Microsoft Excel MVP


"Mike H" wrote:

Ah i see, I was happy to produce NUM errors with my
formula
when the matches
ran out but see this is tidier. Thank you.

Mike

"T. Valko" wrote:

=IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"),

That's an error trap.

It's more efficient than:

=IF(ISERROR(SMALL(IF(ISTEXT(A$1:A$12),ROW(A$1:A$12 )),ROWS(C$1:C1))),

COUNTIF(A$1:A$12,"*")

Returns the total number of *text* entries in the range
and
compares that
number to number of rows the formula is copied to:

=IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"),

If the number of rows the formula is copied to is
greater
than the total
number of text entries in the range then the formula
returns a blank ("").
Otherwise, SMALL(...,n) would return a #NUM! error.

--
Biff
Microsoft Excel MVP


"Mike H" wrote:

Biff,

Just curious but what are you testing for in the
first
part of your formula,

=IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"),

why not just

=SMALL(IF(ISTEXT(A$1:A$12),ROW(A$1:A$12)),ROWS(C$1 :C1))

what am I missing?

Mike

"T. Valko" wrote:

Assume you want the results starting in cell C1.

Try this array formula** entered in C1 and copied
down
until you get blanks:

=IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"),SMALL(IF(I STEXT(A$1:A$12),ROW(A$1:A$12)),ROWS(C$1:C1)),"")

This assumes your *REAL* data range does in fact
start
on row 1. If it
doesn't start on row 1 then we'll need to make a
slight
adjustment.

** array formulas need to be entered using the key
combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both
the
CTRL key and the SHIFT
key then hit ENTER.

As for your "complication", I don't think that can
be
done with a single
formula. If it can I'd love to see it!

--
Biff
Microsoft Excel MVP


"M" wrote:

I have a column that contains empty cells and
text. I
want to return the row
reference of the cells that contain text. For
example.

A2 = empty cell
A3 = empty cell
A4 = 2L
A5 = empty cell
A6 = 2W
A7 = empty cell
A8 = 2L2W
A9 = empty cell
A10 = empty cell
A11 = 2L2W
A12 = empty cell

The formula would return 4. Thanks to Luke I
have.

{=MIN(IF(ISTEXT(A1:A12),ROW(A1:A12),FALSE))}

However I need to copy this formula down a
column.
The formula should return
4 then 6 then 8 then 11 etc. The blank cells are
randomly placed. I can't
sort the data as I have other equations in the
sheet.

To add to this problem I need to repeat the row
number if the original cell
contains 4 or more characters. So the formula
should
return 4 then 6 then 8
then 8 then 11 then 11 etc.

Thanks for your help.

I reaized my mistake while copying webpage in the table. Excel is working
great now. Thank you so much T. Valko.

"T. Valko" wrote:

Here's a small sample file that demonstrates this.

xNarnimar.xls 14kb

http://cjoint.com/?hAdCaKigG6

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Im going to take a break for a few hours. When I return I'll put together
a sample file that does what you want.

--
Biff
Microsoft Excel MVP


"Narnimar" wrote in message
news
T. Valko,
Thanks. Sorry for the wrong Subject above.
I did exactly as per the your instruction but if I copy the cells down
appear blank except F1. Is there something to do more?
Cn you please see it again?

With regards
narnimar,

"T. Valko" wrote:

Try this...

You're subject line says find TEXT but your data is numeric assuming
"blank"
means empty/blank cells.

B11 = column headers Valx
B27 data you want to extract

H1 = Valx

Create this defined range name...
Goto the menu InsertNameDefine
Name: Table
Refers to: =$B$2:$D$7

Enter this formula in I1. It will return the count of records.

=COUNTA(INDEX(Table,,MATCH(H1,B11,0)))

Enter this array formula** in F1:

=IF(ROWS(F$1:F1)=I$1,INDEX(Table,SMALL(IF(INDEX(T able,,MATCH(H$1,B$1$1,0))"",ROW(Table)),ROWS(F $1:F1))-MIN(ROW(Table))+1,MATCH(H$1,B$1$1,0)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

Copy down to a number of rows that is at least equal to the maximum
count of
entries in any of your Val columns. For example, the Val column with the
maximum number of entries is Val3 with 5 entries. So, you'd have to copy
the
formula down to at least 5 rows.

--
Biff
Microsoft Excel MVP


"Narnimar" wrote in message
...
Hi Luke, I really amazed today when I look at here your solution
wondering
about my unresolved issue which is closer to it. I dont know if it is
right
on my part to put the question in between this discusion.
The data base is like this example:-
A B C D E F H
Item Val1 Val2 Val3 Bus 2 Val2
Car 2 blank 5 Cycle 35
Bus 1 2 1 Horse 1
Train blank blank 3
Flight blank blank 1
Cycle 20 35 Blank
Horse Blank 1 2

if I type Val2 in H1 the formula in column F should return a nonblank
list
and its corresponding row value from column from column A as shown
above.
The
formula in F should be able to guess and return nonblank values from
columns
B or C or D based on the value in H1 which would be header name of
these
columns! This is possible? Any question please revert back. Please
help
me.


"Luke M" wrote:

Hmm. One more attempt he
=IF(F87="","",IF(IF(AND(LEN(INDEX($F$1:$F$57,F87)) =4,COUNTIF($F$87:F87,F87)2),F87,MIN(IF(ISTEXT(OF FSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE)))=0,"",IF(AND(LEN(INDEX($F$1:$F$57,F8 7))=4,COUNTIF($F$87:F87,F87)2),F87,MIN(IF(ISTEXT (OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE)))))

Again, an array formula. I tried to setup my sheet as you described,
data
is
in rows 37-57. Ron's VBA solution is probably more elegant, but at
least
now
if you want a formula answer, you have one.

I must admit, this was an interesting challenge.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"M" wrote:

Luke, this is very close to what I want but there are a couple of
errors.
Unfortunately after a lot of head scratching I can't alter it
myself.

1)
When I replace all the references to the correct ones the formula
returns a
"#REF!" error. I assume it is something to do with the offset but
can't
work
it out.

Ref A1:A15 should be F37:F57.
This formula is entered into column M. My formula to find the first
entry is
in row 87 and this formula starts in row 88 onwards

2)
If there are more then 4 characters in the last entry it does not
repeat
(The repeat works fine as long as the last entry is less than 4
characters. I
am not sure if this can be solved or if I need to add a dummy entry
that is
less than 4 characters??

Thanks again for your help. If there is a solution to the first
point I
can
start using the formula. Great!


"T. Valko" wrote:

That doesn't repeat the 11.


--
Biff
Microsoft Excel MVP


"Luke M" wrote:

Hi M!

Use Biff's formula in C1 (or mine, if you prefer), and then in
C2:
=IF(OR(C1="",C1=MAX(IF(ISTEXT($A$1:$A$12),ROW($A$1 :$A$12),FALSE))),"",IF(AND(LEN(INDEX($A$1:$A$12,C1 ))=4,COUNTIF($C$1:C1,C1)2),C1,MIN(IF(ISTEXT(OFFS ET($A$1,C1,0,ROWS($A$1:$A$12)-C1)),ROW(OFFSET($A$1,C1,0,ROWS($A$1:$A$12)-C1)),FALSE))))

Again, this is an array formula. Not a very elegant formula but
it
works.
Note that there are several callouts to $a$1:$a$12 which you
will
need to
adjust as necessary.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"M" wrote:

Thanks for your help however is there a solution to include
repeating the row
number if there is more than 4 characters??

I have just tried Biff's reply. I enter the formula as an
array
in 1 cell
and then copy it down. This correctly lists the row numbers
but
does not
repeat (see the last bit of my first post)

Thanks again.


"T. Valko" wrote:

You're welcome!

--
Biff
Microsoft Excel MVP


"Mike H" wrote:

Ah i see, I was happy to produce NUM errors with my
formula
when the matches
ran out but see this is tidier. Thank you.

Mike

"T. Valko" wrote:

=IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"),

That's an error trap.

It's more efficient than:

=IF(ISERROR(SMALL(IF(ISTEXT(A$1:A$12),ROW(A$1:A$12 )),ROWS(C$1:C1))),

COUNTIF(A$1:A$12,"*")

Returns the total number of *text* entries in the range
and
compares that
number to number of rows the formula is copied to:

=IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"),

If the number of rows the formula is copied to is
greater
than the total
number of text entries in the range then the formula
returns a blank ("").
Otherwise, SMALL(...,n) would return a #NUM! error.

--
Biff
Microsoft Excel MVP


"Mike H" wrote:

Biff,

Just curious but what are you testing for in the
first
part of your formula,

=IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"),

why not just

=SMALL(IF(ISTEXT(A$1:A$12),ROW(A$1:A$12)),ROWS(C$1 :C1))

what am I missing?

Mike

"T. Valko" wrote:

Assume you want the results starting in cell C1.

Try this array formula** entered in C1 and copied
down
until you get blanks:

=IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"),SMALL(IF(I STEXT(A$1:A$12),ROW(A$1:A$12)),ROWS(C$1:C1)),"")

This assumes your *REAL* data range does in fact
start
on row 1. If it
doesn't start on row 1 then we'll need to make a
slight
adjustment.

** array formulas need to be entered using the key
combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both
the

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Narnimar" wrote in message
...
I reaized my mistake while copying webpage in the table. Excel is working
great now. Thank you so much T. Valko.

"T. Valko" wrote:

Here's a small sample file that demonstrates this.

xNarnimar.xls 14kb

http://cjoint.com/?hAdCaKigG6

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Im going to take a break for a few hours. When I return I'll put
together
a sample file that does what you want.

--
Biff
Microsoft Excel MVP


"Narnimar" wrote in message
news T. Valko,
Thanks. Sorry for the wrong Subject above.
I did exactly as per the your instruction but if I copy the cells down
appear blank except F1. Is there something to do more?
Cn you please see it again?

With regards
narnimar,

"T. Valko" wrote:

Try this...

You're subject line says find TEXT but your data is numeric assuming
"blank"
means empty/blank cells.

B11 = column headers Valx
B27 data you want to extract

H1 = Valx

Create this defined range name...
Goto the menu InsertNameDefine
Name: Table
Refers to: =$B$2:$D$7

Enter this formula in I1. It will return the count of records.

=COUNTA(INDEX(Table,,MATCH(H1,B11,0)))

Enter this array formula** in F1:

=IF(ROWS(F$1:F1)=I$1,INDEX(Table,SMALL(IF(INDEX(T able,,MATCH(H$1,B$1$1,0))"",ROW(Table)),ROWS(F $1:F1))-MIN(ROW(Table))+1,MATCH(H$1,B$1$1,0)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and
the
SHIFT
key then hit ENTER.

Copy down to a number of rows that is at least equal to the maximum
count of
entries in any of your Val columns. For example, the Val column with
the
maximum number of entries is Val3 with 5 entries. So, you'd have to
copy
the
formula down to at least 5 rows.

--
Biff
Microsoft Excel MVP


"Narnimar" wrote in message
...
Hi Luke, I really amazed today when I look at here your solution
wondering
about my unresolved issue which is closer to it. I dont know if it
is
right
on my part to put the question in between this discusion.
The data base is like this example:-
A B C D E F H
Item Val1 Val2 Val3 Bus 2
Val2
Car 2 blank 5 Cycle 35
Bus 1 2 1 Horse 1
Train blank blank 3
Flight blank blank 1
Cycle 20 35 Blank
Horse Blank 1 2

if I type Val2 in H1 the formula in column F should return a
nonblank
list
and its corresponding row value from column from column A as shown
above.
The
formula in F should be able to guess and return nonblank values
from
columns
B or C or D based on the value in H1 which would be header name of
these
columns! This is possible? Any question please revert back. Please
help
me.


"Luke M" wrote:

Hmm. One more attempt he
=IF(F87="","",IF(IF(AND(LEN(INDEX($F$1:$F$57,F87)) =4,COUNTIF($F$87:F87,F87)2),F87,MIN(IF(ISTEXT(OF FSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE)))=0,"",IF(AND(LEN(INDEX($F$1:$F$57,F8 7))=4,COUNTIF($F$87:F87,F87)2),F87,MIN(IF(ISTEXT (OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE)))))

Again, an array formula. I tried to setup my sheet as you
described,
data
is
in rows 37-57. Ron's VBA solution is probably more elegant, but at
least
now
if you want a formula answer, you have one.

I must admit, this was an interesting challenge.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"M" wrote:

Luke, this is very close to what I want but there are a couple
of
errors.
Unfortunately after a lot of head scratching I can't alter it
myself.

1)
When I replace all the references to the correct ones the
formula
returns a
"#REF!" error. I assume it is something to do with the offset
but
can't
work
it out.

Ref A1:A15 should be F37:F57.
This formula is entered into column M. My formula to find the
first
entry is
in row 87 and this formula starts in row 88 onwards

2)
If there are more then 4 characters in the last entry it does
not
repeat
(The repeat works fine as long as the last entry is less than 4
characters. I
am not sure if this can be solved or if I need to add a dummy
entry
that is
less than 4 characters??

Thanks again for your help. If there is a solution to the first
point I
can
start using the formula. Great!


"T. Valko" wrote:

That doesn't repeat the 11.


--
Biff
Microsoft Excel MVP


"Luke M" wrote:

Hi M!

Use Biff's formula in C1 (or mine, if you prefer), and then
in
C2:
=IF(OR(C1="",C1=MAX(IF(ISTEXT($A$1:$A$12),ROW($A$1 :$A$12),FALSE))),"",IF(AND(LEN(INDEX($A$1:$A$12,C1 ))=4,COUNTIF($C$1:C1,C1)2),C1,MIN(IF(ISTEXT(OFFS ET($A$1,C1,0,ROWS($A$1:$A$12)-C1)),ROW(OFFSET($A$1,C1,0,ROWS($A$1:$A$12)-C1)),FALSE))))

Again, this is an array formula. Not a very elegant formula
but
it
works.
Note that there are several callouts to $a$1:$a$12 which you
will
need to
adjust as necessary.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"M" wrote:

Thanks for your help however is there a solution to
include
repeating the row
number if there is more than 4 characters??

I have just tried Biff's reply. I enter the formula as an
array
in 1 cell
and then copy it down. This correctly lists the row
numbers
but
does not
repeat (see the last bit of my first post)

Thanks again.


"T. Valko" wrote:

You're welcome!

--
Biff
Microsoft Excel MVP


"Mike H" wrote:

Ah i see, I was happy to produce NUM errors with my
formula
when the matches
ran out but see this is tidier. Thank you.

Mike

"T. Valko" wrote:

=IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"),

That's an error trap.

It's more efficient than:

=IF(ISERROR(SMALL(IF(ISTEXT(A$1:A$12),ROW(A$1:A$12 )),ROWS(C$1:C1))),

COUNTIF(A$1:A$12,"*")

Returns the total number of *text* entries in the
range
and
compares that
number to number of rows the formula is copied to:

=IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"),

If the number of rows the formula is copied to is
greater
than the total
number of text entries in the range then the formula
returns a blank ("").
Otherwise, SMALL(...,n) would return a #NUM! error.

--
Biff
Microsoft Excel MVP


"Mike H" wrote:

Biff,

Just curious but what are you testing for in the
first
part of your formula,

=IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"),

why not just

=SMALL(IF(ISTEXT(A$1:A$12),ROW(A$1:A$12)),ROWS(C$1 :C1))

what am I missing?

Mike

"T. Valko" wrote:

Assume you want the results starting in cell C1.

Try this array formula** entered in C1 and
copied
down
until you get blanks:

=IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"),SMALL(IF(I STEXT(A$1:A$12),ROW(A$1:A$12)),ROWS(C$1:C1)),"")

This assumes your *REAL* data range does in fact
start
on row 1. If it
doesn't start on row 1 then we'll need to make a
slight
adjustment.

** array formulas need to be entered using the
key
combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down
both
the

Dear T. Valko,
By the way, how to get the nonblank list of items from column A in E?

"T. Valko" wrote:

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Narnimar" wrote in message
...
I reaized my mistake while copying webpage in the table. Excel is working
great now. Thank you so much T. Valko.

"T. Valko" wrote:

Here's a small sample file that demonstrates this.

xNarnimar.xls 14kb

http://cjoint.com/?hAdCaKigG6

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Im going to take a break for a few hours. When I return I'll put
together
a sample file that does what you want.

--
Biff
Microsoft Excel MVP


"Narnimar" wrote in message
news T. Valko,
Thanks. Sorry for the wrong Subject above.
I did exactly as per the your instruction but if I copy the cells down
appear blank except F1. Is there something to do more?
Cn you please see it again?

With regards
narnimar,

"T. Valko" wrote:

Try this...

You're subject line says find TEXT but your data is numeric assuming
"blank"
means empty/blank cells.

B11 = column headers Valx
B27 data you want to extract

H1 = Valx

Create this defined range name...
Goto the menu InsertNameDefine
Name: Table
Refers to: =$B$2:$D$7

Enter this formula in I1. It will return the count of records.

=COUNTA(INDEX(Table,,MATCH(H1,B11,0)))

Enter this array formula** in F1:

=IF(ROWS(F$1:F1)=I$1,INDEX(Table,SMALL(IF(INDEX(T able,,MATCH(H$1,B$1$1,0))"",ROW(Table)),ROWS(F $1:F1))-MIN(ROW(Table))+1,MATCH(H$1,B$1$1,0)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and
the
SHIFT
key then hit ENTER.

Copy down to a number of rows that is at least equal to the maximum
count of
entries in any of your Val columns. For example, the Val column with
the
maximum number of entries is Val3 with 5 entries. So, you'd have to
copy
the
formula down to at least 5 rows.

--
Biff
Microsoft Excel MVP


"Narnimar" wrote in message
...
Hi Luke, I really amazed today when I look at here your solution
wondering
about my unresolved issue which is closer to it. I dont know if it
is
right
on my part to put the question in between this discusion.
The data base is like this example:-
A B C D E F H
Item Val1 Val2 Val3 Bus 2
Val2
Car 2 blank 5 Cycle 35
Bus 1 2 1 Horse 1
Train blank blank 3
Flight blank blank 1
Cycle 20 35 Blank
Horse Blank 1 2

if I type Val2 in H1 the formula in column F should return a
nonblank
list
and its corresponding row value from column from column A as shown
above.
The
formula in F should be able to guess and return nonblank values
from
columns
B or C or D based on the value in H1 which would be header name of
these
columns! This is possible? Any question please revert back. Please
help
me.


"Luke M" wrote:

Hmm. One more attempt he
=IF(F87="","",IF(IF(AND(LEN(INDEX($F$1:$F$57,F87)) =4,COUNTIF($F$87:F87,F87)2),F87,MIN(IF(ISTEXT(OF FSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE)))=0,"",IF(AND(LEN(INDEX($F$1:$F$57,F8 7))=4,COUNTIF($F$87:F87,F87)2),F87,MIN(IF(ISTEXT (OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE)))))

Again, an array formula. I tried to setup my sheet as you
described,
data
is
in rows 37-57. Ron's VBA solution is probably more elegant, but at
least
now
if you want a formula answer, you have one.

I must admit, this was an interesting challenge.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"M" wrote:

Luke, this is very close to what I want but there are a couple
of
errors.
Unfortunately after a lot of head scratching I can't alter it
myself.

1)
When I replace all the references to the correct ones the
formula
returns a
"#REF!" error. I assume it is something to do with the offset
but
can't
work
it out.

Ref A1:A15 should be F37:F57.
This formula is entered into column M. My formula to find the
first
entry is
in row 87 and this formula starts in row 88 onwards

2)
If there are more then 4 characters in the last entry it does
not
repeat
(The repeat works fine as long as the last entry is less than 4
characters. I
am not sure if this can be solved or if I need to add a dummy
entry
that is
less than 4 characters??

Thanks again for your help. If there is a solution to the first
point I
can
start using the formula. Great!


"T. Valko" wrote:

That doesn't repeat the 11.


--
Biff
Microsoft Excel MVP


"Luke M" wrote:

Hi M!

Use Biff's formula in C1 (or mine, if you prefer), and then
in
C2:
=IF(OR(C1="",C1=MAX(IF(ISTEXT($A$1:$A$12),ROW($A$1 :$A$12),FALSE))),"",IF(AND(LEN(INDEX($A$1:$A$12,C1 ))=4,COUNTIF($C$1:C1,C1)2),C1,MIN(IF(ISTEXT(OFFS ET($A$1,C1,0,ROWS($A$1:$A$12)-C1)),ROW(OFFSET($A$1,C1,0,ROWS($A$1:$A$12)-C1)),FALSE))))

Again, this is an array formula. Not a very elegant formula
but
it
works.
Note that there are several callouts to $a$1:$a$12 which you
will
need to
adjust as necessary.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"M" wrote:

Thanks for your help however is there a solution to
include
repeating the row
number if there is more than 4 characters??

I have just tried Biff's reply. I enter the formula as an
array
in 1 cell
and then copy it down. This correctly lists the row
numbers
but
does not
repeat (see the last bit of my first post)

Thanks again.


"T. Valko" wrote:

You're welcome!

--
Biff
Microsoft Excel MVP


"Mike H" wrote:

Ah i see, I was happy to produce NUM errors with my
formula
when the matches
ran out but see this is tidier. Thank you.

Mike

"T. Valko" wrote:

=IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"),

That's an error trap.

It's more efficient than:

=IF(ISERROR(SMALL(IF(ISTEXT(A$1:A$12),ROW(A$1:A$12 )),ROWS(C$1:C1))),

COUNTIF(A$1:A$12,"*")

Returns the total number of *text* entries in the
range
and
compares that
number to number of rows the formula is copied to:

=IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"),

If the number of rows the formula is copied to is
greater
than the total
number of text entries in the range then the formula
returns a blank ("").
Otherwise, SMALL(...,n) would return a #NUM! error.

--
Biff
Microsoft Excel MVP


"Mike H" wrote:

Biff,

Just curious but what are you testing for in the

Ok, I assume you mean the items in column A that correspond to the non-blank
rows in Valx. Redefine the named range table to include column A:

Name: Table
Refers to: =$A$2:$D$7

Change the formula in I1 to:

=COUNTA(INDEX(Table,,MATCH(H1,A11,0)))

Change this expression in the formula in F1 (in 2 places):

From:

MATCH(H$1,B$1$1,0)

To:

MATCH(H$1,A$1$1,0)

Make sure you re-enter as an array!

And finally, enter this array formula in E1 and copy down:

=IF(ROWS(E$1:E1)=I$1,INDEX(Table,SMALL(IF(INDEX(T able,,MATCH(H$1,A$1$1,0))"",ROW(Table)),ROWS(E $1:E1))-MIN(ROW(Table))+1,1),"")

--
Biff
Microsoft Excel MVP


"Narnimar" wrote in message
...
Dear T. Valko,
By the way, how to get the nonblank list of items from column A in E?

"T. Valko" wrote:

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Narnimar" wrote in message
...
I reaized my mistake while copying webpage in the table. Excel is
working
great now. Thank you so much T. Valko.

"T. Valko" wrote:

Here's a small sample file that demonstrates this.

xNarnimar.xls 14kb

http://cjoint.com/?hAdCaKigG6

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Im going to take a break for a few hours. When I return I'll put
together
a sample file that does what you want.

--
Biff
Microsoft Excel MVP


"Narnimar" wrote in message
news T. Valko,
Thanks. Sorry for the wrong Subject above.
I did exactly as per the your instruction but if I copy the cells
down
appear blank except F1. Is there something to do more?
Cn you please see it again?

With regards
narnimar,

"T. Valko" wrote:

Try this...

You're subject line says find TEXT but your data is numeric
assuming
"blank"
means empty/blank cells.

B11 = column headers Valx
B27 data you want to extract

H1 = Valx

Create this defined range name...
Goto the menu InsertNameDefine
Name: Table
Refers to: =$B$2:$D$7

Enter this formula in I1. It will return the count of records.

=COUNTA(INDEX(Table,,MATCH(H1,B11,0)))

Enter this array formula** in F1:

=IF(ROWS(F$1:F1)=I$1,INDEX(Table,SMALL(IF(INDEX(T able,,MATCH(H$1,B$1$1,0))"",ROW(Table)),ROWS(F $1:F1))-MIN(ROW(Table))+1,MATCH(H$1,B$1$1,0)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and
the
SHIFT
key then hit ENTER.

Copy down to a number of rows that is at least equal to the
maximum
count of
entries in any of your Val columns. For example, the Val column
with
the
maximum number of entries is Val3 with 5 entries. So, you'd have
to
copy
the
formula down to at least 5 rows.

--
Biff
Microsoft Excel MVP


"Narnimar" wrote in message
...
Hi Luke, I really amazed today when I look at here your solution
wondering
about my unresolved issue which is closer to it. I dont know if
it
is
right
on my part to put the question in between this discusion.
The data base is like this example:-
A B C D E F H
Item Val1 Val2 Val3 Bus 2
Val2
Car 2 blank 5 Cycle 35
Bus 1 2 1 Horse 1
Train blank blank 3
Flight blank blank 1
Cycle 20 35 Blank
Horse Blank 1 2

if I type Val2 in H1 the formula in column F should return a
nonblank
list
and its corresponding row value from column from column A as
shown
above.
The
formula in F should be able to guess and return nonblank values
from
columns
B or C or D based on the value in H1 which would be header name
of
these
columns! This is possible? Any question please revert back.
Please
help
me.


"Luke M" wrote:

Hmm. One more attempt he
=IF(F87="","",IF(IF(AND(LEN(INDEX($F$1:$F$57,F87)) =4,COUNTIF($F$87:F87,F87)2),F87,MIN(IF(ISTEXT(OF FSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE)))=0,"",IF(AND(LEN(INDEX($F$1:$F$57,F8 7))=4,COUNTIF($F$87:F87,F87)2),F87,MIN(IF(ISTEXT (OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE)))))

Again, an array formula. I tried to setup my sheet as you
described,
data
is
in rows 37-57. Ron's VBA solution is probably more elegant, but
at
least
now
if you want a formula answer, you have one.

I must admit, this was an interesting challenge.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"M" wrote:

Luke, this is very close to what I want but there are a
couple
of
errors.
Unfortunately after a lot of head scratching I can't alter it
myself.

1)
When I replace all the references to the correct ones the
formula
returns a
"#REF!" error. I assume it is something to do with the offset
but
can't
work
it out.

Ref A1:A15 should be F37:F57.
This formula is entered into column M. My formula to find the
first
entry is
in row 87 and this formula starts in row 88 onwards

2)
If there are more then 4 characters in the last entry it does
not
repeat
(The repeat works fine as long as the last entry is less than
4
characters. I
am not sure if this can be solved or if I need to add a dummy
entry
that is
less than 4 characters??

Thanks again for your help. If there is a solution to the
first
point I
can
start using the formula. Great!


"T. Valko" wrote:

That doesn't repeat the 11.


--
Biff
Microsoft Excel MVP


"Luke M" wrote:

Hi M!

Use Biff's formula in C1 (or mine, if you prefer), and
then
in
C2:
=IF(OR(C1="",C1=MAX(IF(ISTEXT($A$1:$A$12),ROW($A$1 :$A$12),FALSE))),"",IF(AND(LEN(INDEX($A$1:$A$12,C1 ))=4,COUNTIF($C$1:C1,C1)2),C1,MIN(IF(ISTEXT(OFFS ET($A$1,C1,0,ROWS($A$1:$A$12)-C1)),ROW(OFFSET($A$1,C1,0,ROWS($A$1:$A$12)-C1)),FALSE))))

Again, this is an array formula. Not a very elegant
formula
but
it
works.
Note that there are several callouts to $a$1:$a$12 which
you
will
need to
adjust as necessary.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"M" wrote:

Thanks for your help however is there a solution to
include
repeating the row
number if there is more than 4 characters??

I have just tried Biff's reply. I enter the formula as
an
array
in 1 cell
and then copy it down. This correctly lists the row
numbers
but
does not
repeat (see the last bit of my first post)

Thanks again.


"T. Valko" wrote:

You're welcome!

--
Biff
Microsoft Excel MVP


"Mike H" wrote:

Ah i see, I was happy to produce NUM errors with my
formula
when the matches
ran out but see this is tidier. Thank you.

Mike

"T. Valko" wrote:

=IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"),

That's an error trap.

It's more efficient than:

=IF(ISERROR(SMALL(IF(ISTEXT(A$1:A$12),ROW(A$1:A$12 )),ROWS(C$1:C1))),

COUNTIF(A$1:A$12,"*")

Returns the total number of *text* entries in the
range
and
compares that
number to number of rows the formula is copied
to:

=IF(ROWS(C$1:C1)=COUNTIF(A$1:A$12,"*"),

If the number of rows the formula is copied to is
greater
than the total
number of text entries in the range then the
formula
returns a blank ("").
Otherwise, SMALL(...,n) would return a #NUM!
error.

--
Biff
Microsoft Excel MVP


"Mike H" wrote:

Biff,

Just curious but what are you testing for in
the

Thank you so much!

"T. Valko" wrote:

Ok, I assume you mean the items in column A that correspond to the non-blank
rows in Valx. Redefine the named range table to include column A:

Name: Table
Refers to: =$A$2:$D$7

Change the formula in I1 to:

=COUNTA(INDEX(Table,,MATCH(H1,A11,0)))

Change this expression in the formula in F1 (in 2 places):

From:

MATCH(H$1,B$1$1,0)

To:

MATCH(H$1,A$1$1,0)

Make sure you re-enter as an array!

And finally, enter this array formula in E1 and copy down:

=IF(ROWS(E$1:E1)=I$1,INDEX(Table,SMALL(IF(INDEX(T able,,MATCH(H$1,A$1$1,0))"",ROW(Table)),ROWS(E $1:E1))-MIN(ROW(Table))+1,1),"")

--
Biff
Microsoft Excel MVP


"Narnimar" wrote in message
...
Dear T. Valko,
By the way, how to get the nonblank list of items from column A in E?

"T. Valko" wrote:

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Narnimar" wrote in message
...
I reaized my mistake while copying webpage in the table. Excel is
working
great now. Thank you so much T. Valko.

"T. Valko" wrote:

Here's a small sample file that demonstrates this.

xNarnimar.xls 14kb

http://cjoint.com/?hAdCaKigG6

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Im going to take a break for a few hours. When I return I'll put
together
a sample file that does what you want.

--
Biff
Microsoft Excel MVP


"Narnimar" wrote in message
news T. Valko,
Thanks. Sorry for the wrong Subject above.
I did exactly as per the your instruction but if I copy the cells
down
appear blank except F1. Is there something to do more?
Cn you please see it again?

With regards
narnimar,

"T. Valko" wrote:

Try this...

You're subject line says find TEXT but your data is numeric
assuming
"blank"
means empty/blank cells.

B11 = column headers Valx
B27 data you want to extract

H1 = Valx

Create this defined range name...
Goto the menu InsertNameDefine
Name: Table
Refers to: =$B$2:$D$7

Enter this formula in I1. It will return the count of records.

=COUNTA(INDEX(Table,,MATCH(H1,B11,0)))

Enter this array formula** in F1:

=IF(ROWS(F$1:F1)=I$1,INDEX(Table,SMALL(IF(INDEX(T able,,MATCH(H$1,B$1$1,0))"",ROW(Table)),ROWS(F $1:F1))-MIN(ROW(Table))+1,MATCH(H$1,B$1$1,0)),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and
the
SHIFT
key then hit ENTER.

Copy down to a number of rows that is at least equal to the
maximum
count of
entries in any of your Val columns. For example, the Val column
with
the
maximum number of entries is Val3 with 5 entries. So, you'd have
to
copy
the
formula down to at least 5 rows.

--
Biff
Microsoft Excel MVP


"Narnimar" wrote in message
...
Hi Luke, I really amazed today when I look at here your solution
wondering
about my unresolved issue which is closer to it. I dont know if
it
is
right
on my part to put the question in between this discusion.
The data base is like this example:-
A B C D E F H
Item Val1 Val2 Val3 Bus 2
Val2
Car 2 blank 5 Cycle 35
Bus 1 2 1 Horse 1
Train blank blank 3
Flight blank blank 1
Cycle 20 35 Blank
Horse Blank 1 2

if I type Val2 in H1 the formula in column F should return a
nonblank
list
and its corresponding row value from column from column A as
shown
above.
The
formula in F should be able to guess and return nonblank values
from
columns
B or C or D based on the value in H1 which would be header name
of
these
columns! This is possible? Any question please revert back.
Please
help
me.


"Luke M" wrote:

Hmm. One more attempt he
=IF(F87="","",IF(IF(AND(LEN(INDEX($F$1:$F$57,F87)) =4,COUNTIF($F$87:F87,F87)2),F87,MIN(IF(ISTEXT(OF FSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE)))=0,"",IF(AND(LEN(INDEX($F$1:$F$57,F8 7))=4,COUNTIF($F$87:F87,F87)2),F87,MIN(IF(ISTEXT (OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE)))))

Again, an array formula. I tried to setup my sheet as you
described,
data
is
in rows 37-57. Ron's VBA solution is probably more elegant, but
at
least
now
if you want a formula answer, you have one.

I must admit, this was an interesting challenge.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"M" wrote:

Luke, this is very close to what I want but there are a
couple
of
errors.
Unfortunately after a lot of head scratching I can't alter it
myself.

1)
When I replace all the references to the correct ones the
formula
returns a
"#REF!" error. I assume it is something to do with the offset
but
can't
work
it out.

Ref A1:A15 should be F37:F57.
This formula is entered into column M. My formula to find the
first
entry is
in row 87 and this formula starts in row 88 onwards

2)
If there are more then 4 characters in the last entry it does
not
repeat
(The repeat works fine as long as the last entry is less than
4
characters. I
am not sure if this can be solved or if I need to add a dummy
entry
that is
less than 4 characters??

Thanks again for your help. If there is a solution to the
first
point I
can
start using the formula. Great!


"T. Valko" wrote:

That doesn't repeat the 11.


--
Biff
Microsoft Excel MVP


"Luke M" wrote:

Hi M!

Use Biff's formula in C1 (or mine, if you prefer), and
then
in
C2:
=IF(OR(C1="",C1=MAX(IF(ISTEXT($A$1:$A$12),ROW($A$1 :$A$12),FALSE))),"",IF(AND(LEN(INDEX($A$1:$A$12,C1 ))=4,COUNTIF($C$1:C1,C1)2),C1,MIN(IF(ISTEXT(OFFS ET($A$1,C1,0,ROWS($A$1:$A$12)-C1)),ROW(OFFSET($A$1,C1,0,ROWS($A$1:$A$12)-C1)),FALSE))))

Again, this is an array formula. Not a very elegant
formula
but
it
works.
Note that there are several callouts to $a$1:$a$12 which
you
will
need to
adjust as necessary.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"M" wrote:

Thanks for your help however is there a solution to
include
repeating the row
number if there is more than 4 characters??

I have just tried Biff's reply. I enter the formula as
an
array
in 1 cell
and then copy it down. This correctly lists the row
numbers
but
does not
repeat (see the last bit of my first post)

Thanks again.

You're welcome!

--
Biff
Microsoft Excel MVP


"Narnimar" wrote in message
...
Thank you so much!

"T. Valko" wrote:

Ok, I assume you mean the items in column A that correspond to the
non-blank
rows in Valx. Redefine the named range table to include column A:

Name: Table
Refers to: =$A$2:$D$7

Change the formula in I1 to:

=COUNTA(INDEX(Table,,MATCH(H1,A11,0)))

Change this expression in the formula in F1 (in 2 places):

From:

MATCH(H$1,B$1$1,0)

To:

MATCH(H$1,A$1$1,0)

Make sure you re-enter as an array!

And finally, enter this array formula in E1 and copy down:

=IF(ROWS(E$1:E1)=I$1,INDEX(Table,SMALL(IF(INDEX(T able,,MATCH(H$1,A$1$1,0))"",ROW(Table)),ROWS(E $1:E1))-MIN(ROW(Table))+1,1),"")

--
Biff
Microsoft Excel MVP


"Narnimar" wrote in message
...
Dear T. Valko,
By the way, how to get the nonblank list of items from column A in E?

"T. Valko" wrote:

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Narnimar" wrote in message
...
I reaized my mistake while copying webpage in the table. Excel is
working
great now. Thank you so much T. Valko.

"T. Valko" wrote:

Here's a small sample file that demonstrates this.

xNarnimar.xls 14kb

http://cjoint.com/?hAdCaKigG6

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
Im going to take a break for a few hours. When I return I'll put
together
a sample file that does what you want.

--
Biff
Microsoft Excel MVP


"Narnimar" wrote in message
news T. Valko,
Thanks. Sorry for the wrong Subject above.
I did exactly as per the your instruction but if I copy the
cells
down
appear blank except F1. Is there something to do more?
Cn you please see it again?

With regards
narnimar,

"T. Valko" wrote:

Try this...

You're subject line says find TEXT but your data is numeric
assuming
"blank"
means empty/blank cells.

B11 = column headers Valx
B27 data you want to extract

H1 = Valx

Create this defined range name...
Goto the menu InsertNameDefine
Name: Table
Refers to: =$B$2:$D$7

Enter this formula in I1. It will return the count of records.

=COUNTA(INDEX(Table,,MATCH(H1,B11,0)))

Enter this array formula** in F1:

=IF(ROWS(F$1:F1)=I$1,INDEX(Table,SMALL(IF(INDEX(T able,,MATCH(H$1,B$1$1,0))"",ROW(Table)),ROWS(F $1:F1))-MIN(ROW(Table))+1,MATCH(H$1,B$1$1,0)),"")

** array formulas need to be entered using the key combination
of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key
and
the
SHIFT
key then hit ENTER.

Copy down to a number of rows that is at least equal to the
maximum
count of
entries in any of your Val columns. For example, the Val column
with
the
maximum number of entries is Val3 with 5 entries. So, you'd
have
to
copy
the
formula down to at least 5 rows.

--
Biff
Microsoft Excel MVP


"Narnimar" wrote in
message
...
Hi Luke, I really amazed today when I look at here your
solution
wondering
about my unresolved issue which is closer to it. I dont know
if
it
is
right
on my part to put the question in between this discusion.
The data base is like this example:-
A B C D E F H
Item Val1 Val2 Val3 Bus 2
Val2
Car 2 blank 5 Cycle 35
Bus 1 2 1 Horse 1
Train blank blank 3
Flight blank blank 1
Cycle 20 35 Blank
Horse Blank 1 2

if I type Val2 in H1 the formula in column F should return a
nonblank
list
and its corresponding row value from column from column A as
shown
above.
The
formula in F should be able to guess and return nonblank
values
from
columns
B or C or D based on the value in H1 which would be header
name
of
these
columns! This is possible? Any question please revert back.
Please
help
me.


"Luke M" wrote:

Hmm. One more attempt he
=IF(F87="","",IF(IF(AND(LEN(INDEX($F$1:$F$57,F87)) =4,COUNTIF($F$87:F87,F87)2),F87,MIN(IF(ISTEXT(OF FSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE)))=0,"",IF(AND(LEN(INDEX($F$1:$F$57,F8 7))=4,COUNTIF($F$87:F87,F87)2),F87,MIN(IF(ISTEXT (OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),ROW(OFFSET($F$1,F87,0,ROWS($F$1:$F$57)-F87)),FALSE)))))

Again, an array formula. I tried to setup my sheet as you
described,
data
is
in rows 37-57. Ron's VBA solution is probably more elegant,
but
at
least
now
if you want a formula answer, you have one.

I must admit, this was an interesting challenge.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"M" wrote:

Luke, this is very close to what I want but there are a
couple
of
errors.
Unfortunately after a lot of head scratching I can't alter
it
myself.

1)
When I replace all the references to the correct ones the
formula
returns a
"#REF!" error. I assume it is something to do with the
offset
but
can't
work
it out.

Ref A1:A15 should be F37:F57.
This formula is entered into column M. My formula to find
the
first
entry is
in row 87 and this formula starts in row 88 onwards

2)
If there are more then 4 characters in the last entry it
does
not
repeat
(The repeat works fine as long as the last entry is less
than
4
characters. I
am not sure if this can be solved or if I need to add a
dummy
entry
that is
less than 4 characters??

Thanks again for your help. If there is a solution to the
first
point I
can
start using the formula. Great!


"T. Valko" wrote:

That doesn't repeat the 11.


--
Biff
Microsoft Excel MVP


"Luke M" wrote:

Hi M!

Use Biff's formula in C1 (or mine, if you prefer), and
then
in
C2:
=IF(OR(C1="",C1=MAX(IF(ISTEXT($A$1:$A$12),ROW($A$1 :$A$12),FALSE))),"",IF(AND(LEN(INDEX($A$1:$A$12,C1 ))=4,COUNTIF($C$1:C1,C1)2),C1,MIN(IF(ISTEXT(OFFS ET($A$1,C1,0,ROWS($A$1:$A$12)-C1)),ROW(OFFSET($A$1,C1,0,ROWS($A$1:$A$12)-C1)),FALSE))))

Again, this is an array formula. Not a very elegant
formula
but
it
works.
Note that there are several callouts to $a$1:$a$12
which
you
will
need to
adjust as necessary.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"M" wrote:

Thanks for your help however is there a solution to
include
repeating the row
number if there is more than 4 characters??

I have just tried Biff's reply. I enter the formula
as
an
array
in 1 cell
and then copy it down. This correctly lists the row
numbers
but
does not
repeat (see the last bit of my first post)

Thanks again.