simplify a formula

4

5

6

7

8

9

4

1

2

3

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)

VBA Code:

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 · Jul 7, 2023

excelNewbie22 said:
f15 should've wrote "should be 3",

It is important that you explain the results, only you know the purpose.
Can you explain why 3?

excelNewbie22 said:
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

Also with all due respect, but there it doesn't say that you want to change the formula.

i thought maybe some of you can help simplify it?

Simplify what, the formula or the UDF?

If you have slow problems on the page, the recommended thing is a macro, that's why I give you the macro.

But, let's try to solve your problem with the formula.

For that I need you to explain the results.

DanteAmor · Jul 7, 2023

These are your formulas, one delivers 4 and the other 8, so explain how to get a result of 3.

Libro1

A

B

C

D

E

F

G

15

1

2

1

4

8

16

1

2

Hoja1

Cell Formulas
Range	Formula
F15	F15	=SUM(--(COUNTIF(A16:D16,A15:D15)>0))
G15	G15	=COUNTIF(A15:D15,A16)+COUNTIF(A15:D15,B16)+COUNTIF(A15:D15,C16)+COUNTIF(A15:D15,D16)
Press CTRL+SHIFT+ENTER to enter array formulas.

excelNewbie22 · Jul 9, 2023

Phuoc - thank you but also wrong for the formula (didn't check the macro since i don't need it)

Dante - i apologize if i wasn't clear,
i post the macro for all who would like to help will see how i got to the result, since the xl2bb don't copy macro's,
but i meant simplify as in making a very simple formula, like the example i gave in h2,
and you are right, macro will be faster, but no convenient for me,

so explaining why 3: (i'll do my best)
i want to know how many numbers from the range a1:d1,
repeated uniquely from the range a2:d2
so when there's duplicates it get's tricky,
maybe possible to think like that ?
a1:d1 -
1a 1b 2a 1c
a2:d2 -
1a 1b 2a 2b
so in a1:d1 repeated only 1a and 1b and 2a but 1c didn't appear in a2:d2 so the total is 3

DanteAmor · Jul 9, 2023

No, that example is not the one I want you to explain. Explain the example in post#6, since you say in post#8 that F15 should be 3.

And by the way, explain the examples you have put in your post. since the example you tried to explain doesn't have duplicates.

excelNewbie22 · Jul 9, 2023

the example from post#6 in F15 identical to the one i gave in post#13,
and the examples i gave

Excel Formula:

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

is only an example for what i meant by "simplify" =

Excel Formula:

=COUNTIF(A2:F2,A3)+COUNTIF(A2:F2,B3)+COUNTIF(A2:F2,C3)+COUNTIF(A2:F2,D3)+COUNTIF(A2:F2,E3)+COUNTIF(A2:F2,F3)

which don't have duplicates but with duplicates this method don't work, and this is why i asked for help
basically in all examples, how many times, each number repeats, uniquely, in first row from the previous one

DanteAmor · Jul 9, 2023

sorry.
edit.

DanteAmor · Jul 9, 2023

I don't understand how to count when there are duplicates above and below.
It is something that you must explain, it is not enough to write "It should be..."

Please, write the result for each following case and a brief explanation for each case.

That will certainly help us help you.

Libro1

A

B

C

D

E

F

G

1

Result

2

1

2

3

4

1

3

1

7

5

6

4

5

1

2

3

4

?

why

6

1

5

6

7

8

1

2

3

4

?

why

9

1

6

10

11

1

2

3

4

?

why

12

1

13

14

1

3

4

?

why

15

1

2

5

6

16

17

1

3

4

?

why

18

1

5

6

19

20

1

3

4

?

why

21

1

6

22

23

1

3

4

?

why

24

1

25

26

1

4

?

why

27

1

5

6

7

28

29

1

4

?

why

30

1

6

7

31

32

1

4

?

why

33

1

7

34

35

1

4

?

why

36

1

37

38

1

?

why

39

1

5

6

7

40

41

1

?

why

42

1

6

7

43

44

1

?

why

45

1

7

46

47

1

?

why

48

1

Hoja1

excelNewbie22 · Jul 11, 2023

trying...

test

A

B

C

D

E

F

G

H

1

Result

2

1

2

3

4

3

1

7

5

6

4

5

1

2

3

4

why

the number "1" in a5 :d5 repeated uniquely from a6:d6

6

1

5

6

7

8

1

2

3

4

why

the number "1" in a8 :d8 repeated uniquely from a9:d9

9

1

6

10

11

1

2

3

4

why

the number "1" in a11:d11 repeated uniquely from a12:d12

12

1

13

14

1

3

4

why

the number "1" in a14:d14 repeated uniquely from a15:d15

15

1

2

5

6

16

17

1

3

4

why

the number "1, 1" in a17:d17 repeated uniquely from a18:d18

18

1

5

6

19

20

1

3

4

why

the number "1, 1" in a20:d20 repeated uniquely from a21:d21

21

1

6

22

23

1

3

4

why

the number "1, 1" in a23:d23 repeated uniquely from a24:d24

24

1

25

26

1

4

why

the number "1" in a26:d26 repeated uniquely from a27:d27

27

1

5

6

7

28

29

1

4

why

the number "1, 1" in a29:d29 repeated uniquely from a30:d30

30

1

6

7

31

32

1

4

why

the number "1, 1, 1" in a32:d32repeated uniquely from a33:d33

33

1

7

34

35

1

4

why

the number "1, 1, 1" in a35:d35 repeated uniquely from a36:d36

36

1

37

38

1

why

the number "1" in a38:d38 repeated uniquely from a39:d39

39

1

5

6

7

40

41

1

why

the number "1, 1" in a41:d41 repeated uniquely from a42:d42

42

1

6

7

43

44

1