Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2009
    Posts
    2

    Unanswered: Find Duplicate; Copy; Paste

    Hi,

    I've been searching most of the morning for a solution to this (in hopes of making my life easier).

    The situation:

    I have two files that I merge then manually check for duplicate Customer Numbers. If I find a duplicate, I copy their Keycode from the old to the new. If there are multiple new records with the same Customer Number, I copy the Keycodeto all of the duplicates.

    The file structure:

    A = Keycode
    E = Customer Number

    Additional Info:

    I have the file sorted by Customer Number so they line up and the new record(s) is above the old record.

    Please help! The quicker the better! And thank you so much in advance!

    EDIT: Picture

    Last edited by kudasauce; 12-02-09 at 17:04. Reason: Add image

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    I can't see any spreadsheet pictures in your post (there is only an advertising banner) so I'm going to improvise an example.
    Btw - rather than linking an image to a 3rd party site, it would be much better to attach a workbook which we can experiment with.

    Anyway...

    The fact that they are sorted in Customer Number order, with the new above the old, makes life much easier. Say this is your starting point:
    Code:
    A				E			
    Keycode				Customer Number		
    123				12345			
    234				12345			
    345				23456			
    456				45678			
    456				45678			
    678				45678
    Row 1 contains the column headers "Keycode" and "Customer Number". The column letters A and E are only there as indicators, they are not on the worksheet itself.



    Since you want to copy the old keycode to the new keycode, you could use an empty column to determine the correct keycode. Say column H is empty, put in this formula from the first record and copy downwards:
    =IF(E2=E3,H3,A2)

    It would look like this:
    Code:
    A				E			H
    Keycode				Customer Number		Revised Keycode
    123				12345			234
    234				12345			234
    345				23456			345
    456				45678			678
    456				45678			678
    678				45678			678
    Then you can simply copy and pastespecial values from column H into column A and clear the contents from column H. The final result would be:
    Code:
    A				E			
    Keycode				Customer Number		
    234				12345			
    234				12345			
    345				23456			
    678				45678			
    678				45678			
    678				45678
    So that's a pretty straightforward way of achieving this. Of course you could automate the process using VBA, but if you are not familiar with VBA then I think that this is a more appropriate way for you to do it.

    Hope that helps...

  3. #3
    Join Date
    Dec 2009
    Posts
    2
    Quote Originally Posted by Colin_L View Post
    Hi,

    I can't see any spreadsheet pictures in your post (there is only an advertising banner) so I'm going to improvise an example.
    Btw - rather than linking an image to a 3rd party site, it would be much better to attach a workbook which we can experiment with.

    Anyway...

    The fact that they are sorted in Customer Number order, with the new above the old, makes life much easier. Say this is your starting point:
    Code:
    A				E			
    Keycode				Customer Number		
    123				12345			
    234				12345			
    345				23456			
    456				45678			
    456				45678			
    678				45678
    Row 1 contains the column headers "Keycode" and "Customer Number". The column letters A and E are only there as indicators, they are not on the worksheet itself.



    Since you want to copy the old keycode to the new keycode, you could use an empty column to determine the correct keycode. Say column H is empty, put in this formula from the first record and copy downwards:
    =IF(E2=E3,H3,A2)

    It would look like this:
    Code:
    A				E			H
    Keycode				Customer Number		Revised Keycode
    123				12345			234
    234				12345			234
    345				23456			345
    456				45678			678
    456				45678			678
    678				45678			678
    Then you can simply copy and pastespecial values from column H into column A and clear the contents from column H. The final result would be:
    Code:
    A				E			
    Keycode				Customer Number		
    234				12345			
    234				12345			
    345				23456			
    678				45678			
    678				45678			
    678				45678
    So that's a pretty straightforward way of achieving this. Of course you could automate the process using VBA, but if you are not familiar with VBA then I think that this is a more appropriate way for you to do it.

    Hope that helps...
    Thanks for the reply and I'll re-upload the picture soon.

    However, my list contains over 32,000 records so the most appropriate way is to use a macro to copy the old keycode to the new (empty) keycode cell.

    For a quick example:

    Code:
         A                   B
    Keycode         Cust. Num.
                    am608
     909068         am608
     909068         br777
                    ct489
                    ct489
     909042         ct489
    And I need it to look like:

    Code:
         A                   B
    Keycode         Cust. Num.
     909068            am608
     909068            am608
     909068            br777
     909042            ct489
     909042            ct489
     909042            ct489
    And I need to do that for thousands of records. Currently I highlight the "new" list and merge the two files, then go through it manually, copying and pasting.

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,
    However, my list contains over 32,000 records so the most appropriate way is to use a macro to copy the old keycode to the new (empty) keycode cell.
    The fact that there are 32,000+ rows does not mean that VBA is the most appropriate approach. If someone here writes the VBA code for you then what will you do if it stops producing the results you expect? I guess you wouldn't be able to maintain it? So my line of thinking is that VBA might just not be the most appropriate way for you to do this...?

    Now you have shown your example, isn't it just a case of filling in the blanks with the keycode from the row below? Adding a formula into a helper column, filling down the column and then copying and pasting values is a 2 second job, the logic of which is very similar to the improvised example I provided earlier. I don't see any issues with implementing it? Perhaps some problems may become clearer when you attach a workbook for us to see...?

    Hope that helps...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •