Updating data

  • Thread starter Thread starter marka
  • Start date Start date
M

marka

I have an existing list of contact data in a spreadsheet. This dat
includes email addresses and many more fields. Some of the emai
addresses were out of date and have now been corrected in a seperat
sheet which only contains the persons name , first name and surname i
seperate columns. I have over 2000 updates to add. How can I do thi
wothout having to merge the two sheets by first name and surnmae an
then update changed email addresses? Apologies, a bit long winded but
am desperate for a suggestio
 
Hi
give the following a try:
Assumptions:
- your master sheet (called 'master') has several columns with all your
(old) data. Columns are:
-> col. A: name
-> col. B: first name
-> col. C: email
-> and other columns with data
- your second sheet (called 'update') only contains the following
columns
-> col. A: name
-> col. B: first name
-> col. C: email
- in both sheets row 1 is a heading row

Now try the following:
- in your first sheet insert a helper column for storing the new email
address (lets say insert a new column D)
- enter the following formula in D2 (the first data row) as array
formula (with CTRL+SHIFT+ENTER):
=IF(ISNA(MATCH(1,('update'!$A$2:$A$2500=A2)*('update'!$B$2:$B$2500=B2),
0)),C2,INDEX('update'!$C$2:$C$2500,MATCH(1,('update'!$A$2:$A$2500=A2)*(
'update'!$B$2:$B$2500=B2),0)))
- copy this formula down for all rows (it will show either the updated
email or the old one if no update exists)
- after this copy columns D, goto 'Edit - Paste Special' and choose
'Values' to insert only the contents again
- after this you may delete column C with the old email address

Note: Do this on a backup copy of your file!
 
Back
Top