Workbook not finding duplicated and missing entries

ghrek

Board Regular
Joined
Jul 29, 2005
Messages
95
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
 

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,302
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,302
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
30,311
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
30,311
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

Forum statistics

Threads
1,077,977
Messages
5,337,509
Members
399,154
Latest member
gavlink

Some videos you may like

This Week's Hot Topics

Top