SQL - Doing a JOIN With a Case Statement

I needed to do a join between two different tables on a text field. The problem was that the names in the text fields did not always match exactly to each other and some names were very similar to each other. The only solution I could think of match the tables was to do a join using a CASE statement.

Here is the JOIN with CASE statements that I used:

  LEFT OUTER JOIN
  test.dbo.aVend on ((SUBSTRING(test.dbo.aVend.Description, 1, 8) = SUBSTRING(dbo.SubOrganization.Organization, 1, 8))
     and
 (CASE 
      WHEN len(test.dbo.aVend.Description) > 18 and ( SUBSTRING(test.dbo.aVend.Description, 1, 18) = SUBSTRING(dbo.SubOrganization.Organization, 1, 18)) THEN 1 
      WHEN len(test.dbo.aVend.Description) > 18 and ( SUBSTRING(test.dbo.aVend.Description, 1, 18) <> SUBSTRING(dbo.SubOrganization.Organization, 1, 18)) THEN 0
      else 1
   END = 1)
     and
 (CASE 
      WHEN len(test.dbo.aVend.Description) > 14 and ( SUBSTRING(test.dbo.aVend.Description, 1, 14) = SUBSTRING(dbo.SubOrganization.Organization, 1, 14)) THEN 1 
      WHEN len(test.dbo.aVend.Description) > 14 and ( SUBSTRING(test.dbo.aVend.Description, 1, 14) <> SUBSTRING(dbo.SubOrganization.Organization, 1, 14)) THEN 0
   END = 1)
     and
 (CASE 
      WHEN len(test.dbo.aVend.Description) > 12 and ( SUBSTRING(test.dbo.aVend.Description, 1, 12) = SUBSTRING(dbo.SubOrganization.Organization, 1, 12)) THEN 1 
      WHEN len(test.dbo.aVend.Description) > 12 and ( SUBSTRING(test.dbo.aVend.Description, 1, 12) <> SUBSTRING(dbo.SubOrganization.Organization, 1, 12)) THEN 0
   END = 1)
     and
 (CASE 
      WHEN len(test.dbo.aVend.Description) > 11 and ( SUBSTRING(test.dbo.aVend.Description, 1, 11) = SUBSTRING(dbo.SubOrganization.Organization, 1, 11)) THEN 1 
      WHEN len(test.dbo.aVend.Description) > 11 and ( SUBSTRING(test.dbo.aVend.Description, 1, 11) <> SUBSTRING(dbo.SubOrganization.Organization, 1, 11)) THEN 0
   END = 1)         
   )
   where test.dbo.aVend.[Comp ID] not in (501255, 501221, 500342) or test.dbo.aVend.[Comp ID] is null

Excel - Sequentialy Count The Number Of Repeat Occurances

I a column in Excel I had to put in a sequence number for each matching value in another column. So if my column of values had the series (Red, Red, Blue, Red Blue) then I would end up with (1,2,1,3,2) for the number sequence.

The values that I was sequential numbering were in column D. This is the formula that I used:

=COUNTIF($D$1:D1, D1)

I just entered the formula in the first cell and then filled the remainder of the cells using the fill handle.

Here is a sample of my sequential count in Excel: