adjusting a formula

[excelNewbie22]

hi,
this formula checks how many repeats numbers there is in compared to previous line
but when one of the numbers repeats in both lines it count it as one,
how to fix it?
the desirable result in this example is 3 instead of 2

22
	A	B	C	D	E
1	1	4	6	1	2
2	1	5	1	4	0
44

Cell Formulas
Range	Formula
E1:E2	E1	=SUM(IF(FREQUENCY(IFERROR(MATCH(A1:D1,A2:D2,0),""),COLUMN(A1:D1)-COLUMN(A1)+1)>0,1))
Press CTRL+SHIFT+ENTER to enter array formulas.

p.s
i would glad to get help with this thread too

macro deletion by difference between numbers

hello, i need a macro to delete any row with numbers which doesn't have a difference of 1 AND 2 by an array of predefined numbers example: array: 1-2-3-3 check numbers: 6-7-7-8 or 5-7-7-8 explaintion: the difference between highest number in the array is 3 and the lowest in...

www.mrexcel.com

 

[DanteAmor]

How about:

Libro1
	A	B	C	D	E
1	1	4	6	1	3
2	1	5	1	4	1
3	2	3	4	6	0
Hoja2

Cell Formulas
Range	Formula
E1:E3	E1	=SUMPRODUCT(ISNUMBER(MATCH(A1:D1,A2:D2,0))+0)

 

[jtakw]

Hi,

If I understand correctly, this might work also:

Book3.xlsx
	A	B	C	D	E
1	1	4	6	1	3
2	1	5	1	4	1
3	2	3	4	6	0
Sheet3

Cell Formulas
Range	Formula
E1:E3	E1	=SUMPRODUCT(--(COUNTIF(A2:D2,A1:D1)>0))

 

[excelNewbie22]

both your solutions are good, but when i checked new problem aroused,
what if in previous line there's 3-1-8-7 and in the new/above it there's 2-3-3-5
since there's only one 3 (3-1-8-7) i need the formula to count in the new line number 3 only once (2-3-3-5)
so the result will be 1
is it possible?

22
	A	B	C	D	E	F
1	1	4	6	1	3
2	1	5	1	4	1
3	2	3	4	6	2
4	2	3	3	5	2	should be 1
5	3	1	8	7	0
44

Cell Formulas
Range	Formula
E1:E5	E1	=SUMPRODUCT(ISNUMBER(MATCH(A1:D1,A2:D2,0))+0)

 

[DanteAmor]

Maybe a UDF will help you:

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

HOW TO INSTALL UDFs
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use CountUnique just like it was a built-in Excel function. For example:

varios 08mar2022.xlsm
	A	B	C	D	E
1	1	4	6	1	3
2	1	5	1	4	1
3	2	3	4	6	2
4	2	3	3	5	1
5	3	1	8	7	1
6	1	1	1	1	4
7	1	1	1	1	0
Hoja3

Cell Formulas
Range	Formula
E1:E7	E1	=CountUnique(A1:D1,A2:D2)

 

[excelNewbie22]

DanteAmor
thank you !

is there a way to permanently keep this udf in excel ? not just in one file

 

[DanteAmor]

is there a way to permanently keep this udf in excel ? not just in one file

Check the following link:

Add-ins in Excel

What are Add-ins in Excel? An add-ins is an extension that adds more features and options to Microsoft Excel. Providing additional functions to the user

www.wallstreetmojo.com

 

[excelNewbie22]

thank you again!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.