Find duplicate values in an array

[Bony Pony]

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)),SUMPRODUCT(--($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

 

[מיכ×ל (מיקי) ×בידן]

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

 

[Bony Pony]

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 ..."

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

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)),SUMPRODUCT(--($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

 

[Lars-Åke Aspelin]

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)),SUMPRODUCT(--($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_data)+COLUMN(my_data)),COLUMN()-COLUMN($C159))/1000),
MOD(SMALL(IF(my_data=$C$179,1000*ROW(my_data)+COLUMN(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

 

[Lars-Åke Aspelin]

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)),SUMPRODUCT(--($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_data)+COLUMN(my_data)),COLUMN()-COLUMN($C159))/1000),
MOD(SMALL(IF(my_data=$C$179,1000*ROW(my_data)+COLUMN(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

 

[JLatham]

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.

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 ..."

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

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)),SUMPRODUCT(--($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

 

[מיכ×ל (מיקי) ×בידן]

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

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.

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 ..."

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


:

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)),SUMPRODUCT(--($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

 

[Bony Pony]

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!

 

[JLatham]

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.

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

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.

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 ..."


:

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


:

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)),SUMPRODUCT(--($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