Unifying Lists

[fabioescobar]

Hi! Newbie to the forum, I am currently developing an Excel template that lists and ranks candidates that have taken tests from an online platform our company uses. The main list contains all candidates by first and last names, their location, and tests taken. Many times a single candidate is assigned to take all evaluations, other times it may be one or two. Regardless, they all have to be registered and accounted for.
When I export the results from the platform, candidates have filled their names in each of the evaluations they took, hence creating formatting issues such as spelling mistakes, or ALL CAPS. As a result, I have to do a lot of cleanup on these lists, which I don't mind as its pretty simple. However, I would like to make sure ALL candidates are included and ordered. Because the results come from different sources of the testing platform, I need to validate the final candidate list. The way I validate is after all the cleanup is done, I alphabetically order the lists and use a logical formula like A2=B2, if True then fine if not there fine tune the mistakes.
I would like to know how to formulate my sheet to compare 2 or 3 lists with names just to make sure they are all there, without having to fine tune to be perfect matches. I'd appreciate any leads, and would like to share my Raw Data worksheet with anyone willing to help.
Thanks in advance.

Fabio

 

[Derek Brown]

Hello and welcome to The Board.
It would help if you could say which version of Excel is being used.
Note that formulas UPPER, LOWER and PROPER may be useful if comparing.
I tend to use Conditional Formatting on both lists to highlight (in green) the values that are in the other list or (in red) where there is a duplicate in the same list.