PLEASE HELP: Excel~ Copy/pasting

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 
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?
 

OFFICESPIKE

Thread Starter
Joined
Jan 9, 2006
Messages
10
bomb #21 said:
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?
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.
 
Joined
Jul 1, 2005
Messages
8,546
OFFICESPIKE said:
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.
What method exactly are you currently using for a duplicate to be noticed when entered manually (i.e. not copied & pasted)?
 
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.
 
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.
 

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.
 
Joined
Jul 1, 2005
Messages
8,546
OFFICESPIKE said:
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.
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
 

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?
 
Joined
Jul 25, 2004
Messages
5,458
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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Top