Excel formula for matching numbers

[sinoyon780]

Hi,

I have to column of Mobile number list in W and X so now i want 2 new list in Y and Z as a like

how many number are same in W & X = List in Y
how many number not same in W & X = List in Z (From X)

 

[KRice]

Try this...I was not sure if W and X might contain duplicates within each column, so the Z formula has additional UNIQUE functions to eliminate same-column duplicates (that may not be necessary, depending on your actual data).

Book3
	W	X	Y	Z
1	Source1	Source2	same in both	not same
2	10	33	12	13
3	11	12	11	14
4	12	11	10	15
5	13	10	17	33
6	14	44	16	44
7	15	55	20	55
8	16	17	18
9	17	16	19
10	18	20
11	19	18
12	20	19
Sheet1

Cell Formulas
Range	Formula
Y2:Y9	Y2	=LET(w,UNIQUE(W2:W12),x,UNIQUE(X2:X12),FILTER(x,DROP(FREQUENCY(w,x),-1)>0))
Z2:Z7	Z2	=UNIQUE(VSTACK(UNIQUE(W2:W12),UNIQUE(X2:X12)),,1)
Dynamic array formulas.

Edit: I think this shorter version should also work for the Y formula:

=LET(w,W2:W12,x,X2:X12,FILTER(x,DROP(FREQUENCY(w,x),-1)>0))