Workbook not finding duplicated and missing entries

ghrek

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

Forum statistics

Threads
1,078,447
Messages
5,340,349
Members
399,371
Latest member
wilbot

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top