CODEWITHSUNDEEP
vbaexcel
Sonu Patel
Sonu Patel

Reputation: 1

Count distinct words in the row and paste the count in sheet 2

I have an Excel column containing multiple sets of data. I want to find the count all the distinct set of records in it and then the result of it to be printed in the Cell of sheet 2 in the same Excel.

Client

*Burberry Group p

*CA Technologies

*CA Technologies

*CA Technologies

*CA Technologies

*CA Technologies

*CA Technologies

*CA Technologies

CSM Bakery Solut

Deckers Outdoor

Mattel Inc. (Glo

Mattel Inc. (Glo

Mattel Inc. (Glo

Mattel Inc. (Glo

Mattel Inc. (Glo

Mattel Inc. (Glo

Mattel Inc. (Glo

Riverbed Technol

Riverbed Technol

Riverbed Technol

Riverbed Technol

Riverbed Technol

Riverbed Technol

Smurfit Kappa Gr

Smurfit Kappa Gr

Smurfit Kappa Gr

Smurfit Kappa Gr

Thermo Fisher Sc

Thermo Fisher Sc

Thermo Fisher Sc

Thermo Fisher Sc

Thermo Fisher Sc

Thermo Fisher Sc

Thermo Fisher Sc

For the above data. Result should be 8, as there is 8 client names. But those are repeated in the column.

The result of the count should be printed in sheet 2 cell.

Upvotes: 0

Views: 201

Answers (2)

Bond
Bond

Reputation: 16311

The following array formula will count unique values in column A on Sheet1 and then put the result in A1 on Sheet2:

Dim s As String
s = "Sheet1!" & Sheet1.Range("A1", Sheet1.Range("A1").End(xlDown)).Address
Sheet2.Range("A1") = Evaluate("=SUM(1/COUNTIF(" & s & "," & s & "))")

Upvotes: 1

Uri Goren
Uri Goren

Reputation: 13690

You could use this VBA code, assuming your data is in Sheet1!A2:A100 and the output is in Sheet2!A1

Dim row As Integer, start_row As Integer, end_row As Integer, v As String, concat As String, count_distinct As Integer
start_row=2
end_row=100
concat="~"
For row = start_row to end_row
  v=Sheet1.Range("A" & row)
  If InStr(concat,v)<=0 Then concat=concat & "~" & v
Next row
count_distinct=UBound(Split(concat,"~"))
Sheet2.Range("A1")=count_distinct

The count is in count_distinct and the values are in Split(concat,"~")

However, You don't need VBA to filter out duplicates,

simply read the "Remove duplicate values" section in Filter for unique values with excel, it's a built in feature

Upvotes: 0

Related Questions