excel vba - How to use VBA to add cells to a chart until the strings in two different columns match up -

i have client lists 2 different systems need compare in order figure out if there clients missing in either system. there problem client names may have been entered each system different (ex. sun solutions vs sun sol.). have used vlookup indicate on spreadsheet client names on both lists. "no match found" entered next client when there no match. now, need clarify why there no match found. figured out if there no match both systems, means spelling difference. if there no match 1 of systems, means client missing 1 of systems. solution works though if client names match located on same row. need code add cells 1 column until client name matches in second column.

here have been doing far:

sub notfoundreason()  'compares [accounting] list of clients [management] list of clients determine reason client wasn't found  dim comparrisonsheet worksheet dim rng range  'set variable set comparrisonsheet = sheet1 set rng = range("e:e").find(what:=range("b10").value, _       lookat:=xlwhole, matchcase:=false)  'unprotect sheet start searching , clearing data     comparrisonsheet.unprotect     finalrow = comparrisonsheet.range("b2000").end(xlup).row  searchcompare: 'loop through rows search matching client names , compare non-matching names determine reason why there no match     = 10 finalrow            if not rng nothing               if cells(i, 8) = "missing [accounting]"                  'add cells [accounting] client list                     range(cells(i, 2), cells(i, 3)).select                     selection.insert shift:=xldown, copyorigin:=xlformatfromleftorabove                 ' refreshcalc macro                     range("h10").select                     selection.autofill destination:=range("h10:h1999")                     range("h10:h1999").select              end if              if cells(i, 8) = "missing [management]"                  'add cells [management] client list                     range(cells(i, 5), cells(i, 6)).select                     selection.insert shift:=xldown,   copyorigin:=xlformatfromleftorabove                 ' refreshcalc macro                     range("h10").select                     selection.autofill destination:=range("h10:h1999")                     range("h10:h1999").select              end if        else             'add cells [accounting] until hits it's match                 range(cells(i, 2), cells(i, 3)).select                 selection.insert shift:=xldown, copyorigin:=xlformatfromleftorabove             ' refreshcalc macro                     range("h10").select                     selection.autofill destination:=range("h10:h1999")                     range("h10:h1999").select          end if      next  closing: 'protect sheet , select coder cell next use     comparrisonsheet.protect     range("a1").select     msgbox "have great day! :)"  end sub 

here looks (with example clients):

here should like:

this way think of compare 2 lists. open suggestions if there better way. :)