Find matching values

[chris]

I regularly have to compare two spreadsheets of account names to find the
matching values. Each spreadsheet has account names in one columns, but then
other columns will have different info, which I have to merge together if
there are matches.

I usually line up the columns and then color one set red and them put them
into the same worksheet and sort by name and scan visually for matches, then
do a lot of cutting and pasting...

Question #1 - Is there a way to find all the matching values in one column?
Say column A has two instances of Wal-Mart, how can I locate them in 1,000
lines? Is there a way to move them, and their attendant data into another
column or table once I do?

Thanks!

 

[Luke M]

If your second spreadshet has unique data (each account name has one row) you
could use vlookup to pull the data into the first.
example to get data from 4th column of 2nd spreadsheet:
=VLOOKUP(A2,Sheet2!A1:F100,4,false)
and change the column number accordingly.

Another idea is to use the countif function to check if there are entires on
sheet2.
=if(COUNTIF(Sheet2!A1:A100,A2)>0,"Match found", "no match")
And that would at least give you a quicker way to find matches.

 

[chris]

thanks Luke!

What if I just put them together and search one column? What would the
function for that look like (again, I am searching for many pairs of matches
which I don't know the value of ahead of time)

Thanks,

Chris