Move worksheet data to another based on matching values

leigh_ashlin

Board Regular
Joined
Feb 9, 2005
Messages
74
Hi,

Is there a particular function that somebody can point me to, or a particular vba that I can use for the following scenario?

2 MS Excel workbooks and worksheets:
- 'primarydata.xls' with worksheet called 'legacy'
- 'secondarydata.xls' with worksheet called 'existing'

Around 2000 rows in each worksheet with a common column called 'AccountNumber' - there is a 95% match between the records in the 'AccountNumber' column of each worksheet.

There are 2 columns of data 'existing' worksheet in the 'secondarydata.xls' file called 'Description' & 'Balance' that need to come into the 'legacy' worksheet of the 'primarydata.xls' file. The data from the columns needs to be ordered in the 'legacy' worksheet according to the matching 'AccountNumber' of each file.

The exceptions (or values tht did not match) from the 'existing'worksheet in the 'AccountNumber' file need to be listed in a new worksheet.

--Is there a combination of functions that I can use to execute this task?

Thanks
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
2 suggestions for what they are worth

1. why two workbooks. why not two sheets in one workbook

2. I think you have to use index-match formulas

of course you can write a vba for this.

venkat
 

leigh_ashlin

Board Regular
Joined
Feb 9, 2005
Messages
74
Hi thanks for that.

Can you tell me which are the index-match formulas that I can use so that I can try and work through task, or give me an example of one?

Thanks
 

venkat1926

Well-known Member
Joined
Aug 21, 2005
Messages
4,824
see the sample sheet below and the comments therein
see help for vlooku and also for index and match
Book1
ABCDE
1ITEMDATA
2a1
3s2
4d3
5f4
6g5
7
8vlookup formula
9a1
10the ref value(a) should be in first column in
11the table .
12but if it is not if you want to find
13item corresponding to data 1
14then
15
16a
17
18see formula in B9 and A16
Sheet1
 

Forum statistics

Threads
1,137,195
Messages
5,680,095
Members
419,880
Latest member
suarezprado

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top