Excel 2003 – how to dock\fixate a row\column

What you are looking for is conditional formatting.
Here's a quick tutorial.

First, you need to make a sheet that contains all of the values that you want included from the A columns of each original sheet. This is an easy copy-and-paste operation, which you will want to clean up using "Remove Duplicates".

Let's presume you'll put this in column A of your new sheet, as you've described.

Then, you need to have the new sheet pull the column B values from your originals into columns B and C of the new. VLOOKUP can be made to work between different spreadsheets, even if they exist in completely separate files. I'll give some examples below.

For all of the below examples, I'm presuming the following: - Row 1 is for headers - Column A in the new sheet is a merge of the A columns from the originals - Column B in the new sheet is intended to include items from column B of File 1 - Column C in the new sheet is intended to include items from column B of File 2

For naming sheets/files, I'll use the following conventions: - "Result" will be the name of the spreadsheet you're looking to create - "Source1" is data from "File 1" as described in your question - "Source2" is data from "File 2" as described in your question

The following VLOOKUP is for cell B2 in "Result". This formula presumes your "Source" files are separate workbooks stored in your desktop folder, your username is "Me", and you're running Windows Vista or newer. This also presumes you have not renamed any of the sheets in the source workbooks (keeping defaults of Sheet1, Sheet2, etc.).

=VLOOKUP(A2,'C:\Users\Me\Desktop\[Source1.xlsx]Sheet1'!A:B,2,FALSE)

For cell C2 in "Result", you use the exact same formula but change [Source1.xlsx] to [Source2.xlsx]. To finish off the sheet, copy B2 and C2 all the way down their respective columns.

If you want to later break the relationships between the files so that your "Result" sheet can stand independent of the source sheets, just do a copy/paste of "Values Only" on columns A:C of that sheet.

Alternately, you could have all three sheets in one workbook. This makes for a bit of a cleaner formula since you don't need to specify the source file name. Using the naming convention stated above for the sheet names, the formula for B2 would be:

=VLOOKUP(A2,Source1!A:B,2,FALSE)

Again, the formula for C2 would be the same but replace Source1 with Source2. If you later want to remove the source sheets, you'll have to do a copy/paste of "Values Only" as described earlier in order to retain the data you want in "Result".

There is a small caveat to this. If VLOOKUP looks for data and does not find it, you'll get one of those ugly #N/A messages in the cell. You can get around this with IFERROR. Here's an example, using the last formula above as a base:

=IFERROR(VLOOKUP(A2,Source1!A:B,2,FALSE),"")

This essentially says, "If the VLOOKUP returns an error, make this cell's value blank. Otherwise, display the result of the VLOOKUP."

If you run into trouble, I suggest consulting the Help documents and/or Google for "Remove Duplicates", "VLOOKUP", "Paste Special", and/or "IFERROR" - whichever part you're having a problem with.

NOTE: I've tested these functions in Excel 2010, and have experience using them in Excel 2007 as well. I'm not sure if all of these features are available in Excel 2003 or not. I strongly recommend you upgrade to Office 2007 or later in the near-ish future. Support for Office 2003 will be ending in 2014.