Merging text cells from two worksheets with one shared column

[A Ford]

I have a worksheet called "master" that includes a column called
"identifier" which is an ID name. There are several occurrences of each
"identifier" value in the column. I have another worksheet called "dams" that
contains the same "identifier" column, but with only one occurrence of each
"identifier" value in the column. The only data in common between the two
worksheets is the "identifier" column. I would like to merge the two
worksheets by having excel match the "identifier" name in the two worksheets
and transfer the associated cells in each the row from the "dams" worksheet
to the "master" worksheet. This will result in an addition of several columns
of data to the "master" worksheet from the "dams" worksheet.

Seems that there must be a simple way to do this, but I have not been able
to figure it out (I am not too experienced in Excel). I tried the
"consolidate" feature, but that seems to be limited to numerical formulae,
and I just want to transfer text from each cell.

 

[JLatham]

With both workbooks open, you can actually use the VLOOKUP() formula to echo
the values from the 'dams' workbook into the 'master' workbook based on the
identifier column.

Caveats:
#1 to update, both books have to be open.
#2 sheets in 'dams' have to be set up to use VLOOKUP() which means the
identifier column would have to be in a column to the left of any information
you want to return from a matched row on that sheet.

If this is a one time deal, after setting up the VLOOKUP() getting all the
information to appear in the 'master' book, you could then select all of the
cells with the data in them and use Edit | Copy followed by Edit | Paste
Special w/[Values] selected which will convert the formulas to their value
and break the link between the two workbooks.

If you need to update 'dams' frequently, then you can leave the VLOOKUP()
formulas in place and just know that unless both workbooks are open, the data
may not be current, Excel will retain the last current information.

 

[Autumn]

Hi I know this is not my question but I have one for you since you are a MVP.
I need to merge 2 workbooks that have the same kind of data. I get a new
workbook with new updated information every quarter & I want to run the "old"
workbook against the "new" workbook to see if there are any duplicate names
so that I can get rid of them in the "new" list?

With both workbooks open, you can actually use the VLOOKUP() formula to echo
the values from the 'dams' workbook into the 'master' workbook based on the
identifier column.

Caveats:
#1 to update, both books have to be open.
#2 sheets in 'dams' have to be set up to use VLOOKUP() which means the
identifier column would have to be in a column to the left of any information
you want to return from a matched row on that sheet.

If this is a one time deal, after setting up the VLOOKUP() getting all the
information to appear in the 'master' book, you could then select all of the
cells with the data in them and use Edit | Copy followed by Edit | Paste
Special w/[Values] selected which will convert the formulas to their value
and break the link between the two workbooks.

If you need to update 'dams' frequently, then you can leave the VLOOKUP()
formulas in place and just know that unless both workbooks are open, the data
may not be current, Excel will retain the last current information.

I have a worksheet called "master" that includes a column called
"identifier" which is an ID name. There are several occurrences of each
"identifier" value in the column. I have another worksheet called "dams" that
contains the same "identifier" column, but with only one occurrence of each
"identifier" value in the column. The only data in common between the two
worksheets is the "identifier" column. I would like to merge the two
worksheets by having excel match the "identifier" name in the two worksheets
and transfer the associated cells in each the row from the "dams" worksheet
to the "master" worksheet. This will result in an addition of several columns
of data to the "master" worksheet from the "dams" worksheet.

Seems that there must be a simple way to do this, but I have not been able
to figure it out (I am not too experienced in Excel). I tried the
"consolidate" feature, but that seems to be limited to numerical formulae,
and I just want to transfer text from each cell.

 

[JLatham]

Here's a quick way using a formula. You'd need to put this into a cell in
the Quarterly Update workbook that is currently unused. For the example,
we'll presume the list of names in the 'master' book is in column A, and that
the names also appear in column A in the quarterly update (adjust column IDs
appropriately)

=COUNTIF([MasterBook.xls]MasterSheet!$A:$A,A2)
'fill' that formula all the way down the quarterly update sheet for as far
as you have names on rows. Where a name is new (doesn't appear in Master
list) it will return zero (0), for names that are in master list it will
return a count of the number of times it appears in the master list, be it 1
or 1001 - but it will be greater than zero.

After you've done that, return to the cell at the top of the column with the
formula in it and, from the menu, choose Data | Filter | AutoFilter
Open up the list that is created in that column and choose [Custom] and in
the dialog that appears, choose "is greater than" in the left column and
enter 0 (zero) in the right column. [OK] to close the dialog. Now all that
will be shown are rows where the count is greater than zero, which indicates
names that are in your master list. Choose all of those rows and use Edit |
Delete Row
All of the entries will be gone! Back up to your AutoFilter and either show
[All] or back to menu and uncheck the AutoFilter option and all that will be
left on the sheet are rows with new/unmatched names.
Now you can even delete the column with the formula in it, since you don't
need it any more.

Hi I know this is not my question but I have one for you since you are a MVP.
I need to merge 2 workbooks that have the same kind of data. I get a new
workbook with new updated information every quarter & I want to run the "old"
workbook against the "new" workbook to see if there are any duplicate names
so that I can get rid of them in the "new" list?

With both workbooks open, you can actually use the VLOOKUP() formula to echo
the values from the 'dams' workbook into the 'master' workbook based on the
identifier column.

Caveats:
#1 to update, both books have to be open.
#2 sheets in 'dams' have to be set up to use VLOOKUP() which means the
identifier column would have to be in a column to the left of any information
you want to return from a matched row on that sheet.

If this is a one time deal, after setting up the VLOOKUP() getting all the
information to appear in the 'master' book, you could then select all of the
cells with the data in them and use Edit | Copy followed by Edit | Paste
Special w/[Values] selected which will convert the formulas to their value
and break the link between the two workbooks.

If you need to update 'dams' frequently, then you can leave the VLOOKUP()
formulas in place and just know that unless both workbooks are open, the data
may not be current, Excel will retain the last current information.

I have a worksheet called "master" that includes a column called
"identifier" which is an ID name. There are several occurrences of each
"identifier" value in the column. I have another worksheet called "dams" that
contains the same "identifier" column, but with only one occurrence of each
"identifier" value in the column. The only data in common between the two
worksheets is the "identifier" column. I would like to merge the two
worksheets by having excel match the "identifier" name in the two worksheets
and transfer the associated cells in each the row from the "dams" worksheet
to the "master" worksheet. This will result in an addition of several columns
of data to the "master" worksheet from the "dams" worksheet.

Seems that there must be a simple way to do this, but I have not been able
to figure it out (I am not too experienced in Excel). I tried the
"consolidate" feature, but that seems to be limited to numerical formulae,
and I just want to transfer text from each cell.