Extracting duplicates with row ref's to a list
 

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?

Extracting duplicates with row ref's to a list
 

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?

Extracting duplicates with row ref's to a list
 

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?

Extracting duplicates with row ref's to a list
 

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

Extracting duplicates with row ref's to a list
 

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

Extracting duplicates with row ref's to a list
 

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!

Extracting duplicates with row ref's to a list
 

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" wrote:

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!

Extracting duplicates with row ref's to a list
 

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

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

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mac" wrote in message
...
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..:-) )

Extracting duplicates with row ref's to a list
 

This is it, excellent Max! Thank you again!

"Max" wrote:

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

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

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
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mac" wrote in message
...
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..:-) )

Extracting duplicates with row ref's to a list
 

welcome
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Mac" wrote in message
...
This is it, excellent Max! Thank you again!