Extracting duplicates with row ref's to a list

[Mac]

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?

 

[Max]

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 B22, 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.

 

[Mac]

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

 

[Max]

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

 

[Mac]

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

 

[Max]

welcome, glad you got it up.

Now I've got the hang of that!

Thank you very much,Max!

 

[Mac]

Well, there's one more point - looking at your formulas, I don't see why the
first occurences in A are not included? In B, you check the whole column for
a text occurence, but why isn't this working with the first occurence of
every item? (if I understood why, I'd have altered that formulas myself.. )

 

[Max]

.. don't see why the first occurences in A are not included?

I wasn't sure from your original posting, that's why I mentioned:

If you want to extract a full list including 1st occurences, just change
amend the formula in the criteria col B to:

In B2:
=IF(A2="","",IF(COUNTIF(A:A,A2)>1,ROW(),""))
Copy down

Above assumes you won't have anything in A1 which might match the data in A2
down

The required results will then be extracted in cols C and D, as before

 

[Mac]

This is it, excellent Max! Thank you again!

I wasn't sure from your original posting, that's why I mentioned:


If you want to extract a full list including 1st occurences, just change
amend the formula in the criteria col B to:

In B2:
=IF(A2="","",IF(COUNTIF(A:A,A2)>1,ROW(),""))
Copy down

Above assumes you won't have anything in A1 which might match the data in A2
down

The required results will then be extracted in cols C and D, as before

 

[Max]

welcome