how can I compare contents of columns in excel?

  • Thread starter Thread starter maht
  • Start date Start date
M

maht

I have two excel files viz. file-A and -B.
One of the columns of File-A contains chemical names which
is also contained in one of the File-B columns.There are a
lot more chemical names in the latter file than in the
former.
What I would like to do is to transfer additional
pertinent information from columns in File-B to new
columns in File-A on the basis of the chemical names found
in File-A. At the end of the exercise I would have a file
with the same number of chemicals but with more
information.
How can I do it? Your help would be much appreciated.
 
Take a look at the =vlookup() worksheet function.

If you move/copy the column B to column A in File B, you can use =vlookup() to
retrieve as many columns as you want:

=vlookup($a1,'[file b.xls]Sheet1'!$A:$G,2,false)

will take that value in A1 and compare it to the list in sheet1 column A of the
fileB workbook.

If it finds a match, it'll return the 2nd column (column B since I started in
column A).

Changing that 2 to 3 will bring back column C.

The false means that it has to be an exact match.

If no match is found, you'll see #n/a.

If you want to hide that you could use:

=if(iserror(vlookup($a1,'[file b.xls]Sheet1'!$A:$G,2,false)),"missing",
vlookup($a1,'[file b.xls]Sheet1'!$A:$G,2,false))
(all one cell)
 
-----Original Message-----
I have two excel files viz. file-A and -B.
One of the columns of File-A contains chemical names which
is also contained in one of the File-B columns.There are a
lot more chemical names in the latter file than in the
former.
What I would like to do is to transfer additional
pertinent information from columns in File-B to new
columns in File-A on the basis of the chemical names found
in File-A. At the end of the exercise I would have a file
with the same number of chemicals but with more
information.
How can I do it? Your help would be much appreciated.

.
This is the formula I use; it looks complicated but it
Click to expand...
isn't =if(isna(match(<spreadsheet A 1st chemical name
cell>,<Range of spreadsheet B 1st through last chemical
name >,0)),"",vlookup((<spreadsheet A 1st chemical name
cell>,<spreadsheet b chemical name column through end of
data wanted column>,<column #of data desired>))

If in Spreadsheet A you have Chemical name in A1 through
A10 and in Spreadsheet B you have chemical name in C1
through C50 and the chemical data in cells F1 through F50
your formula would look like this.

=if(isna(match(A1,spreadsheetB!$C$1:$C$50,0)),"",vlookup
(A1,spreadsheetB!$C$1:$F$1:$F$50,4))

Put this formula in the first cell of an empty column in
Spreadsheet A and copy it down.

Two things you must remember for this to work.

1. Spreadsheet B (the feeder spreadsheet) must be in
alpha/numerical order by the column you are comparing
(chemical names)
2. The ranges must be absolute (have $ signs before
both the column letters and row numbers ($C$1:$C$50)
 
Back
Top