Step 1:
Convert Employee Name from lastname, firstname, middle to: lastName, FirstName
=MID(B2,FIND(",",B2)+1,LEN(B2))
Separate LastName from the rest. . .
=LEFT(B2,FIND(",",B2)-1)
Separate "Steven Barrett" into just "Steven"
=IFERROR(LEFT(D2,FIND(" ",D2)-1),D2)
Merge FIRST and LAST names. . . by Concatenation
=CONCATENATE(C2,",",E2)
So, F2 is the final result.
Step 2:
Find Duplicates
Remove suffixes:
Sr, Jr, IV, III, II, I
Step 3:
Run the macros in: template_lastFirst_dupes_11.15.21.xlsm then template_fluVaxStatusSheet_11.15.21.xlsm
.bas files:
Step 4:
New
Processing the Flu Vaccine Status sheet (given to you by Occupational Health):
Make sure the sheet is formatted like below:
Then, copy the above worksheet to the 'template_VaxStatus2.xlsm' workbook. . . then run the "processVaxStatusSheetColC" module . . . then clean up the bottom parts of the sheet . . . watch for "Bailey I"
template_VaxStatus2.xlsm with processVaxStatusSheetColC.bas
processFluVaxSheet_11.15.21.mp4
Processing the Human Resources All Employee Roster
Make sure the sheet is formatted like below:
Then, copy the above worksheet to the 'template_lastFirst_dupes_11.15.21.xlsm' workbook. . . then run the "lastFirst" module . . . then clean up the bottom parts of the sheet . . . watch for "Bailey I"
template_lastFirst_dupes_11.15.21.xlsm and lastFirst.bas . . . then findDupesColC.bas then toUpperColC.bas then copy column C to new sheet in A1, then removeSuffixes.bas then copy column A1 & insert copied cells into Column C in main sheet
Video is: processHRroster_11.15.21_2ndTake.mp4