VBA code for Distinct Count

[Michael Fowler]

Need help with two things ... (1) VBA code for Distinct Count of Values in a Column, and (2) VBA code for Distinct Count of Values in a Column, but with a condition from another Column (in effect, a Distinct CountIf).

Thanks in advance.

 

[Michael Fowler]

1. Hmm, the suggestions offered have been based on what is shown in your profile. If you need your question answered for different versions it obviously would be best to state that in the first post of a thread.
2. Do you need this solution for work or for home?
3. If you are on 2016 in the office and 2021 at home, when are you using the 365 shown in your profile?

Thanks for pointing that out Peter. I don't remember selecting 365 on my profile, so I don't know if that was a default selection or not. Have corrected now, changing to both 2021 and 2016 in my profile to cater for both home and work systems. In answer to your question ... immediately I need this solution to work in 2016, but it would also be good if it worked in 2021. Cheers, Michael.

 

[Peter_SSs]

No, 365 is not a default so you must have accidentally set that but at least you have it right now.

If you were interested in a solution using a permanent formula you could use this which should count the unique values in rows 2:1000 even if any rows in that range get deleted.

=SUMPRODUCT((INDEX('Subledger Data'!H:H,2):INDEX('Subledger Data'!H:H,1000)<>"")/COUNTIF(INDEX('Subledger Data'!H:H,2):INDEX('Subledger Data'!H:H,1000),INDEX('Subledger Data'!H:H,2):INDEX('Subledger Data'!H:H,1000)&""))

However, if you do want to go with a vba solution you could try something like this. My code uses the last row used in column H of 'Subledger Data'. I'm not sure if that was your intention. If not you can modify the code for a different calculation of 'Lastrow'

Sub Test2()
  Dim d As Object
  Dim a As Variant
  Dim i As Long, Lastrow As Long, MyCount As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  With Sheets("Subledger Data")
    Lastrow = .Range("H" & Rows.Count).End(xlUp).Row
    a = .Range("H2", .Range("H" & Lastrow)).Value
  End With
  For i = 1 To UBound(a)
    If Len(a(i, 1)) > 0 Then d(a(i, 1)) = 1
  Next i
  MyCount = d.Count
  MsgBox MyCount
End Sub

 

[Michael Fowler]

No, 365 is not a default so you must have accidentally set that but at least you have it right now.

If you were interested in a solution using a permanent formula you could use this which should count the unique values in rows 2:1000 even if any rows in that range get deleted.

=SUMPRODUCT((INDEX('Subledger Data'!H:H,2):INDEX('Subledger Data'!H:H,1000)<>"")/COUNTIF(INDEX('Subledger Data'!H:H,2):INDEX('Subledger Data'!H:H,1000),INDEX('Subledger Data'!H:H,2):INDEX('Subledger Data'!H:H,1000)&""))

However, if you do want to go with a vba solution you could try something like this. My code uses the last row used in column H of 'Subledger Data'. I'm not sure if that was your intention. If not you can modify the code for a different calculation of 'Lastrow'

Sub Test2()
  Dim d As Object
  Dim a As Variant
  Dim i As Long, Lastrow As Long, MyCount As Long
 
  Set d = CreateObject("Scripting.Dictionary")
  With Sheets("Subledger Data")
    Lastrow = .Range("H" & Rows.Count).End(xlUp).Row
    a = .Range("H2", .Range("H" & Lastrow)).Value
  End With
  For i = 1 To UBound(a)
    If Len(a(i, 1)) > 0 Then d(a(i, 1)) = 1
  Next i
  MyCount = d.Count
  MsgBox MyCount
End Sub

Thanks Peter, I'll give those a try and let you know how it goes. I do appreciate your help. Cheers, Michael.

 

[Peter_SSs]

You're welcome.

 

[Michael Fowler]

Thanks Peter, I'll give those a try and let you know how it goes. I do appreciate your help. Cheers, Michael.

Yes Peter, your VBA code did work, so thanks very much for that. Are you also able to help me with the second part of my query ? That is, VBA code for Distinct Count of Values in a Column, but with a condition from another Column. So I need to do a count on the Column you've already helped me with ('Subledger Data'!H:H) by Funder 1, Funder 2, etc. So a Distinct Count on column H:H by Funders in another column.

 

[Peter_SSs]

Yes Peter, your VBA code did work, so thanks very much for that. Are you also able to help me with the second part of my query ? That is, VBA code for Distinct Count of Values in a Column, but with a condition from another Column. So I need to do a count on the Column you've already helped me with ('Subledger Data'!H:H) by Funder 1, Funder 2, etc. So a Distinct Count on column H:H by Funders in another column.

That sounds like you are looking for multiple results rather than a single result?
Could you make up a small set of dummy data (say about 15 rows with 3 Funders) and post that and the expected results with XL2BB?
That way we can see how the data is arranged (eg if sorted or not), copy it for testing and know where the columns are and where the results should be and what they would look like.

 

[Michael Fowler]

That sounds like you are looking for multiple results rather than a single result?
Could you make up a small set of dummy data (say about 15 rows with 3 Funders) and post that and the expected results with XL2BB?
That way we can see how the data is arranged (eg if sorted or not), copy it for testing and know where the columns are and where the results should be and what they would look like.

Hi Peter. Firstly I needed a single result, which you've solved for me. Secondly, I now need multiple results, which is not number of transactions by Funder, but number of different Clients by Funder. In other words, for each Funder a Distinct Client Count. I have a small data sample ready, but am not familiar with XL2BB. Please advise.

 

[Peter_SSs]

Please advise.

My advice would be to follow the XL2BB link that I gave you and learn about it, then provide the samples requested.