Thread: Comparing 2 columns and flagging matches

Hello all. I am a new excel formula user in need of assistance.

I have a spreadsheet with several columns of data...excel 2k3. I'm simply trying to compare 2 columns and create or flag matches. The data contains alpha numerics in both columns. 1 column is single computer hostnames, the other is computer host names with fully qualified domain name. i.e

column1 column2
wwxxyy01 wwxxyy01.aa.bb.com

Column 1 contains approx 500 hostnames. Column 2 contains approx 8000 fully qualified names. I'm looking to simply get the column 2 information (out of 8000) into the 500 items in column 1.

As I mentioned....very new....just seems simple enough but seem to be having trouble with vlookup, match, index, search...etc

Thanks for the help.

It depends on what approach you want to take and if this is a one off exercise ... and possibly lots of other factors. And the structure of your data may have a bearing on the approach.

Based on the information provided and assuming there is no data after column 2, I'd use Data | Text to Columns delimited by a "." (no quotes) and output the results in column C onwards. I would then use an Advanced Filter | Unique entries to get a list of unique (duh) entries which you can then sort. Then use VLOOKUP or a simple COUNTIF to check which ones are in or missing from column A.

Posting a sample workbook is always the best way to ensure a quick and definitive (?) solution.

Regards