Move worksheet data to another based on matching values

[leigh_ashlin]

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

 

[venkat1926]

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]

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]

see the sample sheet below and the comments therein
see help for vlooku and also for index and match

Book1
	A	B	C	D	E
1	ITEM	DATA
2	a	1
3	s	2
4	d	3
5	f	4
6	g	5
7
8	vlookup formula
9	a	1
10	the ref value(a) should be in first column in
11	the table .
12	but if it is not if you want to find
13	item corresponding to data 1
14	then
15
16	a
17
18	see formula in B9 and A16
Sheet1