I am working on excel automation.I have two excel sheets.The first sheet contains a column called employer name .The requirement is i have to search for every employer name from this sheet into another sheet if it is present or not.
The condition is if for example the name is H.S Andrews so at first we will search for the whole name if not found we will remove a word from it till the time a match has been found or we are left with a word or a combination of word that should have minimum of 4 characters.
say if H.S Andrews is searched then if the match is not found then we cannot remove andrews from here since then we will only be left with 2 chracters.
If multiple results are found then we have to compare records based on the name and we have to pick up the first record that matches the criteria and update the columns in sheet 1 accordingly
Can someone help me on this. I tried to use a data table but the second excel contains almost 50000 records .Is that a good way of doing it ?