Workbook not finding duplicated and missing entries

ghrek

Board Regular
Joined
Jul 29, 2005
Messages
115
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,328
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,328
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
33,379
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
33,379
Office Version
365
Platform
Windows
Glad we could help & thanks for the feedback
 

Forum statistics

Threads
1,084,743
Messages
5,379,565
Members
401,613
Latest member
KarimK9

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top