simplify a formula

[excelNewbie22]

hi !
i'm using this formula in g2,

=SUM(--(COUNTIF(A3:F3,A2:F2)>0))

but when i applied it on a very large quantity of rows it gets slow, so i thought and manage to use a simpler formula like countif, in a longer (text wise) but much faster formula like in h2,
and now i want to do the same but with COUNTUNIQUE UDF, but it's more complicated, and i thought maybe some of you can help simplify it? since my above method wouldn't work on countunique

test-.xlsm
	A	B	C	D	E	F	G	H
1							smart way	simplify
2	1	2	3	4	5	6	3	3
3	4	5	6	7	8	9
4	1	2	2	3			2
5	2	3	8	5
test

Cell Formulas
Range	Formula
G2	G2	=SUM(--(COUNTIF(A3:F3,A2:F2)>0))
H2	H2	=COUNTIF(A2:F2,A3)+COUNTIF(A2:F2,B3)+COUNTIF(A2:F2,C3)+COUNTIF(A2:F2,D3)+COUNTIF(A2:F2,E3)+COUNTIF(A2:F2,F3)
G4	G4	=CountUnique(A4:D4,A5:D5)

Function CountUnique(r1 As Range, r2 As Range)
  Dim i As Long, j As Long
  Dim a As Variant, b As Variant
  a = r1.Value
  b = r2.Value
 
  For i = 1 To UBound(a, 2)
    For j = 1 To UBound(b, 2)
      If b(1, j) = a(1, i) Then
        CountUnique = CountUnique + 1
        b(1, j) = "x"
        Exit For
      End If
    Next
  Next
End Function

 

[DanteAmor]

Hi @excelNewbie22 , I hope you are well.

Try the following macro instead of formulas or UDFs.
The macro will perform the count for all rows.

Sub count_uniques()
  Dim a As Variant, b As Variant
  Dim i As Long, n As Long, j As Long, jj As Long
 
  a = Range("A2:F" & Range("A" & Rows.Count).End(3).Row).Value
  ReDim b(1 To UBound(a, 1), 1 To 1)
  For i = 1 To UBound(a, 1) Step 2
    n = 0
    For j = 1 To UBound(a, 2)
      If a(i, j) <> "" Then
        For jj = 1 To UBound(a, 2)
          If a(i + 1, jj) = a(i, j) Then
            n = n + 1
          End If
        Next
        b(i, 1) = n
      End If
    Next
  Next
  Range("G2").Resize(UBound(b)).Value = b
End Sub

------------------
But if you want the UDF, here it is updated:

Function CountUnique(r1 As Range, r2 As Range)
  Dim j As Long, jj As Long, n As Long
  Dim a As Variant, b As Variant
  a = r1.Value
  b = r2.Value
 
  For j = 1 To UBound(b, 2)
    If a(1, j) <> "" Then
      For jj = 1 To UBound(b, 2)
        If a(1, j) = b(1, jj) Then
          n = n + 1
        End If
      Next
    End If
  Next
  CountUnique = n
End Function

--------------
Try it and tell us if it is faster and you no longer have slow problems in your sheet.
Cordially
Dante Amor
--------------​

 

[DanteAmor]

I corrected a small detail in the UDF, here is the updated code:

Function CountUnique(r1 As Range, r2 As Range)
  Dim j As Long, jj As Long, n As Long
  Dim a As Variant, b As Variant
  a = r1.Value
  b = r2.Value
 
  For j = 1 To UBound(a, 2)
    If a(1, j) <> "" Then
      For jj = 1 To UBound(b, 2)
        If a(1, j) = b(1, jj) Then
          n = n + 1
        End If
      Next
    End If
  Next
  CountUnique = n
End Function

Dante Amor
	A	B	C	D	E	F	G
1							UDF
2	1	2	3	4	5	6	3
3	4	5	6	7	8	9
4	1	2	2	3			3
5	2	3	8	5
6	1	2	3	4	5	6	2
7	5	1
8	1	2	3	4	5	6	0
9	8	9
10	1	2	3	4	5	6	6
11	1	2	3	4	5	6
Hoja2

Cell Formulas
Range	Formula
G2,G10,G8,G6,G4	G2	=CountUnique(A2:F2,A3:F3)

 

[excelNewbie22]

hi dante, thanks for helping,
don't get me wrong, the first countunique i posted is fast, only slow down when fill down to a million (example) rows,
the udf you posted is wrong, see example
and while i'm appreciated your help, i prefer something more like in h2, if possible but for rows with duplicates like in the example for countunique (the original)

test.xlsb
	A	B	C	D	E	F	G	H
1							UDF	simplify
2	1	2	3	4	5	6	3	3
3	4	5	6	7	8	9
4	1	2	2	3			3	should be 2
5	2	3	8	5
6	1	2	3	4	5	6	2
7	5	1
8	1	2	3	4	5	6	0
9	8	9
10	1	2	3	4	5	6	6
11	1	2	3	4	5	6
12	1	2	2	3			6	should be 4
13	1	2	2	3
14
test

Cell Formulas
Range	Formula
G2,G12,G10,G8,G6,G4	G2	=CountUnique(A2:F2,A3:F3)
H2	H2	=COUNTIF(A2:F2,A3)+COUNTIF(A2:F2,B3)+COUNTIF(A2:F2,C3)+COUNTIF(A2:F2,D3)+COUNTIF(A2:F2,E3)+COUNTIF(A2:F2,F3)

 

[DanteAmor]

Here the corrected UDF:

Function CountUnique(r1 As Range, r2 As Range)
  Dim j As Long, jj As Long, n As Long
  Dim a As Variant, b As Variant
  Dim dic As Object
  a = r1.Value
  b = r2.Value
  Set dic = CreateObject("Scripting.Dictionary")

  For j = 1 To UBound(a, 2)
    If a(1, j) <> "" Then
      If Not dic.exists(a(1, j)) Then
        dic(a(1, j)) = Empty
        For jj = 1 To UBound(b, 2)
          If a(1, j) = b(1, jj) Then
            n = n + 1
          End If
        Next
      End If
    End If
  Next
  CountUnique = n
End Function

But the UDF is like a formula that will all update for every change in the sheet.
So, I recommend you the macro, also the corrected macro.
It will work for all the rows, do the test and tell us how long it takes with the million lines.

Sub count_uniques()
  Dim a As Variant, b As Variant
  Dim i As Long, n As Long, j As Long, jj As Long
  Dim dic As Object
 
  Set dic = CreateObject("Scripting.Dictionary")

  a = Range("A2:F" & Range("A" & Rows.Count).End(3).Row).Value
  ReDim b(1 To UBound(a, 1), 1 To 1)
  For i = 1 To UBound(a, 1) Step 2
    n = 0
    dic.RemoveAll
    For j = 1 To UBound(a, 2)
      If a(i, j) <> "" Then
        If Not dic.exists(a(i, j)) Then
          dic(a(i, j)) = Empty
          For jj = 1 To UBound(a, 2)
            If a(i + 1, jj) = a(i, j) Then
              n = n + 1
            End If
          Next
          b(i, 1) = n
        End If
      End If
    Next
  Next
  Range("G2").Resize(UBound(b)).Value = b
End Sub

I did a test with a million lines and it took 7 seconds.

 

[excelNewbie22]

the udf still wrong, see example,
but no offense dante, really, i need a simple if+countif and etc, as long as the formula will be,
again, if it's possible

test.xlsb
	A	B	C	D	E	F	G	H
1							UDF	simplify
2	1	2	3	4	5	6	3	3
3	4	5	6	7	8	9
4	1	2	2	3			2	should be 2
5	2	3	8	5
6	1	2	3	4	5	6	2
7	5	1
8	1	2	3	4	5	6	0
9	8	9
10	1	2	3	4	5	6	6
11	1	2	3	4	5	6
12	1	2	2	3			4	should be 4
13	1	2	2	3
14
15	1	1	2	1	4	should be 4
16	1	1	2	2
test

Cell Formulas
Range	Formula
G2,G12,G10,G8,G6,G4	G2	=CountUnique(A2:F2,A3:F3)
H2	H2	=COUNTIF(A2:F2,A3)+COUNTIF(A2:F2,B3)+COUNTIF(A2:F2,C3)+COUNTIF(A2:F2,D3)+COUNTIF(A2:F2,E3)+COUNTIF(A2:F2,F3)
E15	E15	=CountUnique(A15:D15,A16:D16)

 

[DanteAmor]

In your examples which one is having problems with the udf? I see all the results well. However, you want to fix the udf or a formula that uses countif.
If you don't want the udf, why are we wasting time fixing udf.
I'm not offended, I'm just giving you a better alternative.

 

[excelNewbie22]

i've mistaken, in f15 should've wrote "should be 3",
and, with respect, i wrote in the first post i need a simple formula like with "if" and "countif" if possible,
but you offer macro+udf and i didn't want to ignore, so i checked and mention it's wrong, but i'm still in need of a simple formula
and after all, i sincerely appreciated your want to help, truly!

 

[Phuoc]

Try this

Function CountUnique(r1 As Range, r2 As Range)
  Dim j As Long, jj As Long, n As Long
  Dim a As Variant, b As Variant
  Dim dic As Object
  a = r1.Value
  b = r2.Value
  Set dic = CreateObject("Scripting.Dictionary")

  For j = 1 To UBound(a, 2)
    If a(1, j) <> "" Then
      If Not dic.exists(a(1, j)) Then
        dic(a(1, j)) = Empty
        For jj = 1 To UBound(b, 2)
          If a(1, j) = b(1, jj) Then
            n = n + 1
            Exit For ' add
          End If
        Next
      End If
    End If
  Next
  CountUnique = n
End Function

 

[Phuoc]

Or try

Book1
	A	B	C	D	E	F	G
1							smart way
2	1	2	3	3	3	2	2
3	2	2	2	3	3	3
Sheet1

Cell Formulas
Range	Formula
G2	G2	=COUNT(IF(MATCH(A2:F2,A2:F2,0)=COLUMN(A2:F2),MATCH(A2:F2,A3:F3,0)))
Press CTRL+SHIFT+ENTER to enter array formulas.