Dear Experts
I had to complete a huge excel cleaning job and was not provided time.
Please refer to the attached input and reference output tabs.
I have excel column, with names in various formats, I need to compare each cell of this with master database and if matching print the row number against column A.
What I tried:
I used Delimeter and split the dates, and combined based on date, segregated into another column, then I use VLOOKUP but its tedious as I have to do several steps because given name and surname is different.
Thank you in advance.
Input file
<tbody>
</tbody>Result
<tbody>
</tbody>
I had to complete a huge excel cleaning job and was not provided time.
Please refer to the attached input and reference output tabs.
I have excel column, with names in various formats, I need to compare each cell of this with master database and if matching print the row number against column A.
What I tried:
I used Delimeter and split the dates, and combined based on date, segregated into another column, then I use VLOOKUP but its tedious as I have to do several steps because given name and surname is different.
Thank you in advance.
Input file
Target to search ( each of the cell below) - Date format DD/MM/YY | Search against this Coloumn | Record 1D | Date |
Tanya, Surname_25/12/18 | Names 1 | ||
David Surname_11/12/12 | Names 2 Surname | ||
Surname kenny_10/08/12 | Surname Name 3 | ||
Surname, Thomas_12/06/19 | Name_Surname4 | ||
Jean ven desol_12/04/18 | Tanya, Surname_25/12/18 | ||
Craig McDermott | Peter Jones|_10/11/13 | Surname Name 4 | ||
Vince_Surname_15/06/16 | Surname Name 5 | ||
Eddy Murphy_23/10/18 | Surname Name 6 | ||
Surname Name 6 | |||
Objective is to search each cell of the column A, which has the record with date after 5th March 12 and match with each of the cell of Column B and Print matched with row number of Coumn B. As shown in Tab Results Expected | David Surname_11/12/12 | ||
Jean ven desol_12/04/18 | |||
Surname kenny_10/08/12 | |||
Eddy Murphy_23/10/18 | |||
Jean ven desol_12/04/18 | |||
Craig McDermott | Peter Jones|_10/11/13 | |||
Vince_Surname | |||
<tbody>
</tbody>
Rearranged Search Names and Date | Date | Search against this Coloumn | Record 1D | Date | Record Status (if matched, print the row) | ||
Tanya Surname | 25/12/2018 | Names 1 | 6 | ||||
David Surname | 11/12/2012 | Names 2 Surname | 11 | ||||
Kenny Surname | 10/08/2012 | Surname Name 3 | Not available | ||||
Thomas Surname | 12/06/2019 | Name_Surname4 | Not available | ||||
Jean ven desol | 12/04/2018 | Tanya, Surname_25/12/18 | 12 | ||||
Craig McDermott | 10/11/2013 | Surname Name 4 | 16 | ||||
Vince Surname | 15/06/2016 | Surname Name 5 | 17 | ||||
Eddy Murphy | 23/10/2018 | Surname Name 6 | |||||
list continues | List continues | Surname Name 6 | |||||
David Surname_11/12/12 | |||||||
Jean ven desol_12/04/18 | |||||||
Surname kenny_10/08/12 | |||||||
Eddy Murphy_23/10/18 | |||||||
Thanks in advance. | Jean ven desol_12/04/18 | ||||||
Craig McDermott | Peter Jones|_10/11/13 | |||||||
Vince_Surname |
<tbody>
</tbody>
Last edited by a moderator: