Results 1 to 7 of 7

Thread: Unique List

  1. #1
    Acolyte Tech's Avatar
    Join Date
    Jan 2017
    Posts
    23
    Articles
    0
    Excel Version
    2010

    Unique List



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi,

    Can someone derive formulas to fill list based on ID Number please?

    Thank you

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    Do you mean in O14:

    =IFERROR(IF($N14="","",INDEX(Table146[HRS],MATCH(1,INDEX((Table146[[INV]:[INV]]=$H$3)*(Table146[[CODE]:[CODE]]=$N14),0),0))),"")

    copied down and across the grid.
    Last edited by NBVC; 2019-09-04 at 12:47 PM. Reason: Updated formula to include Invoice #


  3. #3
    Acolyte Tech's Avatar
    Join Date
    Jan 2017
    Posts
    23
    Articles
    0
    Excel Version
    2010
    Thank you for replying but I do mean from N14:Q27 thanks. I put the data that matches the ID 106.

  4. #4
    Acolyte Tech's Avatar
    Join Date
    Jan 2017
    Posts
    23
    Articles
    0
    Excel Version
    2010
    Could someone please check this solution?

  5. #5
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    Ok, in N14 enter this array formula copied down:

    =IFERROR(INDEX(Table146[CODE],SMALL(IF(Table146[INV]=$H$3,ROW(Table146[INV])-ROW(B$14)+1),ROWS(N$14:N14))),"")

    confirm with CTRL+SHIFT+ENTER.

    The rest of the table uses previous formula


  6. #6
    Acolyte Tech's Avatar
    Join Date
    Jan 2017
    Posts
    23
    Articles
    0
    Excel Version
    2010
    Thank you kindly for your help.

  7. #7
    Acolyte caabyyc's Avatar
    Join Date
    Dec 2014
    Location
    Burnaby/Calgary
    Posts
    26
    Articles
    0
    Excel Version
    2016
    OR

    N14=IFERROR(HLOOKUP(N$13,Table146[[#All],[CODE]:[UOM]],SMALL(IF(Table146[INV]=$H$3,ROW(Table146[INV])-12,"/"),ROW(A1)),),"")


    CONTRO,SHIFT,ENTER THNE COPY TO Q27

Posting Permissions

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