Hi everyone,
21:00 on a Saturday night and I'm struggling with this!

I have textual data in cells C158:L177

Unfortunately these are not all unique values ...

I want to get the address of each dearched instance.

In cell C179 I want to enter a search text which exists in the data.

In D179 I want the address of the first instance, E179 the address of the
second etc.

For unique values the formula:
=ADDRESS(SUMPRODUCT(--($C$158:$L$177=C179)*ROW($C$158:$L$177)),SUMPRODUC T(--($C$158:$L$177=C179)*COLUMN($C$158:$L$177)),4)

works like a dream but for multiple instances, it doesn't work.

I can do this with VBA in a heartbeat but I am determined to do it with a
non array entered function.

Please can anyone hekp?

Kind regards,
Bony


--
"There are 10 types of people in this world. Those who understand Binary
and those who don''t ..."

Ctrl+[F] will do the job and present you ALL the cell addresses where the
search criteria was found .
Micky


"Bony Pony" wrote:

Hi everyone,
21:00 on a Saturday night and I'm struggling with this!

I have textual data in cells C158:L177

Unfortunately these are not all unique values ...

I want to get the address of each dearched instance.

In cell C179 I want to enter a search text which exists in the data.

In D179 I want the address of the first instance, E179 the address of the
second etc.

For unique values the formula:
=ADDRESS(SUMPRODUCT(--($C$158:$L$177=C179)*ROW($C$158:$L$177)),SUMPRODUC T(--($C$158:$L$177=C179)*COLUMN($C$158:$L$177)),4)

works like a dream but for multiple instances, it doesn't work.

I can do this with VBA in a heartbeat but I am determined to do it with a
non array entered function.

Please can anyone hekp?

Kind regards,
Bony


--
"There are 10 types of people in this world. Those who understand Binary
and those who don''t ..."

Thanks for pointing out the blindingly obvious - did you read the part about
wanting to return the ADDRESS of the location?
--
"There are 10 types of people in this world. Those who understand Binary
and those who don''t ..."


"מיכ�ל (מיקי) �בידן" wrote:

Ctrl+[F] will do the job and present you ALL the cell addresses where the
search criteria was found .
Micky


"Bony Pony" wrote:

Hi everyone,
21:00 on a Saturday night and I'm struggling with this!

I have textual data in cells C158:L177

Unfortunately these are not all unique values ...

I want to get the address of each dearched instance.

In cell C179 I want to enter a search text which exists in the data.

In D179 I want the address of the first instance, E179 the address of the
second etc.

For unique values the formula:
=ADDRESS(SUMPRODUCT(--($C$158:$L$177=C179)*ROW($C$158:$L$177)),SUMPRODUC T(--($C$158:$L$177=C179)*COLUMN($C$158:$L$177)),4)

works like a dream but for multiple instances, it doesn't work.

I can do this with VBA in a heartbeat but I am determined to do it with a
non array entered function.

Please can anyone hekp?

Kind regards,
Bony


--
"There are 10 types of people in this world. Those who understand Binary
and those who don''t ..."

I should think that those seeking assistance would see the wisdom of NOT
BEING RUDE.

At this point, I would suggest that you "...do this with VBA in a heartbeat
...." since attempts by others to assist seem to have failed.

"Bony Pony" wrote:

Thanks for pointing out the blindingly obvious - did you read the part about
wanting to return the ADDRESS of the location?
--
"There are 10 types of people in this world. Those who understand Binary
and those who don''t ..."


"מיכ�ל (מיקי) �בידן" wrote:

Ctrl+[F] will do the job and present you ALL the cell addresses where the
search criteria was found .
Micky


"Bony Pony" wrote:

Hi everyone,
21:00 on a Saturday night and I'm struggling with this!

I have textual data in cells C158:L177

Unfortunately these are not all unique values ...

I want to get the address of each dearched instance.

In cell C179 I want to enter a search text which exists in the data.

In D179 I want the address of the first instance, E179 the address of the
second etc.

For unique values the formula:
=ADDRESS(SUMPRODUCT(--($C$158:$L$177=C179)*ROW($C$158:$L$177)),SUMPRODUC T(--($C$158:$L$177=C179)*COLUMN($C$158:$L$177)),4)

works like a dream but for multiple instances, it doesn't work.

I can do this with VBA in a heartbeat but I am determined to do it with a
non array entered function.

Please can anyone hekp?

Kind regards,
Bony


--
"There are 10 types of people in this world. Those who understand Binary
and those who don''t ..."

Although I didn't 100% meet the question - the way I suggested enables him to
click each and every found address and to "jump" to a particular cell.
Micky


"JLatham" wrote:

I should think that those seeking assistance would see the wisdom of NOT
BEING RUDE.

At this point, I would suggest that you "...do this with VBA in a heartbeat
..." since attempts by others to assist seem to have failed.

"Bony Pony" wrote:

Thanks for pointing out the blindingly obvious - did you read the part about
wanting to return the ADDRESS of the location?
--
"There are 10 types of people in this world. Those who understand Binary
and those who don''t ..."


"מיכ�ל (מיקי) �בידן" wrote:

Ctrl+[F] will do the job and present you ALL the cell addresses where the
search criteria was found .
Micky


"Bony Pony" wrote:

Hi everyone,
21:00 on a Saturday night and I'm struggling with this!

I have textual data in cells C158:L177

Unfortunately these are not all unique values ...

I want to get the address of each dearched instance.

In cell C179 I want to enter a search text which exists in the data.

In D179 I want the address of the first instance, E179 the address of the
second etc.

For unique values the formula:
=ADDRESS(SUMPRODUCT(--($C$158:$L$177=C179)*ROW($C$158:$L$177)),SUMPRODUC T(--($C$158:$L$177=C179)*COLUMN($C$158:$L$177)),4)

works like a dream but for multiple instances, it doesn't work.

I can do this with VBA in a heartbeat but I am determined to do it with a
non array entered function.

Please can anyone hekp?

Kind regards,
Bony


--
"There are 10 types of people in this world. Those who understand Binary
and those who don''t ..."

Apologies for side-tracking the conversation, but I just hate rudeness toward
someone attempting to assist, even if that assistance turns out to be totally
off base (which you were definitely not). A simple "that's close, but how
would I modify it to..." would have been more appropriate from Bony Pony.
Heck, I was totally wrong in a recent posting (told someone I didn't think
something could be done without VBA, only to be proven 100% wrong within 3
more postings), but all were courteous enough not to thumb their nose at my
dumb response.
Anyhow, I think Bony Pony owes you (and the forum) an apology for his/her
bad manners. And that's all I'm going to say on the subject.

"מיכ�ל (מיקי) �בידן" wrote:

Although I didn't 100% meet the question - the way I suggested enables him to
click each and every found address and to "jump" to a particular cell.
Micky


"JLatham" wrote:

I should think that those seeking assistance would see the wisdom of NOT
BEING RUDE.

At this point, I would suggest that you "...do this with VBA in a heartbeat
..." since attempts by others to assist seem to have failed.

"Bony Pony" wrote:

Thanks for pointing out the blindingly obvious - did you read the part about
wanting to return the ADDRESS of the location?
--
"There are 10 types of people in this world. Those who understand Binary
and those who don''t ..."


"מיכ�ל (מיקי) �בידן" wrote:

Ctrl+[F] will do the job and present you ALL the cell addresses where the
search criteria was found .
Micky


"Bony Pony" wrote:

Hi everyone,
21:00 on a Saturday night and I'm struggling with this!

I have textual data in cells C158:L177

Unfortunately these are not all unique values ...

I want to get the address of each dearched instance.

In cell C179 I want to enter a search text which exists in the data.

In D179 I want the address of the first instance, E179 the address of the
second etc.

For unique values the formula:
=ADDRESS(SUMPRODUCT(--($C$158:$L$177=C179)*ROW($C$158:$L$177)),SUMPRODUC T(--($C$158:$L$177=C179)*COLUMN($C$158:$L$177)),4)

works like a dream but for multiple instances, it doesn't work.

I can do this with VBA in a heartbeat but I am determined to do it with a
non array entered function.

Please can anyone hekp?

Kind regards,
Bony


--
"There are 10 types of people in this world. Those who understand Binary
and those who don''t ..."

On Sat, 13 Feb 2010 12:59:15 -0800, Bony Pony
wrote:

Hi everyone,
21:00 on a Saturday night and I'm struggling with this!

I have textual data in cells C158:L177

Unfortunately these are not all unique values ...

I want to get the address of each dearched instance.

In cell C179 I want to enter a search text which exists in the data.

In D179 I want the address of the first instance, E179 the address of the
second etc.

For unique values the formula:
=ADDRESS(SUMPRODUCT(--($C$158:$L$177=C179)*ROW($C$158:$L$177)),SUMPRODUC T(--($C$158:$L$177=C179)*COLUMN($C$158:$L$177)),4)

works like a dream but for multiple instances, it doesn't work.

I can do this with VBA in a heartbeat but I am determined to do it with a
non array entered function.

Please can anyone hekp?

Kind regards,
Bony

I don't think it will be possible without an array entered formula.
But if you can accept an array formula, try this formula in cell D159.

=IF(COLUMN()-COLUMN($C159)COUNT(IF(my_data=$C$179,1000*ROW(my_ data)+COLUMN(my_data))),"",
ADDRESS(INT(SMALL(IF(my_data=$C$179,1000*ROW(my_da ta)+COLUMN(my_data)),COLUMN()-COLUMN($C159))/1000),
MOD(SMALL(IF(my_data=$C$179,1000*ROW(my_data)+COLU MN(my_data)),COLUMN()-COLUMN($C159)),1000),4))

Note: This is an array formula that should be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER.

my_data is just an abbreviation for you data range, you may replace it
with $C158:$L177 (please observe the $) if you don't want to name your
data range.

Copy the formula from D159 as far to the right as you need to list the
addresses of the most frequent multiple data.

Hope this helps / Lars-Åke







Note: This is an array formula that must be confirmed by

On Sat, 13 Feb 2010 12:59:15 -0800, Bony Pony
wrote:

Hi everyone,
21:00 on a Saturday night and I'm struggling with this!

I have textual data in cells C158:L177

Unfortunately these are not all unique values ...

I want to get the address of each dearched instance.

In cell C179 I want to enter a search text which exists in the data.

In D179 I want the address of the first instance, E179 the address of the
second etc.

For unique values the formula:
=ADDRESS(SUMPRODUCT(--($C$158:$L$177=C179)*ROW($C$158:$L$177)),SUMPRODUC T(--($C$158:$L$177=C179)*COLUMN($C$158:$L$177)),4)

works like a dream but for multiple instances, it doesn't work.

I can do this with VBA in a heartbeat but I am determined to do it with a
non array entered function.

Please can anyone hekp?

Kind regards,
Bony

I don't think it will be possible without an array entered formula.
But if you can accept an array formula, try this formula in cell D159.

=IF(COLUMN()-COLUMN($C159)COUNT(IF(my_data=$C$179,1000*ROW(my_ data)+COLUMN(my_data))),"",
ADDRESS(INT(SMALL(IF(my_data=$C$179,1000*ROW(my_da ta)+COLUMN(my_data)),COLUMN()-COLUMN($C159))/1000),
MOD(SMALL(IF(my_data=$C$179,1000*ROW(my_data)+COLU MN(my_data)),COLUMN()-COLUMN($C159)),1000),4))

Note: This is an array formula that should be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER.

my_data is just an abbreviation for you data range, you may replace it
with $C158:$L177 (please observe the $) if you don't want to name your
data range.

Copy the formula from D159 as far to the right as you need to list the
addresses of the most frequent multiple data.

Hope this helps / Lars-Åke

Hi Lars,
Many thanks for taking the time to read and understand my question. I have
tried your array and can't get it to work - however you have given me a
couple of ideas.

Thanks again!
--
"There are 10 types of people in this world. Those who understand Binary
and those who don''t ..."


"Lars-Ã…ke Aspelin" wrote:

On Sat, 13 Feb 2010 12:59:15 -0800, Bony Pony
wrote:

Hi everyone,
21:00 on a Saturday night and I'm struggling with this!

I have textual data in cells C158:L177

Unfortunately these are not all unique values ...

I want to get the address of each dearched instance.

In cell C179 I want to enter a search text which exists in the data.

In D179 I want the address of the first instance, E179 the address of the
second etc.

For unique values the formula:
=ADDRESS(SUMPRODUCT(--($C$158:$L$177=C179)*ROW($C$158:$L$177)),SUMPRODUC T(--($C$158:$L$177=C179)*COLUMN($C$158:$L$177)),4)

works like a dream but for multiple instances, it doesn't work.

I can do this with VBA in a heartbeat but I am determined to do it with a
non array entered function.

Please can anyone hekp?

Kind regards,
Bony

I don't think it will be possible without an array entered formula.
But if you can accept an array formula, try this formula in cell D159.

=IF(COLUMN()-COLUMN($C159)COUNT(IF(my_data=$C$179,1000*ROW(my_ data)+COLUMN(my_data))),"",
ADDRESS(INT(SMALL(IF(my_data=$C$179,1000*ROW(my_da ta)+COLUMN(my_data)),COLUMN()-COLUMN($C159))/1000),
MOD(SMALL(IF(my_data=$C$179,1000*ROW(my_data)+COLU MN(my_data)),COLUMN()-COLUMN($C159)),1000),4))

Note: This is an array formula that should be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER.

my_data is just an abbreviation for you data range, you may replace it
with $C158:$L177 (please observe the $) if you don't want to name your
data range.

Copy the formula from D159 as far to the right as you need to list the
addresses of the most frequent multiple data.

Hope this helps / Lars-Ã…ke
.