Counting the number of different names in a row

[Guest]

I have a block of cells from row 2 to 37. The information of interest starts at column G to column CI. In each odd numbered row is a list of names. I need to count the number of different names in each odd numbered row disregarding blank cells in the row and return those values to a column in another sheet in the same workbook. For example:
Row 3 - Mike, Mike, Kevin, ,Tammy would return a value of 3 where each name is in a separate cell with a blank cell between kevin and tammy.
Any help is greatly appreciated.
Erik

 

[merjet]

Your request lacks clarity, but the following should get
close to what you want.

HTH,
Merjet


Sub Macro1()
Dim colNames As Collection
Dim c As Range
Dim rng As Range
Dim iCt As Integer
Dim iRow As Integer

Sheets("Sheet2").Cells.Clear
On Error Resume Next
For iRow = 3 To 37 Step 2
Set colNames = New Collection
Set rng = Sheets("Sheet1").Range("G" & iRow & ":CI" & iRow)
For Each c In rng
Debug.Print c.Address
colNames.Add c.Value, c.Value
Next c
For iCt = 1 To colNames.Count
Sheets("Sheet2").Cells(iCt, (iRow - 1) / 2) = colNames(iCt)
Next iCt
Set colNames = Nothing
Next iRow

End Sub

 

[Guest]

Your request lacks clarity, but the following should get
close to what you want.

HTH,
Merjet


Sub Macro1()
Dim colNames As Collection
Dim c As Range
Dim rng As Range
Dim iCt As Integer
Dim iRow As Integer

Sheets("Sheet2").Cells.Clear
On Error Resume Next
For iRow = 3 To 37 Step 2
Set colNames = New Collection
Set rng = Sheets("Sheet1").Range("G" & iRow & ":CI" & iRow)
For Each c In rng
Debug.Print c.Address
colNames.Add c.Value, c.Value
Next c
For iCt = 1 To colNames.Count
Sheets("Sheet2").Cells(iCt, (iRow - 1) / 2) = colNames(iCt)
Next iCt
Set colNames = Nothing
Next iRow

End Sub


I put that into sheet1 as a worksheet change sub and it returns all the different names in each row as a column in sheet2. All I really want to know is how many different people are in each odd numbered row. So if row 3 has a list of names with only 4 distinctly different names in the list, the number 4 is returned to cell a1 in sheet2. Then if row 5 in sheet1 has 3 different names, the number 3 is returned to a2 of sheet2 and so on through row 37.

 

[merjet]

All I really want to know is how many different people are in each odd

numbered row. So if row 3 has a list of names with only 4 distinctly different
names in the list, the number 4 is returned to cell a1 in sheet2. Then if row 5
in sheet1 has 3 different names, the number 3 is returned to a2 of sheet2 and
so on through row 37.

Then replace:
For iCt = 1 To colNames.Count
Sheets("Sheet2").Cells(iCt, (iRow - 1) / 2) = colNames(iCt)
Next iCt

With:
Sheets("Sheet2").Cells(iCt, (iRow - 1) / 2, "A") = colNames.Count

HTH,
Merjet

 

[Guest]

Here is the final product. Thank you so much for your help.
Erik

Private Sub Worksheet_Change(ByVal Target As Range)
Dim colNames As Collection
Dim c As Range
Dim rng As Range
Dim iCt As Integer
Dim iRow As Integer

On Error Resume Next
For iRow = 3 To 37 Step 2
Set colNames = New Collection
Set rng = Sheets("Tracker").Range("G" & iRow & ":CI" & iRow)
For Each c In rng
Debug.Print c.Address
colNames.Add c.Value, c.Value
Next c
Sheets("Student Info").Cells(((iRow - 1) / 2) + 3, 9) = colNames.Count
Set colNames = Nothing
Next iRow
End Sub