Geek asks and answers

Specify the number of different values ​​in the selected (large) range in VBA?

How to count the number of different values ​​(numbers and strings, mixed) in a selected (large) range in VBA?

I think of it this way:
 1. Read the data in a one-dimensional array.
 2. Sort the array (fast or merger) must be checked, 3. Just count the number of different values, if the sorted array: if(a[i]<>a[i+1]) then counter=counter+1.

Is this the most effective way to solve this problem?

Edit: I want to do this in Excel.

+5
source share
4 answers

Here is the VBA solution

You do not need an array to do this. You can also use the collection. Example

Sub Samples()
    Dim scol As New Collection

    With Sheets("Sheet1")
        For i = 1 To 100 '<~~ Assuming the range is from A1 to A100
            On Error Resume Next
            scol.Add .Range("A" & i).Value, Chr(34) & _
            .Range("A" & i).Value & Chr(34)
            On Error GoTo 0
        Next i
    End With

    Debug.Print scol.Count

    'For Each itm In scol
    '   Debug.Print itm
    'Next
End Sub

Followup

Sub Samples()
    Dim scol As New Collection
    Dim MyAr As Variant

    With Sheets("Sheet1")
        '~~> Select your range in a column here
        MyAr = .Range("A1:A10").Value

        For i = 1 To UBound(MyAr)
            On Error Resume Next
            scol.Add MyAr(i, 1), Chr(34) & _
            MyAr(i, 1) & Chr(34)
            On Error GoTo 0
        Next i
    End With

    Debug.Print scol.Count

    'For Each itm In scol
    '   Debug.Print itm
    'Next
End Sub
+7

2 3, , Scripting.Dictionary . , (resume next). , count, .

, :

Function UniqueEntryCount(SourceRange As Range) As Long

    Dim MyDataset As Variant
    Dim dic As Scripting.Dictionary
    Set dic = New Scripting.Dictionary

    MyDataset = SourceRange

    On Error Resume Next

    Dim i As Long

    For i = 1 To UBound(MyDataset, 1)

        dic.Add MyDataset(i, 1), ""

    Next i

    On Error GoTo 0

    UniqueEntryCount = dic.Count

    Set dic = Nothing

End Function

, resume next " ", exists , , , , . , , , , , exists YMMY. exists:

Function UniqueEntryCount(SourceRange As Range) As Long

    Dim MyDataset As Variant
    Dim dic As Scripting.Dictionary
    Set dic = New Scripting.Dictionary

    MyDataset = SourceRange

    Dim i As Long

    For i = 1 To UBound(MyDataset, 1)

        if not dic.Exists(MyDataset(i,1)) then dic.Add MyDataset(i, 1), ""

    Next i

    UniqueEntryCount = dic.Count

    Set dic = Nothing

End Function

, , .

+4

, i_saw_drones, Scripting.Dictionary. On Error Resume Next, . , Microsoft Scripting Runtime. , , - .

, Excel, 1 . , .

(.. UniqueCount = UniqueEntryCount(ActiveSheet.Cells) UniqueCount = UniqueEntryCount(MySheet.Range("A1:D100"))

Function UniqueEntryCount(SourceRange As Range) As Long
    Dim MyDataset As Variant
    Dim MyRow As Variant
    Dim MyCell As Variant
    Dim dic As Object
    Dim l1 As Long, l2 As Long

    Set dic = CreateObject("Scripting.Dictionary")
    MyDataset = SourceRange

    For l1 = 1 To UBound(MyDataset)
        ' There is no function to get the UBound of the 2nd dimension 
        ' of an array (that I'm aware of), so use this division to 
        ' get this value. This does not work for >=3 dimensions!
        For l2 = 1 To SourceRange.Count / UBound(MyDataset)
            If Not dic.Exists(MyDataset(l1, l2)) Then
                dic.Add MyDataset(l1, l2), MyDataset(l1, l2)
            End If
        Next l2
    Next l1

    UniqueEntryCount = dic.Count
    Set dic = Nothing
End Function

, "" . , , :

    For l1 = 1 To UBound(MyDataset)
        For l2 = 1 To SourceRange.Count / UBound(MyDataset)
            If Not dic.Exists(MyDataset(l1, l2)) And MyDataset(l1, l2) <> "" Then
                dic.Add MyDataset(l1, l2), MyDataset(l1, l2)
            End If
        Next l2
    Next l1
+3

, #. .

// first copy the array so you don't lose any data
List<value> copiedList = new List<value>(yourArray.ToList());

//for through your list so you test every value
for (int a = 0; a < copiedList.Count; a++)
{
  // copy instances to a new list so you can count the values and do something with them
  List<value> subList = new List<value>(copiedList.FindAll(v => v == copiedList[i]);

  // do not do anything if there is only 1 value found
  if(subList.Count > 1)
                        // You would want to leave 1 'duplicate' in
    for (int i = 0; i < subList.Count - 1; i++)
        // remove every instance from the array but one
        copiedList.Remove(subList[i]);
}
int count = copiedList.Count; //this is your actual count

Not tested, please try.

You must wrap this inside the method so that there is no mess with garbage. Otherwise, you will lose a copy of the array only later. (return counter)

EDIT: you need a list to work, use Array.ToList ();

0
source

More articles:


All Articles