Data Cleaning - help

newexcler

New Member
Joined
Nov 15, 2015
Messages
3
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
Target to search ( each of the cell below) - Date format DD/MM/YYSearch against this ColoumnRecord 1DDate
Tanya, Surname_25/12/18Names 1
David Surname_11/12/12Names 2 Surname
Surname kenny_10/08/12Surname Name 3
Surname, Thomas_12/06/19Name_Surname4
Jean ven desol_12/04/18 Tanya, Surname_25/12/18
Craig McDermott | Peter Jones|_10/11/13Surname Name 4
Vince_Surname_15/06/16Surname Name 5
Eddy Murphy_23/10/18Surname 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 ExpectedDavid 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>
Result
Rearranged Search Names and DateDateSearch against this ColoumnRecord 1DDateRecord Status (if matched, print the row)
Tanya Surname25/12/2018Names 16
David Surname11/12/2012Names 2 Surname11
Kenny Surname10/08/2012Surname Name 3Not available
Thomas Surname12/06/2019Name_Surname4Not available
Jean ven desol12/04/2018 Tanya, Surname_25/12/1812
Craig McDermott10/11/2013Surname Name 416
Vince Surname15/06/2016Surname Name 517
Eddy Murphy23/10/2018Surname Name 6
list continuesList continuesSurname 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:

Forum statistics

Threads
1,077,822
Messages
5,336,566
Members
399,088
Latest member
Swindlestikz

Some videos you may like

This Week's Hot Topics

Top