vba Check Col has same count of Values in another Col with the same count of values regardless of the order

[gint3232]

Hi Everyone,

Does anyone know how to via "VBA" check one COLs Values against another Cols Values, and check that they match each other as in count of each set of Values! I do not wish to use conditional formatting ,as I have lot more manipulating to do before and after I achieving this.

Take the following two examples: first one the condition is not met and the 2nd is met

Col A has 3x "A", 4x"B" and 2x "Z" Where as Col B has 4x"B", 1 x "Z" and 4 x "A"...False

[TABLE="class: grid, width: 50, align: center"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Z[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Z[/TD]
[TD]A[/TD]
[/TR]
</tbody>[/TABLE]

Col A has 3x "A", 4x"B" and 2x "Z" Where as Col B has 4x"B", 2 x "Z" and 3 x "A"...TRUE

[TABLE="class: grid, width: 50, align: left"]
<tbody>[TR]
[TD]B[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Z[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]Z[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Z[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
</tbody>[/TABLE]



Many thanks for reading and any suggestion welcome

 

[Fluff]

How about

Sub CompareCol()

   Dim Cl As Range
   Dim Cnt As Long
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A2", Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then
            If WorksheetFunction.CountIf(Range("A:A"), Cl.Value) = WorksheetFunction.CountIf(Range("B:B"), Cl.Value) Then Cnt = Cnt + 1
            .Add Cl.Value, Nothing
         End If
      Next Cl
      If Cnt = .Count Then
         MsgBox True
      Else
         MsgBox False
      End If
   End With
End Sub

 

[gint3232]

How about

Well That is exactly what I needed your a champ! thanks ever so much!

 

[Fluff]

Glad to help & thanks for the feedback