Merging Excel worksheets?

[ɹəuəllıʍ ʇɐb]

Excel is not my strong point, and even reading Excel Help topics gives me a headache! So let me ask the question here; I know there are some people here who are good at Excel.

I have two Excel documents - very simple tables, no formulas etc.

key1 | information data A
key2 | information data B
key4 | information data C

key1 | information data D
key2 | information data E
key3 | information data F

I would like to merge them somehow so that I get a result sheet like this

key1 | information data A | information data D
key2 | information data B | information data E
key3 |					| information data F
key4 | information data C |

Any idea

• if this is possible?
  
• if so, how?
  

[Irene]

It looks as though the values key1, key2 etc in the left hand column of the first spreadsheet have missing consecutive entries; i.e. key1, key2, key4, unlike the second which is key1, key2, key3.

Would this be true?

[Alan2273]

This answer is from Chris.

Highlight [i assume] Column B in the second worksheet and copy it.

Then in the first worksheet highlight column C and paste it.

[Irene]

This answer is from Chris. Highlight [i assume] Column B in the second worksheet and copy it. Then in the first worksheet highlight column C and paste it.

This answer would be fine if column 'A' exactly mirrors column A of spreadsheet 1 amd spreadsheet 2.

If one has values 1,2,4,6,7 and two has values 1,2,3,4,5,6,7, copy and paste will not work.

You would find that value 4 in column 1, would end up with values from column 3 in spreadsheet 2!!

[ɹəuəllıʍ ʇɐb]

Yes, Irene, your observation is right: the number of rows in the two worksheets are different. And both worksheet may have missing A columns.

A manual merge of the two is near impossible, as both documents will have between 6,000 and 10,000 rows.

I will eagerly wait for more ideas, but meanwhile I will try to do something with Access - or even SQL Server.

[Irene]

Pat, I believe that you would require someone to write a small Utility Program / Macro which would identify a missing value in column A in either of your spreadsheets as they merge.

Unfortunately, I am not quite up to that standard, but I am sure it would be a relatively simple matter for someone with a sufficient level of experience. :)

[D4\/!d]

I suppose opening the two sheets and copy/pasting from smaller to larger doesn't work, coz of the number of rows.

Maybe start a third sheet that has the features and layout of both sheets. Then copy sheet 1 into the new sheet3, followed by sheet 2 into sheet 3.

Try this fella:

http://www.mrexcel.com/questions.shtml

[ɹəuəllıʍ ʇɐb]

Thanks for the suggestion, Irene! Actually we have one lady at the office who is quite experienced with Excel, maybe I should ask her if she can write a macro.

Thanks also, D4\/!d, I will check out the link.

Meanwhile, since I am the last at the office, I will check out if I can import the sheet 1 into Access, and what the possibility of a merge or join is. (Haven't created sheet 2 yet - missing data).

[ɹəuəllıʍ ʇɐb]

Update: I have played a little with Microsoft Access (for the first time in my life!); I have imported both Excel sheets as individual tables, then attempted to join them.

Unfortunately Access only supports three join types

1. INNER JOIN: which results in only rows with key1, key2
   
2. LEFT OUTER JOIN: result contains key1, key2, key4
   
3. RIGHT OUTER JOIN: result has key1, key2, key3
   

What I really need is a FULL OUTER JOIN that gives me all keys 1-4.

I will check if Microsoft SQL Server support the FULL OUTER JOIN.