I have a column of some 10.000 values with duplicates highlighted; now, I'd
like ot have all of these duplicates ( not just a list of distinct values)
stored to list with a row reference of each (in a neighbouring column, for
example). Does anyone have an idea?

Assume you mean "duplicate" as in the 2nd occurence onwards of an item

Source data assumed running in A2 down

In B2:
=IF(A2="","",IF(COUNTIF(A$2:A2,A2)1,ROW(),""))
Leave B1 blank

In C2:
=IF(ROWS($1:1)COUNT($B:$B),"",INDEX(A:A,SMALL($B: $B,ROWS($1:1))))
Copy C2 to D2. Select B2:D2, copy down to cover the max expected extent of
source data in col A. Hide away col B. Col C returns the duplicates from col
A, col D returns the corresponding row numbers for the duplicates.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mac" wrote:
I have a column of some 10.000 values with duplicates highlighted; now, I'd
like ot have all of these duplicates ( not just a list of distinct values)
stored to list with a row reference of each (in a neighbouring column, for
example). Does anyone have an idea?

This does not seem to work properly...can you send an example sheet via
email, please?

"Max" wrote:

Assume you mean "duplicate" as in the 2nd occurence onwards of an item

Source data assumed running in A2 down

In B2:
=IF(A2="","",IF(COUNTIF(A$2:A2,A2)1,ROW(),""))
Leave B1 blank

In C2:
=IF(ROWS($1:1)COUNT($B:$B),"",INDEX(A:A,SMALL($B: $B,ROWS($1:1))))
Copy C2 to D2. Select B2:D2, copy down to cover the max expected extent of
source data in col A. Hide away col B. Col C returns the duplicates from col
A, col D returns the corresponding row numbers for the duplicates.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mac" wrote:
I have a column of some 10.000 values with duplicates highlighted; now, I'd
like ot have all of these duplicates ( not just a list of distinct values)
stored to list with a row reference of each (in a neighbouring column, for
example). Does anyone have an idea?

"Mac" wrote:
This does not seem to work properly...
can you send an example sheet via email, please?

Here's a link to an illustrative sample:
http://www.flypicture.com/download/NTA5MzY=
Listing duplicates n their row numbers.xls

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Now I've got the hang of that!:-)Thank you very much,Max!

"Max" wrote:

"Mac" wrote:
This does not seem to work properly...
can you send an example sheet via email, please?

Here's a link to an illustrative sample:
http://www.flypicture.com/download/NTA5MzY=
Listing duplicates n their row numbers.xls

--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

welcome, glad you got it up.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mac" wrote in message
...
Now I've got the hang of that!:-)
Thank you very much,Max!