Hi Everyone,

I am working on an Analytics project and need some help with formatting the data. I need a way to compare candidate names and fill in their candidate ID based on what data I already have. Below is an example of the data I am working with:



can_id can_nam
MURPHY, SCOTT
MURPHY, SCOTT
MURPHY, SCOTT
MURPHY, SCOTT
MURPHY, SCOTT
MURPHY, SCOTT
MURPHY, SCOTT
MURPHY, SCOTT
MURPHY, SCOTT
MURPHY, SCOTT
MURPHY, SCOTT
MURPHY, SCOTT
H0NY20079 MURPHY, SCOTT M
H0NY20079 MURPHY, SCOTT M
MURPHY (NY), SCOTT
MURPHY (NY), SCOTT
MURPHY (NY), SCOTT
MURPHY (NY), SCOTT
MURPHY (NY), SCOTT
MURPHY (NY), SCOTT
MURPHY (NY), SCOTT
MURPHY (NY), SCOTT
H0NY20079 Murphy, Scott
H0NY20079 Murphy, Scott
H0NY20079 Murphy, Scott
H0NY20079 Murphy, Scott
H0NY20079 Murphy, H. Scott
H0NY20079 Murphy, H. Scott
H0NY20079 Murphy, H. Scott
H0NY20079 Murphy, H. Scott
H0NY20079 Murphy, H. Scott
H0NY20079 Murphy, H. Scott
H0NY20079 Murphy, H. Scott
H0NY20079 Murphy, H. Scott
H0NY20079 Murphy, H. Scott
H0NY20079 Murphy, H. Scott
H0NY20079 Murphy, H. Scott
H0NY20079 Murphy, H. Scott
H0NY20079 Murphy, H. Scott
H0NY20079 Murphy, H. Scott
H0NY20079 MURPHY, SCOTT M
H0NY20079 MURPHY, SCOTT M
H0NY20079 Murphy, Scott
H0NY20079 Murphy, Scott
H0NY20079 Murphy, Scott
H0NY20079 Murphy, Scott
H0NY20079 Murphy, Scott
H0NY20079 Murphy, Scott
H0NY20079 Murphy, Scott

can_id can_nam
Aden, Doug
Aden, Doug
Aden, Doug
Aden, Doug
Aden, Doug
Aden, Doug
H0CO04148 Aden, Doug
H0CO04148 Aden, Doug

This is the case for multiple different candidates in the file and I need the can_id to exist for all. Does anyone have a dynamic way to do this?

Thanks!