Merging 2 databases

**harrypotter1** · 11-02-2012, 09:33 AM

I want to merge 2 excel databases based on the first column (called ''ticker''). To make it complicated, some tickers have a slightly different format in the 2 files (for example Alza Corp is called "AZA" in the first sheet and "AZA.1" in the second sheet, these have to be merged). To make it even more complicated, the sheets have a different amount of rows and some companies that are in one sheet are not in the other and the other way around. I have pasted a small part of the total database in the attachment. The columns 'firm_id' and 'numblks' from sheet 1 have to end up next to the column 'Gind', as already done manually for ALZA CORP.

Who can help me?? I have already spent almost 2 days solving this, but become more and more desperate every minute..

**jake.masters** · 11-02-2012, 09:45 AM

I think you may have forgotten to attach your attachment. Post that up here and we can have a look.

**harrypotter1** · 11-02-2012, 09:56 AM

Thank you

**jake.masters** · 11-02-2012, 10:07 AM

I am not sure I understand what you want this to look like when you are finished. For example,

BEAM	          BEAM INC
BEAM.1	  SUMMIT TECHNOLOGY INC

In the first sheet, BEAM is its own ticker, while on the second sheet, there are both BEAM and BEAM.1. I would normally find and replace ".1", but it seems as though the data is different as they have different company names, ebitda, etc.

Which sheet has 'better' information. Using your example, would you rather have Alza Corp called ALZA, or ALZA.1, and are these two names interchangeable? Maybe if put together a quick 10 lines of what you would want your final product to look like, it would help us figure out the best way to get you there.

**harrypotter1** · 11-02-2012, 11:35 AM

The names are not interchangable as you show with the example of BEAM.
I have put the endresult as how it should be for all companies in sheet 3.

**jake.masters** · 11-02-2012, 01:26 PM

I apologize, that does not really answer my questions, and therefore I still do not know what you are looking for. To me, it looks like you have just copied the cells from sheet 2 where the ticket is ALZ.1 onto the third sheet. Ideally, I want to know how to merge the data and what you want it to look like, which tickers to use between the two sheets, and how the two sheets should be integrated into one.

If you were just going to copy the data, I would just insert columns so everything matches up and then copy, paste and sort. I am assuming this is not what you are looking for, though.

**harrypotter1** · 11-02-2012, 02:30 PM

I want to merge the data by using the tickers on sheet 2.
I have found this formula, but it is not correct because it does not take the year into account =IF.WRONG(VLOOKUP(A85925;Blockholder!$A$2:$F$302;3;0);"")

Thanks for helping