Results 1 to 7 of 7

Thread: Workbook not finding duplicated and missing entries

  1. #1
    Board Regular
    Join Date
    Jul 2005
    Posts
    74
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Workbook not finding duplicated and missing entries

    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_Atm...SkPf2?e=5x6ntD

  2. #2
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,260
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Workbook not finding duplicated and missing entries

    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?

  3. #3
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,260
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Workbook not finding duplicated and missing entries

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

  4. #4
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,260
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Workbook not finding duplicated and missing entries

    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 by Special-K99; Sep 19th, 2019 at 07:27 AM.

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,177
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Workbook not finding duplicated and missing entries

    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",""))
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  6. #6
    Board Regular
    Join Date
    Jul 2005
    Posts
    74
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Workbook not finding duplicated and missing entries

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

    it worked . A big big thanks

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,177
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Workbook not finding duplicated and missing entries

    Glad we could help & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •