Thread: Replace contents form 1 workbook with another based on simular data
View Single Post
 
Old 12-05-2012, 11:11 PM
macropod's Avatar
macropod macropod is offline Windows 7 64bit Office 2010 32bit
Administrator
  
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 22,467
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default
Assuming all the records in Book1 are found in Book2, you can do this with an array formula. For example, with both workbooks open, the following will update column B:
=INDEX([Book2.xlsx]Sheet1!$B1:$B10,MATCH(A2&C2&D2&E2,[Book2.xlsx]Sheet1!$A1:$A10&[Book2.xlsx]Sheet1!$C1:$C10&[Book2.xlsx]Sheet1!$D1:$D10&[Book2.xlsx]Sheet1!$E1:$E10,0))
For column F, change '$B1:$B10' to '$F1:$F10'. Also, assuming you have more than 10 rows, change all the 10s in the formulae to whatever is the maximum nuumber of rows in Book2.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote