Help with Matching Text Fields - Then Moving the Matching Cells Side by Side

[andrewdgold]

I have an excel issue that if someone could help me figure out, would
save a lot of time.

Here is the situation: I currently have 2 databases exported together
into excel side by side. On the left side there is database 1 - a
huge column of company names (column A) with their account #s in an
adjacent column (B). On the right side of the worksheet there is
database 2 - a huge column of the same company names (however some of
these names have been named a bit differently than the first DB....yet
still the same companies are all there - column C) along with other
columns that deal with important information concerning each company
(columns D-H).

This is what I need to do....I need to go through the list of DB 2 and
find their corresponding company name + account # from the two DB 1
columns (I hope that made sense). In order to do this manually...I will
have to scroll down DB 1 looking for the right co name that is similar
to the one I am matching in DB 2. Once I have found a match
somewhere, I need to cut 2 cells (co. name and account # from DB 1)
then insert them side by side the similar account name in DB 2 so it
all lines up. So ultimately....I should have matching account names
with their account #s and other info sitting side by side instead of
all over the place.

Now this is what I want Excel to be able to do: perhaps I can create
a macro that will allow me to search for "part" of the co. name
(unique identifier) in the DB 1 column, then it will hopefully grab
the correct cell along with its adjacent account # cell...cut them
both and paste them above the cell selected...so in the end I will have
the correct DB1 account # and name next to its DB2 data and all I
would have had to do this whole time was select the right cell to
paste it into and search under a certain name criteria. I understand
this wouldn't work 100% of the time (in which case I would have to
take those few and manually do them), but it would save a person A LOT
of time in the searching, cutting, and pasting routines. - I am not
sure if this is more of an Access query problem, but it would be
better if it could be solved in Excel.

Please let me know if anyone can help

 

[Dave Peterson]

You may be able to do something with =index(match()) or =vlookup().

Debra Dalgleish has lots of notes on =vlookup() and =index(match()) here:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
and
http://www.contextures.com/xlFunctions03.html (for =index(match()))

I've never seen a partial match work close enough to help. I'd do my best to
match up the exact matches and then a manual effort for the mismatches.

You may also want to look at some of the techniques that Chip Pearson uses:
http://www.cpearson.com/excel/duplicat.htm

 

[andrewdgold]

Thank you Dave,

These links which I have seen before (through my browsing of the
google groups) didn't seem to do "exactly" what I needed. However, I
did find a macro script that helped me. I tweaked it a bit to
highlight cells in both columns that have matching text. Afterwards I
created 2 new columns on both sides and put in a formula to tell me
the color code of the cell background (eg. whether it is highlighted
yellow or just plain white - meaning no match). Then I sorted the
data by color code and I was able to segregate exact matches from the
ones I would have to match up manually. Although I didn't completely
solve my problem...I fixed almost half of the entries in little time
which does a lot for me.

If anyone else has this problem, you can email me at
(e-mail address removed).