If X = Y then (when it doesn't have to be an exact match)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Range("A1:G65500").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

i = 2
n = 3

Do Until Cells(i, 1) = ""
Do Until Cells(i, 1) <> Cells(i - 1, 1)
i = i + 1
Loop
Sheets("Calc").Cells(n, 13) = Cells(i, 1)
n = n + 1
i = i + 1
Loop

So I end up with a list of the unique names from one list put into another
list.

Two problems, the first, someone who seems to change their name from Joanne
to JOANNE . The sort seems to recognise that these two names are the same,
but the macro doesn't; what I then end up with is in the list of unique names
about 10 lines of the same person, alternating between upper case and normal.

Second problem, I do the exact same macro (sorting a different column, and
looking at that column for names) for a list of jobs, some of the Jobs have a
space or such after their name so they're not exact match.

Also, I have one job called "Advisor Slips" and another thats "Advisors
Slips" and that extra "s" makes them different.

Is there anyway I could solve any or all of these problems? Ideally I could
put that it doesn't have to be an exact match, only say 90% similar?
 
This will ignore capitalization and spaces. Any help? James

Do Until Replace(UCase(Cells(i,1))," ","")<>Replace(UCase(Cells(i-1,1)),"
","")
 
Change -
Do Until Cells(i, 1) <> Cells(i - 1, 1)
Click to expand...
to-
Do Until Replace(LCase(Cells(i, 1)), " ", "") _
<> Replace(LCase(Cells(i - 1, 1)), " ", "")

Regards,
Peter T
 
That solved both problems. Cheers

Zone said:
This will ignore capitalization and spaces. Any help? James

Do Until Replace(UCase(Cells(i,1))," ","")<>Replace(UCase(Cells(i-1,1)),"
","")
Click to expand...
 
Back
Top