Check for duplicates and move to another worksheet
 

Not very automated but here's a suggestion
Assume your data is in A1:F100
In G1 enter =C1&"*"&D1 and copy down the column by double clicking the fill
handle
In H1 enter =MIN(COUNTIF($G$1:$G$100,G1)-1,1) and copy down
If both C and D occur with same values in more than 1 row you get a value of
1
Now temporarily sort (you have the sequence number to 'unsort') by H or use
Data|Filter to let you get all rows with H value of 1
Copy and paste to new worksheet
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Reggie" wrote in message
...
Hi I have a worksheet that has 6 columns and variable rows. Each column
has
numbers for the values. what i need is excel to check column 3 and 4 and
compare it with the rest of the rows if duplicates are found move the all
duplicates including the original to another worksheet. Start with row 1
then
continue the whole process

example:
Worksheet 1
sequence trace amount check trancode pck trace
1 4444 .10 1111 45 85
<----- Original
2 4521 1.15 859 54 75
3 8631 .10 1111 45 56
<----- Duplicate


Worksheet 2
1 4444 .10 1111 45 85
<----- moved to worksheet 2 for worksheet 1

3 8631 .10 1111 45 56
<----- moved to worksheet 2 for worksheet 1

thanks in advance!

Check for duplicates and move to another worksheet
 

I am sure someone could code this in VBA but is it necessary?
I would keep a 'dummy' file handy with the formulas so I could copy and
paste them to the daily work file.
Doing this followed by a sort and a quick cut-and-paste should take less
than 5 mins

Maybe do a copy-and paste first, then apply formulas, sort and cut out
unneeded rows
In this way the original data is left untouched. Saves a few steps.
all the best
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Reggie" wrote in message
...
Thanks for your quick response. Is there a way to automate this process?
The
reason i need it automated is because this worksheet would be used
dialy...

"Bernard Liengme" wrote:

Not very automated but here's a suggestion
Assume your data is in A1:F100
In G1 enter =C1&"*"&D1 and copy down the column by double clicking the
fill
handle
In H1 enter =MIN(COUNTIF($G$1:$G$100,G1)-1,1) and copy down
If both C and D occur with same values in more than 1 row you get a value
of
1
Now temporarily sort (you have the sequence number to 'unsort') by H or
use
Data|Filter to let you get all rows with H value of 1
Copy and paste to new worksheet
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Reggie" wrote in message
...
Hi I have a worksheet that has 6 columns and variable rows. Each
column
has
numbers for the values. what i need is excel to check column 3 and 4
and
compare it with the rest of the rows if duplicates are found move the
all
duplicates including the original to another worksheet. Start with row
1
then
continue the whole process

example:
Worksheet 1
sequence trace amount check trancode pck trace
1 4444 .10 1111 45
85
<----- Original
2 4521 1.15 859 54
75
3 8631 .10 1111 45
56
<----- Duplicate


Worksheet 2
1 4444 .10 1111 45
85
<----- moved to worksheet 2 for worksheet 1

3 8631 .10 1111 45
56
<----- moved to worksheet 2 for worksheet 1

thanks in advance!

Check for duplicates and move to another worksheet
 

No need to use VBA: You can use the CountIf() function to determine if it's
a double, put that in the seventh column. Insert another column before
column A and there perform something like: if it's a double determine the
maximum used in column A until now and add 1.

In the other sheet use VLookup and the ROW() function to copy the
values.....

Confused???

I've added an examplesheet to this message.

Goodluck,

Bennie (i'm not looking much into this group, so mail me directly at
bdijkslag at that warm mail service from microsoft... ;-) incase you have
any more questions.

"Reggie" wrote in message
...
Thank you for all your help on this........Could someone tell me how make
this possbile in vb?

"Bernard Liengme" wrote:

I am sure someone could code this in VBA but is it necessary?
I would keep a 'dummy' file handy with the formulas so I could copy and
paste them to the daily work file.
Doing this followed by a sort and a quick cut-and-paste should take less
than 5 mins

Maybe do a copy-and paste first, then apply formulas, sort and cut out
unneeded rows
In this way the original data is left untouched. Saves a few steps.
all the best
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Reggie" wrote in message
...
Thanks for your quick response. Is there a way to automate this
process?
The
reason i need it automated is because this worksheet would be used
dialy...

"Bernard Liengme" wrote:

Not very automated but here's a suggestion
Assume your data is in A1:F100
In G1 enter =C1&"*"&D1 and copy down the column by double clicking the
fill
handle
In H1 enter =MIN(COUNTIF($G$1:$G$100,G1)-1,1) and copy down
If both C and D occur with same values in more than 1 row you get a
value
of
1
Now temporarily sort (you have the sequence number to 'unsort') by H
or
use
Data|Filter to let you get all rows with H value of 1
Copy and paste to new worksheet
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Reggie" wrote in message
...
Hi I have a worksheet that has 6 columns and variable rows. Each
column
has
numbers for the values. what i need is excel to check column 3 and 4
and
compare it with the rest of the rows if duplicates are found move
the
all
duplicates including the original to another worksheet. Start with
row
1
then
continue the whole process

example:
Worksheet 1
sequence trace amount check trancode pck
trace
1 4444 .10 1111 45
85
<----- Original
2 4521 1.15 859 54
75
3 8631 .10 1111 45
56
<----- Duplicate


Worksheet 2
1 4444 .10 1111 45
85
<----- moved to worksheet 2 for worksheet 1

3 8631 .10 1111 45
56
<----- moved to worksheet 2 for worksheet 1

thanks in advance!