ExcelBanter - merge 2 spreadsheets info to 1

hi Jules,

You haven't really given any details that people can base any solutions
on to help you solve your problem.
For example, do you want the new spreadsheet (Merged) to have the "like
data" from each of the original spreadsheets (Ori1 & Ori2) in rows
underneath each other or rearranged side by side across the columns of
the same row?

However, I'll give it a go...
NB: file & sheet names & ranges will all need to be changed as
appropriate.

1) You need to identify "like rows" or duplicates which appear in both
spreadsheets (check out the link below to Chip's page). I would do this
by creating a helper column to the right of your data (in Ori1) &
entering a formula similar to the following:
=VLOOKUP(Sheet1!A1,[Ori2.xls]Sheet1!$A$1:$F$2,1,FALSE)

2) Filter the added column to be "does not equal" "#N/A".

3) Copy the filtered rows of data into the new workbook, Merged, (may
need to be paste special'd as values to prevent any links being
upset).

Now if you want the "like rows" next to each other in the Merged file
repeat steps 1-3 in the second workbook & use Data - Sort.
Or if you want the data from "like rows" in Ori2 pasted in the same row
as the Ori1 data in the Merged file:
A) Go to the first empty column in the new file & type in:
=VLOOKUP(Sheet1!$A1,[Ori1.xls]Sheet1!$A$1:$F$2,COLUMN(Sheet1!G1)-4,FALSE)

With the part, "COLUMN(Sheet1!G1)-4", the "G" is the empty column
letter that the formulae is being entered into and the "4" in the "-4"
is equal to G-2 (ie G is the 6th letter of the alphabet).

B) Copy this down as many rows & across as many columns as needed.

C) Once the results have been found I'd copy & paste as values to speed
up your workbook & minimise the file size. I usually keep a copy of the
formula in a comment on the header row in case it is needed again when
values ie the rate or the quantity change.

The following links may give you some other ideas to try:
http://www.cpearson.com/excel/duplicat.htm
http://excelforum.com/showthread.php?t=534484

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

--
broro183
------------------------------------------------------------------------
broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068
View this thread: http://www.excelforum.com/showthread...hreadid=535259