Workbook not finding duplicated and missing entries

ghrek

Board Regular
Joined
Jul 29, 2005
Messages
145
Hi

I have this workbook that is supposed to compare data in columns B-F against data in columns I-L and if missing from columns I-L it supposed to say MISSING in affected row in column G.

It then supposed to look in columns I-L for duplicated entries and then if duplicated it marks one of the duplicated entries up as DUPLICATE in column J .

It seems to be missing lots of them. I have on a manual calculation as its such a big file.

Can anyone take a look for me please.

Thanks

File is here.

https://1drv.ms/x/s!AlFxNGHiZueY_AtmI44lV2sSkPf2?e=5x6ntD
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,341
How does it say "MISSING", Column G contains no formulas to do this and I cant see any VBA that would put the value there either?
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,341
You need to include the 0 at the end of the MATCH, see below

in F2
=IF([@Source]="","",IF(ISNA(MATCH([@ID]&[@Field2]&[@Date]&[@Value],INDEX($I$2:$I$9999&$J$2:$J$9999&$K$2:$K$9999&$L$2:$L$9999,0),0)),"Missing",""))

Not sure you can do it that way though, I still get MISSING when the data is present.
It produces a #VALUE error with a normal MATCH, maybe it's different for tables, am not expert on tables properties.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,012
Office Version
365
Platform
Windows
Impossible to test this as your table is empty, but try
=IF([@Source]="","",IF(ISNA(MATCH([@ID]&"|"&[@Field2]&"|"&[@Date]&"|"&[@Value],INDEX($I$2:$I$9999&"|"&$J$2:$J$9999&"|"&$K$2:$K$9999&"|"&$L$2:$L$9999,0),0)),"Missing",""))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,012
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,090,234
Messages
5,413,225
Members
403,468
Latest member
adenard

This Week's Hot Topics

Top