Finding Mismatches Cont.

BijanBorazjani

New Member
Joined
Oct 22, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
If the unit number is in both Data Sets, then secondary data needs to be checked. How would I match data from Set 2 to Set 1 and check the next columns for matching data as well. If there would be a mismatch, i need it populate the unit number, and both statuses in their respective columns.


OKC Inventory Equipment 10-22-21.xlsm
ABCDEFGHIJKLMNOPQRSTUVW
1Branch Unit NumberBranch Unit StatusBranch LocationInfor Unit NumberInfor Unit StatusInfor LocationUnit Number Not In InforBranch Unit StatusBranch LocationUnit Number Not in BranchInfor Unit StatusInfor LocationUnit Status MismatchUnit NumberUnit LocationInfor StatusBranch Status
24050-0094ReadyMARENT2200-0275ReadyMARENT6666666Blown UpMARENTT53NCF17On RentMARENT
34050-1169ReadyMARENT2200-0304ReadyMARENT1111111  
44050-1189ReadyMARENT2200-0354ReadyMARENT  
54050-1256ReadyMARENT2210-0036ReadyMARENT  
64020-0025ReadyMARENT2210-0086HD1MARENT  
7S03-092KReadyMARENT2220-0110ReadyMARENT  
8S03-098KReadyMARENT2220-0146ReadyMARENT  
9S03-123KReadyMARENT2220-0179ReadyMARENT  
10S03-166KReadyMARENT2220-0197ReadyMARENT  
11S03-215KReadyMARENT2220-0209ReadyMARENT  
12S03-275KReadyMARENT2220-0218ReadyMARENT  
13S2.5-041ReadyMARENT2220-0304ReadyMARENT  
142200-0275ReadyMARENT2220-0348ReadyMARENT  
152200-0304ReadyMARENT2220-0367ReadyMARENT  
162200-0354ReadyMARENT2220-0412ReadyMARENT  
17R13035Check InMARENT2220-0417Check InMARENT  
18R13037ReservedMARENT2220-0436ReservedMARENT  
19R13045ReservedMARENT2220-0444ReservedMARENT  
20R13048Check InMARENT2220-0446Check InMARENT  
21R13074Check InMARENT2220-0987Check InMARENT  
22R13077ReservedMARENT2235-0078ReservedMARENT  
23R13078ReservedMARENT2235-0083ReservedMARENT  
24R13081ReservedMARENT2235-0084ReservedMARENT  
25R13094ReservedMARENT2235-0085ReservedMARENT  
26R14496ReadyMARENT2235-0087ReadyMARENT  
27R14497ReadyMARENT2235-0089ReadyMARENT  
28R14499ReadyMARENT2235-0091ReadyMARENT  
29R14502Check InMARENT2235-0095Check InMARENT  
30R14506Check InMARENT2235-0096Check InMARENT  
31R14507ReservedMARENT2235-0098ReservedMARENT  
32R14508ReservedMARENT2235-0099ReservedMARENT  
33R14510ReservedMARENT2235-0101ReservedMARENT  
34R14511ReservedMARENT3000-0011ReservedMARENT  
35R14512ReservedMARENT3020-0004ReservedMARENT  
36R14513ReservedMARENT3050-0010ReservedMARENT  
37R14514ReadyMARENT3050-0021ReadyMARENT  
38R14515ReservedMARENT3050-0033ReservedMARENT  
392235-0078Check InMARENT4020-0025Check InMARENT  
402235-0083ReadyMARENT4050-0094ReadyMARENT  
Inventory Comparison
Cell Formulas
RangeFormula
A2:A40A2=IFERROR(CELL("contents",Query1[@[Unit Number]]),"")
B2:B40B2=IFNA(VLOOKUP(A2,'Current Inventory'!$A$2:$C$1001,2,FALSE),"")
C2:C40C2=IFNA(VLOOKUP(A2,'Current Inventory'!$A$2:$C$1001,3,FALSE),"")
D2:D40D2=IF('Infor Import'!A2=0,"",(CELL("contents",'Infor Import'!A2)))
E2:E40E2=IFNA(VLOOKUP(A2,'Infor Import'!$A$2:$C$1001,2,FALSE),"")
F2:F40F2=IFNA(VLOOKUP(A2,'Infor Import'!$A$2:$C$1001,3,FALSE),"")
K2K2=IFERROR(FILTER(A2:A1001,(A2:A1001<>"")*(COUNTIF(D2:D1001,A2:A1001)=0)),"")
L2L2=IFNA(VLOOKUP(K2,$A$2:$C$1001,2,FALSE),"")
M2M2=IFNA(VLOOKUP(K2,$A$2:$C$1001,3,FALSE),"")
O2:O3O2=FILTER(D2:D1001,(D2:D1001<>"")*(COUNTIF(A2:A1001,D2:D1001)=0))
P2:P40P2=IFNA(VLOOKUP(O2,$D$2:$F$1001,2,FALSE),"")
Q2:Q40Q2=IFNA(VLOOKUP(O2,$D$2:$F$1001,3,FALSE),"")
Dynamic array formulas.
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,034
Messages
5,767,756
Members
425,431
Latest member
Sayson

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top