Hello
I am currently working on an excel sheet whereby there is a list of names in 5 columns. Some rows in these columns have more than one name in each cell. These names are separated with a return carriage.
I need to know the number of non repetitive names in these 5 columns.
I was thinking of first separating these names by using text to columns, assigning them to an array and then looping through to check for repetitions, and increasing the count when a name that appears only once in the array is found. If the name is found again it should not count.
I am not sure if my requirements are clear. So far I only have the code to separate the names by text to columns
Any help would be greatly appreciated!
I am currently working on an excel sheet whereby there is a list of names in 5 columns. Some rows in these columns have more than one name in each cell. These names are separated with a return carriage.
I need to know the number of non repetitive names in these 5 columns.
I was thinking of first separating these names by using text to columns, assigning them to an array and then looping through to check for repetitions, and increasing the count when a name that appears only once in the array is found. If the name is found again it should not count.
I am not sure if my requirements are clear. So far I only have the code to separate the names by text to columns
Code:
Sub CountName()
Dim rngSource As Range
Dim rngDestination As Range
Set rngSource = Sheet1.Range("E2:E5") ' or wherever my list is, working with only one column for now
Set rngDestination = Sheet1.Range("O2") ' parsed data will be placed here
rngSource.TextToColumns _
Destination:=rngDestination, _
DataType:=xlDelimited, _
Other:=True, _
OtherChar:=vbLf
End Sub
Any help would be greatly appreciated!