View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.misc
Bony Pony[_3_] Bony Pony[_3_] is offline
external usenet poster
 
Posts: 36
Default Find duplicate values in an array

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