Workbook not finding duplicated and missing entries

[ghrek]

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]

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]

I think you mean cvolumn F should say MISSING, not column G.

 

[Special-K99]

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.

 

[Fluff]

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",""))

 

[ghrek]

Fluff.... YOU ARE A LEGEND...

it worked . A big big thanks

 

[Fluff]

Glad we could help & thanks for the feedback