Mismatched Return

[BijanBorazjani]

Good Morning,

I have 2 columns with Unit Numbers. One is a local inventory DB and the other is the Internet inventory DB. I need to compare the two columns of data, find if there are any mismatches, and have the unit number that is not matched inserted into a cell. I wouldn't mind using a VBA code if needed.

So to summarize:

A2:A1001 Data Set 1
D2:D1001 Data Set 2

K2:K1001 Unit Number is in Data Set 2 but not Data Set 1
O2:O1001 Unit Number is in Data Set 1 but not Data Set 2

Any help would be greatly appreciated!

 

[Eric W]

Welcome to the MrExcel forum!

As you have Excel 365, it's pretty easy:

ME temp.xlsm
	A			D							K				O
1	Data Set 1			Data Set 2							In 2 but not 1				In 1 but not 2
2	a			e							i				a
3	b			f							j				b
4	c			g							k				c
5	d			h							l				d
6	e			i							m
7	f			j							n
8	g			k
9	h			l
10				m
11				n
12
Sheet41

Cell Formulas
Range	Formula
K2:K7	K2	=FILTER(D2:D1001,(D2:D1001<>"")*(COUNTIF(A2:A1001,D2:D1001)=0))
O2:O5	O2	=FILTER(A2:A1001,(A2:A1001<>"")*(COUNTIF(D2:D1001,A2:A1001)=0))
Dynamic array formulas.

 

[BijanBorazjani]

Welcome to the MrExcel forum!

As you have Excel 365, it's pretty easy:

ME temp.xlsm
	A			D							K				O
1	Data Set 1			Data Set 2							In 2 but not 1				In 1 but not 2
2	a			e							i				a
3	b			f							j				b
4	c			g							k				c
5	d			h							l				d
6	e			i							m
7	f			j							n
8	g			k
9	h			l
10				m
11				n
12
Sheet41

Cell Formulas
Range	Formula
K2:K7	K2	=FILTER(D2:D1001,(D2:D1001<>"")*(COUNTIF(A2:A1001,D2:D1001)=0))
O2:O5	O2	=FILTER(A2:A1001,(A2:A1001<>"")*(COUNTIF(D2:D1001,A2:A1001)=0))
Dynamic array formulas.

This was exactly what I needed! Thank you very much. My head and the wall appreciate it greatly.

 

[BijanBorazjani]

Welcome to the MrExcel forum!

As you have Excel 365, it's pretty easy:

ME temp.xlsm
	A			D							K				O
1	Data Set 1			Data Set 2							In 2 but not 1				In 1 but not 2
2	a			e							i				a
3	b			f							j				b
4	c			g							k				c
5	d			h							l				d
6	e			i							m
7	f			j							n
8	g			k
9	h			l
10				m
11				n
12
Sheet41

Cell Formulas
Range	Formula
K2:K7	K2	=FILTER(D2:D1001,(D2:D1001<>"")*(COUNTIF(A2:A1001,D2:D1001)=0))
O2:O5	O2	=FILTER(A2:A1001,(A2:A1001<>"")*(COUNTIF(D2:D1001,A2:A1001)=0))
Dynamic array formulas.

OK so what would i do if i wanted to see if the units that are in both Set 1 and Set 2 have different Statuses? Below is a minisheet.

OKC Inventory Equipment 10-22-21.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Branch Unit Number	Branch Unit Status	Branch Location	Infor Unit Number	Infor Unit Status	Infor Location					Unit Number Not In Infor	Branch Unit Status	Branch Location		Unit Number Not in Branch	Infor Unit Status	Infor Location		Unit Status Mismatch	Unit Number	Unit Location	Infor Status	Branch Status
2	4050-0094	Ready	MARENT	2200-0275	Ready	MARENT					T53NCF17				#CALC!
3	4050-1169	Ready	MARENT	2200-0304	Ready	MARENT
4	4050-1189	Ready	MARENT	2200-0354	Ready	MARENT
5	4050-1256	Ready	MARENT	2210-0036	Ready	MARENT
6	4020-0025	Ready	MARENT	2210-0086	HD1	MARENT
7	S03-092K	Ready	MARENT	2220-0110	Ready	MARENT
8	S03-098K	Ready	MARENT	2220-0146	Ready	MARENT
9	S03-123K	Ready	MARENT	2220-0179	Ready	MARENT
10	S03-166K	Ready	MARENT	2220-0197	Ready	MARENT
11	S03-215K	Ready	MARENT	2220-0209	Ready	MARENT
12	S03-275K	Ready	MARENT	2220-0218	Ready	MARENT
13	S2.5-041	Ready	MARENT	2220-0304	Ready	MARENT
14	2200-0275	Ready	MARENT	2220-0348	Ready	MARENT
15	2200-0304	Ready	MARENT	2220-0367	Ready	MARENT
16	2200-0354	Ready	MARENT	2220-0412	Ready	MARENT
17	R13035	Check In	MARENT	2220-0417	Check In	MARENT
18	R13037	Reserved	MARENT	2220-0436	Reserved	MARENT
19	R13045	Reserved	MARENT	2220-0444	Reserved	MARENT
20	R13048	Check In	MARENT	2220-0446	Check In	MARENT
21	R13074	Check In	MARENT	2220-0987	Check In	MARENT
22	R13077	Reserved	MARENT	2235-0078	Reserved	MARENT
23	R13078	Reserved	MARENT	2235-0083	Reserved	MARENT
24	R13081	Reserved	MARENT	2235-0084	Reserved	MARENT
25	R13094	Reserved	MARENT	2235-0085	Reserved	MARENT
26	R14496	Ready	MARENT	2235-0087	Ready	MARENT
27	R14497	Ready	MARENT	2235-0089	Ready	MARENT
28	R14499	Ready	MARENT	2235-0091	Ready	MARENT
29	R14502	Check In	MARENT	2235-0095	Check In	MARENT
30	R14506	Check In	MARENT	2235-0096	Check In	MARENT
31	R14507	Reserved	MARENT	2235-0098	Reserved	MARENT
32	R14508	Reserved	MARENT	2235-0099	Reserved	MARENT
33	R14510	Reserved	MARENT	2235-0101	Reserved	MARENT
34	R14511	Reserved	MARENT	3000-0011	Reserved	MARENT
35	R14512	Reserved	MARENT	3020-0004	Reserved	MARENT
36	R14513	Reserved	MARENT	3050-0010	Reserved	MARENT
37	R14514	Ready	MARENT	3050-0021	Ready	MARENT
38	R14515	Reserved	MARENT	3050-0033	Reserved	MARENT
39	2235-0078	Check In	MARENT	4020-0025	Check In	MARENT
40	2235-0083	Ready	MARENT	4050-0094	Ready	MARENT
41	2235-0084	Reserved	MARENT	4050-1169	Reserved	MARENT
Inventory Comparison

Cell Formulas
Range	Formula
A2:A41	A2	=IFERROR(CELL("contents",Query1[@[Unit Number]]),"")
B2:B41	B2	=IFNA(VLOOKUP(A2,'Current Inventory'!$A$2:$C$1000,2,FALSE),"")
C2:C41	C2	=IFNA(VLOOKUP(A2,'Current Inventory'!$A$2:$C$1000,3,FALSE),"")
D2:D41	D2	=IF('Infor Import'!A2=0,"",(CELL("contents",'Infor Import'!A2)))
E2:E41	E2	=IFNA(VLOOKUP(A2,'Infor Import'!$A$2:$C$1001,2,FALSE),"")
F2:F41	F2	=IFNA(VLOOKUP(A2,'Infor Import'!$A$2:$C$1001,3,FALSE),"")
K2	K2	=FILTER(D2:D1001,(D2:D1001<>"")*(COUNTIF(A2:A1001,D2:D1001)=0))
O2	O2	=FILTER(A2:A1001,(A2:A1001<>"")*(COUNTIF(D2:D1001,A2:A1001)=0))

 

[Eric W]

I couldn't paste your mini-sheet, since it referenced other sheets, but see if this works for you:

ME temp.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	Branch Unit Number	Branch Unit Status	Branch Location	Infor Unit Number	Infor Unit Status	Infor Location					Unit Number Not in Infor	Branch Unit Status	Branch Location		Unit Number Not in Branch	Branch Unit Status	Branch Location		Unit Status Mismatch	Unit Number	Unit Location	Infor Status	Branch Status
2	a	xx	1	c	Ready	10					a	xx	1		i	yy	16			d	11	HD1	Ready
3	b	xx	2	d	HD1	11					b	xx	2		j	yy	17			e	12	Reserved	Check In
4	c	Ready	3	e	Reserved	12					z	xx	9		k	yy	18			g	14	Ready	Reserved
5	d	Ready	4	f	Ready	13									l	yy	19			h	15	Ready	Check In
6	e	Check In	5	g	Ready	14
7	f	Ready	6	h	Ready	15
8	g	Reserved	7	i	yy	16
9	h	Check In	8	j	yy	17
10	z	xx	9	k	yy	18
11				l	yy	19
12
13
Sheet42

Cell Formulas
Range	Formula
K2:M4	K2	=FILTER(A2:C20,(A2:A20<>"")*(COUNTIF(D2:D20,A2:A20)=0))
O2:Q5	O2	=FILTER(D2:F20,(D2:D20<>"")*(COUNTIF(A2:A20,D2:D20)=0))
T2:T5	T2	=FILTER(D2:D20,(D2:D20<>"")*(COUNTIF(A2:A20,D2:D20)*(COUNTIFS(A2:A20,D2:D20,B2:B20,E2:E20)=0)))
U2:U5	U2	=VLOOKUP(T2#,D2:F20,3,0)
V2:V5	V2	=VLOOKUP(T2#,D2:F20,2,0)
W2:W5	W2	=VLOOKUP(T2#,A2:C20,2,0)
Dynamic array formulas.