Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-05-2012, 04:02 AM
shabbaranks shabbaranks is offline Replace contents form 1 workbook with another based on simular data Windows 7 64bit Replace contents form 1 workbook with another based on simular data Office 2007
Advanced Beginner
Replace contents form 1 workbook with another based on simular data
 
Join Date: Mar 2011
Posts: 89
shabbaranks is on a distinguished road
Default Replace contents form 1 workbook with another based on simular data

Hi all,

Ive attached a sample to "try" and explain what I am doing. Basically what I would like to achieve is to compare 2 spreadsheets within different workbooks which hold the same text within certain columns but different values in other columns and replace the data in say workbook 1 with whats in workbook 2.



So based on the attached, workbook 1 has simular data in row 2 except for columns B and F (TEST 2 and TEST 6) how can I get the value from columns B and F based on the matching value of column A and place it into Book 1?

Thanks!!
Attached Files
File Type: xlsx Book1.xlsx (8.3 KB, 7 views)
File Type: xlsx Book2.xlsx (8.3 KB, 7 views)
Reply With Quote
  #2  
Old 12-05-2012, 11:11 PM
macropod's Avatar
macropod macropod is offline Replace contents form 1 workbook with another based on simular data Windows 7 64bit Replace contents form 1 workbook with another based on simular data Office 2010 32bit
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
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
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to merge two columns & replace contents of cells conditionally? mag Excel 3 10-24-2012 01:07 PM
Range(Cell1,Cell2) Error on another workbook controlling some other workbook? tinfanide Excel Programming 1 02-09-2012 04:08 PM
Replace contents form 1 workbook with another based on simular data macro to transfer data from one workbook to another workbook virsojour Excel Programming 5 02-01-2011 08:58 PM
Replace contents form 1 workbook with another based on simular data How do I merge data from one sheet in a workbook out into multiple sheets nolesca Excel 4 06-07-2010 08:13 AM
Is there a way to do this? (automatically enter text based on form data) TIKKI555 Word 0 05-26-2010 09:21 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:35 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft