compare columns

[ChristiLingard]

Hi all, I need to compare two columns to see which numbers are in both columns and which aren't. Is there an easy way to do this? My first column is E and the column I want to compare it to is N.

 

[ChristiLingard]

Hi all, I need to compare two columns to see which numbers are in both columns and which aren't. Is there an easy way to do this? My first column is E and the column I want to compare it to is N.

I found my answer, thank you.

 

[Fluff]

How about

Fluff.xlsm
	E	F	G	H	I	J	K	L	M	N
1				In both	In E only	In N only	In one
2	12			12	77	84	77			84
3	77			23	16	34	80			2
4	16			90	80	49	82			34
5	80			90	82	53	64			49
6	23			72	64	62	88			53
7	90			41	88	30	26			62
8	90			2	26	34	85			30
9	82				85	40	74			34
10	72				74	42	81			72
11	64				81	98	79			40
12	88				16	37	19			23
13	41				79	22	84			42
14	26				19	98	49			98
15	85						53			41
16	74						62			37
17	81						30			22
18	16						40			90
19	79						42			98
20	19						37			12
21	2						22			2
Main

Cell Formulas
Range	Formula
H2:H8	H2	=FILTER(E2:E21,COUNTIFS(N2:N21,E2:E21))
I2:I14	I2	=FILTER(E2:E21,COUNTIFS(N2:N21,E2:E21)=0)
J2:J14	J2	=FILTER(N2:N21,COUNTIFS(E2:E21,N2:N21)=0)
K2:K21	K2	=UNIQUE(VSTACK(E2:E21,N2:N21),,1)
Dynamic array formulas.

 

[ChristiLingard]

How about

Fluff.xlsm
	E	F	G	H	I	J	K	L	M	N
1				In both	In E only	In N only	In one
2	12			12	77	84	77			84
3	77			23	16	34	80			2
4	16			90	80	49	82			34
5	80			90	82	53	64			49
6	23			72	64	62	88			53
7	90			41	88	30	26			62
8	90			2	26	34	85			30
9	82				85	40	74			34
10	72				74	42	81			72
11	64				81	98	79			40
12	88				16	37	19			23
13	41				79	22	84			42
14	26				19	98	49			98
15	85						53			41
16	74						62			37
17	81						30			22
18	16						40			90
19	79						42			98
20	19						37			12
21	2						22			2
Main

Cell Formulas
Range	Formula
H2:H8	H2	=FILTER(E2:E21,COUNTIFS(N2:N21,E2:E21))
I2:I14	I2	=FILTER(E2:E21,COUNTIFS(N2:N21,E2:E21)=0)
J2:J14	J2	=FILTER(N2:N21,COUNTIFS(E2:E21,N2:N21)=0)
K2:K21	K2	=UNIQUE(VSTACK(E2:E21,N2:N21),,1)
Dynamic array formulas.

Oh wow! That is awesome! Thank you so much!!!

 

[Fluff]

You're welcome & thanks for the feedback.