Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: How to extract only first 2 items, from different groups in the same data, meeting certain criteria
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default How to extract only first 2 items, from different groups in the same data, meeting certain criteria

    I have a table as below. The goal is to extract only first two shop i.d.s from each country having the status either "Active" or "Completed" or "Discontinued". If there are no shops in the country with that status do not extract. If only one than extract only one. Finally the answer should be in a table showing all the responses with above mentioned criteria.
    I tried using AGGREGATE with formula 15 and option 6. in the [K] argument i use MOD. but the problem is it does not stop after 2 extractions. if the country has more shops with the above mentioned status it start again and repeat till all the shops in that country are analyzed. My formula looks like this

    INDEX(Sheet1!$B$2:$B$265,AGGREGATE(15,6,(ROW(Sheet1!$A$2:$A$265)-ROW(Sheet1!$A$2)+1)/((Sheet1!$A$2:$A$265=Sheet1!A2)*(Sheet1!$C$2:$C$265<>"SF")),ROUND((MOD(ROW(),2)+0.5),0)))

    Country Code Shop ID Status
    4101 4101001 SF
    4101 4101002 Discontinued
    4101 4101003 Active
    4101 4101004 Active
    4102 4102001 Completed
    4102 4102002 Completed
    4102 4102003 Completed
    4102 4102004 Completed
    4102 4102005 Completed
    4102 4102006 Active
    4102 4102007 Discontinued
    4102 4102008 Active
    4102 4102009 Active
    4102 4102010 Active
    4102 4102011 Scr
    4103 4103001 Completed
    4103 4103002 SF
    4103 4103003 SF
    4103 4103004 Completed
    4103 4103005 Completed
    4103 4103006 SF
    4103 4103007 Active
    1201 1201001 Completed
    1201 1201002 Completed
    1201 1201003 Active
    1201 1201004 SF
    1201 1201005 Active
    4001 4001001 Completed
    4001 4001002 Completed
    4001 4001003 Completed
    4001 4001004 Active
    4001 4001005 Active
    4004 4004001 Completed
    4004 4004002 Completed
    4004 4004003 Discontinued
    4004 4004004 SF
    4004 4004005 Completed
    4004 4004006 SF
    4004 4004007 SF
    4004 4004008 SF
    4004 4004009 Discontinued
    8001 8001001 Completed
    8001 8001002 SF
    8001 8001003 SF
    8001 8001004 Active
    2301 2301001 SF
    2301 2301002 SF
    2303 2303001 SF
    2303 2303002
    2303 2303003 Screened

  2. #2
    New Member
    Join Date
    Aug 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to extract only first 2 items, from different groups in the same data, meeting certain criteria

    please help to extract only first two shop ids with the status mentioned above from this table

  3. #3
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,818
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: How to extract only first 2 items, from different groups in the same data, meeting certain criteria

    something like this?

    Country Code Shop ID
    4101
    4101002
    4101
    4101003
    4102
    4102001
    4102
    4102002
    4103
    4103001
    4103
    4103004
    1201
    1201001
    1201
    1201002
    4001
    4001001
    4001
    4001002
    4004
    4004001
    4004
    4004002
    8001
    8001001
    8001
    8001004
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  4. #4
    New Member
    Join Date
    Aug 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to extract only first 2 items, from different groups in the same data, meeting certain criteria

    yes exactly this the goal Sandy. How did you get here???

  5. #5
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,818
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: How to extract only first 2 items, from different groups in the same data, meeting certain criteria

    with PowerQuery aka Get&Transform

    Code:
    // Table1
    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        Filter = Table.SelectRows(Source, each ([Status] = "Active" or [Status] = "Completed" or [Status] = "Discontinued")),
        Group = Table.Group(Filter, {"Country Code"}, {{"Count", each _, type table}}),
        First2 = Table.AddColumn(Group, "First2", each Table.FirstN([Count],2)),
        Expand = Table.ExpandTableColumn(First2, "First2", {"Shop ID"}, {"Shop ID"})
    in
        Expand
    and no, this is not vba
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  6. #6
    New Member
    Join Date
    Aug 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to extract only first 2 items, from different groups in the same data, meeting certain criteria

    looks good. thanks. However I don't know PowerQuery. I was looking for more of an answer using formulas if possible.

  7. #7
    Board Regular sandy666's Avatar
    Join Date
    Oct 2015
    Posts
    2,818
    Post Thanks / Like
    Mentioned
    32 Post(s)
    Tagged
    1 Thread(s)

    Cool Re: How to extract only first 2 items, from different groups in the same data, meeting certain criteria

    maybe it's time to learn PowerQuery?

    anyway, good luck with formula

    Have a nice day
    I know you know but I forgot my Crystal Ball and don't know what you know



    In the first post, show the type of machine (PC / Mac) and the Office version you are working on
    impossible things we do on the spot. for miracles you need to wait for a while

  8. #8
    New Member
    Join Date
    Aug 2019
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: How to extract only first 2 items, from different groups in the same data, meeting certain criteria

    I sure will. Thanks Sandy. But by the time I learn PowerQuery I will wait someone else to also help me on this challenge. Thanks again

  9. #9
    Board Regular lrobbo314's Avatar
    Join Date
    Jul 2008
    Location
    California
    Posts
    2,403
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Default Re: How to extract only first 2 items, from different groups in the same data, meeting certain criteria

    I made a Unique list of your country codes. Just copy the country code column, paste, then remove duplicates. Then set up the formula like below. It's an array formula so Ctrl+Shift+Enter.

    LEGO HTML
    FGH
    1Country Code12
    2410141010024101003
    3410241020014102002
    4410341030014103004
    5120112010011201002
    6400140010014001002
    7400440040014004002
    8800180010018001004
    92301Error 2036Error 2036
    102303Error 2036Error 2036


    Formulas
    G2=SMALL(IF(($A$2:$A$51=$F2)*(($C$2:$C$51="Active")+($C$2:$C$51="Completed")+($C$2:$C$51="Discontinued"))<>0,$B$2:$B$51),G$1)
    H2=SMALL(IF(($A$2:$A$51=$F2)*(($C$2:$C$51="Active")+($C$2:$C$51="Completed")+($C$2:$C$51="Discontinued"))<>0,$B$2:$B$51),H$1)
    G3=SMALL(IF(($A$2:$A$51=$F3)*(($C$2:$C$51="Active")+($C$2:$C$51="Completed")+($C$2:$C$51="Discontinued"))<>0,$B$2:$B$51),G$1)
    H3=SMALL(IF(($A$2:$A$51=$F3)*(($C$2:$C$51="Active")+($C$2:$C$51="Completed")+($C$2:$C$51="Discontinued"))<>0,$B$2:$B$51),H$1)
    G4=SMALL(IF(($A$2:$A$51=$F4)*(($C$2:$C$51="Active")+($C$2:$C$51="Completed")+($C$2:$C$51="Discontinued"))<>0,$B$2:$B$51),G$1)
    H4=SMALL(IF(($A$2:$A$51=$F4)*(($C$2:$C$51="Active")+($C$2:$C$51="Completed")+($C$2:$C$51="Discontinued"))<>0,$B$2:$B$51),H$1)
    G5=SMALL(IF(($A$2:$A$51=$F5)*(($C$2:$C$51="Active")+($C$2:$C$51="Completed")+($C$2:$C$51="Discontinued"))<>0,$B$2:$B$51),G$1)
    H5=SMALL(IF(($A$2:$A$51=$F5)*(($C$2:$C$51="Active")+($C$2:$C$51="Completed")+($C$2:$C$51="Discontinued"))<>0,$B$2:$B$51),H$1)
    G6=SMALL(IF(($A$2:$A$51=$F6)*(($C$2:$C$51="Active")+($C$2:$C$51="Completed")+($C$2:$C$51="Discontinued"))<>0,$B$2:$B$51),G$1)
    H6=SMALL(IF(($A$2:$A$51=$F6)*(($C$2:$C$51="Active")+($C$2:$C$51="Completed")+($C$2:$C$51="Discontinued"))<>0,$B$2:$B$51),H$1)
    G7=SMALL(IF(($A$2:$A$51=$F7)*(($C$2:$C$51="Active")+($C$2:$C$51="Completed")+($C$2:$C$51="Discontinued"))<>0,$B$2:$B$51),G$1)
    H7=SMALL(IF(($A$2:$A$51=$F7)*(($C$2:$C$51="Active")+($C$2:$C$51="Completed")+($C$2:$C$51="Discontinued"))<>0,$B$2:$B$51),H$1)
    G8=SMALL(IF(($A$2:$A$51=$F8)*(($C$2:$C$51="Active")+($C$2:$C$51="Completed")+($C$2:$C$51="Discontinued"))<>0,$B$2:$B$51),G$1)
    H8=SMALL(IF(($A$2:$A$51=$F8)*(($C$2:$C$51="Active")+($C$2:$C$51="Completed")+($C$2:$C$51="Discontinued"))<>0,$B$2:$B$51),H$1)
    G9=SMALL(IF(($A$2:$A$51=$F9)*(($C$2:$C$51="Active")+($C$2:$C$51="Completed")+($C$2:$C$51="Discontinued"))<>0,$B$2:$B$51),G$1)
    H9=SMALL(IF(($A$2:$A$51=$F9)*(($C$2:$C$51="Active")+($C$2:$C$51="Completed")+($C$2:$C$51="Discontinued"))<>0,$B$2:$B$51),H$1)
    G10=SMALL(IF(($A$2:$A$51=$F10)*(($C$2:$C$51="Active")+($C$2:$C$51="Completed")+($C$2:$C$51="Discontinued"))<>0,$B$2:$B$51),G$1)
    H10=SMALL(IF(($A$2:$A$51=$F10)*(($C$2:$C$51="Active")+($C$2:$C$51="Completed")+($C$2:$C$51="Discontinued"))<>0,$B$2:$B$51),H$1)
    Last edited by lrobbo314; Aug 18th, 2019 at 08:42 PM.
    To add code to a workbook. Hit Alt+F11. Hit Alt+I+M to insert new module. Then paste code.
    Array formulas must be entered by hitting Ctrl+Shift+Enter.

    We can't solve problems by using the same kind of thinking we used when we created them.

    Imagination is more important than knowledge.

  10. #10
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,211
    Post Thanks / Like
    Mentioned
    68 Post(s)
    Tagged
    14 Thread(s)

    Default Re: How to extract only first 2 items, from different groups in the same data, meeting certain criteria

    You can use the following formula and filter column D by values ​​1 and 2.

     ABCD
    1Country CodeShop IDStatusCount
    241014101001SF0
    341014101002Discontinued1
    441014101003Active2
    541014101004Active3
    641024102001Completed1
    741024102002Completed2
    841024102003Completed3
    941024102004Completed4
    1041024102005Completed5
    1141024102006Active6
    1241024102007Discontinued7
    1341024102008Active8
    1441024102009Active9
    1541024102010Active10
    1641024102011Scr0
    1741034103001Completed1
    1841034103002SF0
    1941034103003SF0
    2041034103004Completed2

    CellFormula
    D2=IF(OR(C2={"Completed","Active","Discontinued"}),SUM(COUNTIFS($A$2:A2,A2,$C$2:C2,{"Completed","Active","Discontinued"})) ,0)



    Result:


     ABCD
    1Country CodeShop IDStatusCount
    341014101002Discontinued1
    441014101003Active2
    641024102001Completed1
    741024102002Completed2
    1741034103001Completed1
    2041034103004Completed2
    2412011201001Completed1
    2512011201002Completed2
    2940014001001Completed1
    3040014001002Completed2
    3440044004001Completed1
    3540044004002Completed2
    4380018001001Completed1
    4680018001004Active2



    -----------------------
    Or take that auxiliary column D to generate the formula and bring the data of values ​​1 and 2.
    Regards Dante Amor

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
  •