Macro - to copy duplicate rows to another sheet

[[email protected]]

Hello,

I have unsorted data (and needs to stay unsorted, so no autofilters)
that I want to copy all duplicate rows into another sheet based on a
column value. Example:

A B C ...
1 x a a
2 z b b
3 x c c
4 y d d
5 z e e
6 r f f
7 x g g
....

Using column A as criteria for duplicates. There are 3 instances of x
and 2 instances of z that will need to be copied. Another worksheet
would then contain:

A B C
1 x a a
2 x c c
3 x g g
4 z b b
5 z e e


I have been looking at other macros that are prevalent in this
newsgroup that DELETE duplicate rows. However, I do not want to delete
anything. Since I do not know the # of times a particular value in the
column would be duplicated, I'm not sure a 2 FOR/LOOP type statements
ala bubble-sort type method would work?

Thanks,
-jzk

[Don Guillett]

You don't have to sort to use autofilter.

--
Don Guillett
SalesAid Software
[email protected]
<[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> I have unsorted data (and needs to stay unsorted, so no autofilters)
> that I want to copy all duplicate rows into another sheet based on a
> column value. Example:
>
> A B C ...
> 1 x a a
> 2 z b b
> 3 x c c
> 4 y d d
> 5 z e e
> 6 r f f
> 7 x g g
> ...
>
> Using column A as criteria for duplicates. There are 3 instances of x
> and 2 instances of z that will need to be copied. Another worksheet
> would then contain:
>
> A B C
> 1 x a a
> 2 x c c
> 3 x g g
> 4 z b b
> 5 z e e
>
>
> I have been looking at other macros that are prevalent in this
> newsgroup that DELETE duplicate rows. However, I do not want to delete
> anything. Since I do not know the # of times a particular value in the
> column would be duplicated, I'm not sure a 2 FOR/LOOP type statements
> ala bubble-sort type method would work?
>
> Thanks,
> -jzk
>

[Max]

Perhaps a formulas approach would also work / suffice ?

Assume data below is in Sheet1

> A B C ...
> 1 x a a
> 2 z b b
> 3 x c c
> 4 y d d
> 5 z e e
> 6 r f f
> 7 x g g
> ...

(with col A as the key criteria col)

Use an empty col to the right, say, col E?

Put in E1:
=IF(A1="","",IF(COUNTIF(A:A,A1)>1,CODE(LEFT(TRIM(A1),1))+ROW()/10^10,""))

Copy E1 down to say, E100, to cover the max expected data range
(can copy down ahead of data input)

In Sheet2
----------
Put in A1:
=IF(ISERROR(SMALL(Sheet1!$E:$E,ROWS($A$1:A1))),"",INDEX(Sheet1!A:A,MATCH(SMA
LL(Sheet1!$E:$E,ROWS($A$1:A1)),Sheet1!$E:$E,0)))

Copy A1 across to C1, fill down to C100
(cover the same range as in Sheet1)

Sheet2 will return the desired results from Sheet1, i.e. only duplicate rows
will appear, "sorted" in relative alpha sequence (e.g. all the "x"'s and
"z"'s will come together) & bunched at the top

For the sample data in Sheet1, you'll get:

> A B C
> 1 x a a
> 2 x c c
> 3 x g g
> 4 z b b
> 5 z e e
(rest are "blank" rows)

Adapt to suit ..

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
<[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> I have unsorted data (and needs to stay unsorted, so no autofilters)
> that I want to copy all duplicate rows into another sheet based on a
> column value. Example:
>
> A B C ...
> 1 x a a
> 2 z b b
> 3 x c c
> 4 y d d
> 5 z e e
> 6 r f f
> 7 x g g
> ...
>
> Using column A as criteria for duplicates. There are 3 instances of x
> and 2 instances of z that will need to be copied. Another worksheet
> would then contain:
>
> A B C
> 1 x a a
> 2 x c c
> 3 x g g
> 4 z b b
> 5 z e e
>
>
> I have been looking at other macros that are prevalent in this
> newsgroup that DELETE duplicate rows. However, I do not want to delete
> anything. Since I do not know the # of times a particular value in the
> column would be duplicated, I'm not sure a 2 FOR/LOOP type statements
> ala bubble-sort type method would work?
>
> Thanks,
> -jzk
>