1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

PLEASE HELP: Excel~ Copy/pasting

Discussion in 'Business Applications' started by OFFICESPIKE, Jan 9, 2006.

Thread Status:
Not open for further replies.
Advertisement
  1. OFFICESPIKE

    OFFICESPIKE Thread Starter

    Joined:
    Jan 9, 2006
    Messages:
    10
    :confused:
    HELLO.
    I am in charge of loading students data into a spreadsheet.
    I need to avoid from duplicating the same students into my file.
    I have set up a way to find duplicates by entering in their telephone number.
    This is accurate when loading students one by one.

    Here is my problem:
    It would save me days of work if I were able to merge (copy/paste) one
    worksheet into the other. Is there a way I could copy/paste one student
    or multiple students into my database AND still having it detect duplicates?
    ***if it has to be one by one, it's still better than my current method.
    ***if i can paste in multiples it would be best.

    *although I detect by phone number, here are the fields I have to type for
    each student: phone 1, first name, last name, address, city, state, zip, phone 2,
    phone 3, birthdate, emergency cntct, relationship, emer address, emer city, emer state,
    emer zip, date entered, stud grade, notes..... etc. ***see why copy/paste is needed.
    lol.
    thank you.

    OFFICESPIKE
     
  2. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Welcome to TSG, OFFICESPIKE.

    Are the "students to be loaded" in another sheet in the same workbook, or in a different workbook (or workbooks)?

    You could use a simple MATCH formula to "flag" which records to be loaded are already in your database, then sort by the column containing the formula so that the flagged (and unflagged) records get grouped together, and then only copy & paste the unflagged records. Does this sound close?
     
  3. OFFICESPIKE

    OFFICESPIKE Thread Starter

    Joined:
    Jan 9, 2006
    Messages:
    10
    THANKS FOR THE WELCOME.

    The "students to be loaded" are in identical workbooks. They simply need to be moved over into ONE database.
    One thing I have noticed is that when I copy/paste a number it
    dosn't get noticed as a duplicate. This is the major issue.
     
  4. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    What method exactly are you currently using for a duplicate to be noticed when entered manually (i.e. not copied & pasted)?
     
  5. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Can you/are you allowed to download and install an add-in?
     
  6. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    I suggest you download ASAP from http://www.asap-utilities.com/. You can use it to "empty duplicates in selection" (a single column), then use Edit -- Go To -- Special -- Blanks to delete all rows where the column has been blanked.
     
  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Another way (if you can't d-load an add-in) is to use a conditional formatting formula to highlight values in the "Phone" field that are already present "higher up".

    Assuming row1 is a header and the "Phone" field is column B, the formula:

    =MATCH(B1,INDIRECT("B1:B"&ROW()-1),0)>0

    applied to all of column B will do the highlighting. Let me know if you need a screenshot to make this clear.
     
  8. OFFICESPIKE

    OFFICESPIKE Thread Starter

    Joined:
    Jan 9, 2006
    Messages:
    10
    I WILL CHECK THIS OUT RIGHT NOW.
    I am not sure if i am "allowed" but do not, want to ask... perhaps
    figuring this out on my own will make my boss happy. haha
    As for the method on how exactly a duplicate is noticed- All I am sure of, is
    that we have a formula (i guess it would be called) that detects when the same
    phone number has been entered more than once in phone1, phone2,
    or phone3 columns.
    Aty first, it would only notice if it were in the same column, we have recently
    fixed it to notice if the number has been entered in any column.

    The biggest reason I am needing help with the copy/paste is:
    If I take work home, I would like to merge into data base.
    If a co-worker works on data base, would like to merge our work without duplicates!
    =)
    thanks again.
     
  9. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    If there are 3 phone columns, it would be helpful if you could post the formula so we can dissect it. It would be even better (i.e. perfect) if you could post a sample file (just a few copied records, with any private data removed).

    Rgds,
    Andy
     
  10. OFFICESPIKE

    OFFICESPIKE Thread Starter

    Joined:
    Jan 9, 2006
    Messages:
    10
    On It, Now.
     
  11. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    I would recommend brettdj's Duplicate Master...

    http://members.iinet.net.au/~brettdj/

    It's excellent work.

    Also, if you copy/paste your data into one sheet, you can use Data | AdvancedFilter | Unique values..

    HTH
     
  12. OFFICESPIKE

    OFFICESPIKE Thread Starter

    Joined:
    Jan 9, 2006
    Messages:
    10
    Here Is An Example Of My Worksheet.
    Thank U !!
     

    Attached Files:

  13. OFFICESPIKE

    OFFICESPIKE Thread Starter

    Joined:
    Jan 9, 2006
    Messages:
    10
    just had a meeting , i think we are changing to ACCESS.....
    this sound like a good idea?
     
  14. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Even if you have duplicates in that data (I'm assuming by any of the three phone numbers) you could use a formula such as this in column S ..

    =(COUNTIF($A$2:A2,A2)>1)+(COUNTIF($J$2:J2,A2)>1)+(COUNTIF($K$2:K2,A2)>1)

    Then ..

    Go to Sheet2
    Select Data | Filter | AdvancedFilter
    Select Copy to Another Location
    Set your List Range as data (including headers) except for column S with formula
    Set your Criteria Range as S1:S and your last row
    Set your Copy To as Sheet2!A1 (just click A1 since you're on Sheet2)
    Ensure you check Unique Records Only
    Click Ok.

    You should now have a list without duplicates.
     
  15. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Yes, sounds like a good idea.
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/432555

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice