merge 2 spreadsheets info to 1

[JulesVern]

Good day everyone ,
I hope someone can assist me
I am trying to figure out how to ie:
take 2 spreadsheets,
and merge the like rows of information only on both of the spreadsheets , into a new spreadsheet.
If you need any details please let me know.

appreciated,Jules

[broro183]

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...