Macro to Normalize Data

I have been getting spreadsheets from an OCR program and attempting to clean it up. One issue that I have is that I have two columns, one with an SSN and the other with a name, and sometimes the name is either spelled differently or misread by the OCR. I am looking to get the "max" value for the name (by length), but a pivot table only works for numbers. I do not want to use a formula, but rather a VBA approach so that I can select the cells (e.g. A2:B9) and values in B2:B9 automatically show the max name value for each name.

See attached spreadsheet. Any ideas? Thanks.

Good point..I guess whichever one is first..so long as only one is used.

even if same-valued entries in Col A aren't all next to each other

Please Login or Register  to view this content.

Thanks Kalak..that is exactly what I was looking for.

Glad to help.

If you want a simpler, easier to understand(?) macro (my earlier was rather unnecessarily complex overkill) where all the like 111-11-1111 etc. are consecutive, then

Please Login or Register  to view this content.

Either one will work.. Thanks again...