Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
check for duplicates | Excel Discussion (Misc queries) | |||
Copy and move check box (check boxes) with new cell link? | Excel Worksheet Functions | |||
Find Duplicates and Move to New Worksheet | Excel Worksheet Functions | |||
check for duplicates | Excel Worksheet Functions | |||
Check for duplicates | Excel Worksheet Functions |