Formula/Function: Group of Cell Values = Name

[Adamo13]

Hello, Excel Friends.

My obstacle may not have a fix, but I must ask. I have a column that has file nos. for matters (e.g., 0B-040300US). The seventh character of the file no. identifies the family it belongs to. For instance, all the following matters (0B-040300US, 0B-040301US, and 0B-040302US) belong to the same family. A matter with file no. 0B-040400US would be another family; 0B-040500US would be, yet another family. I created a column using the LEFT function to extract the first seven character (e.g., 0B-0403). I now want to create a column that will recognize all of the similar family members and place a numerical value for that family (i.e., all matters with the same 7-character value (e.g., 0B-0403) will be named "1" under the Family column; all matters with "0B-0404" will be family "2" -- and so on.

So basically, I need to know if I can look to the family members (similar 7-character file nos.) and create a column to define what family they belong to (1, 2, 3, 4, etc.).

Any help or guidance would be cherished! THX.

 

[ravishankar]

Hi
assuming col a has file nos, List the 7 letter strings in col J and the corresponding family nos in col K. paste the following codes in the macro window ( alt F11)

Sub adam()
Dim x As Long, e As Long
x = Range("J65536").End(xlUp).Row
On Error Resume Next
    For e = 2 To Range("A65536").End(xlUp).Row
    Cells(e, 2) = Left(Cells(e, 1), 3)
    Cells(e, 3) = WorksheetFunction.VLookup(Cells(e, 2), Range("J2:K" & x), 2, False)
    Next e
 MsgBox "Complete"
End Sub

Run the macro. it lists 7 character string in col B and family No. in col C
Ravi