Page 2 of 2 FirstFirst 12
Results 11 to 12 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. #11
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,269
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

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

    Maybe something like this...


    A
    B
    C
    D
    E
    F
    G
    H
    1
    Country Code
    Shop ID
    Status
    Status
    Country Code
    Shop ID
    2
    4101
    4101001
    SF
    Active
    4101
    4101002
    3
    4101
    4101002
    Discontinued
    Completed
    4101
    4101003
    4
    4101
    4101003
    Active
    Discontinued
    4102
    4102001
    5
    4101
    4101004
    Active
    4102
    4102002
    6
    4102
    4102001
    Completed
    4103
    4103001
    7
    4102
    4102002
    Completed
    4103
    4103004
    8
    4102
    4102003
    Completed
    1201
    1201001
    9
    4102
    4102004
    Completed
    1201
    1201002
    10
    4102
    4102005
    Completed
    4001
    4001001
    11
    4102
    4102006
    Active
    4001
    4001002
    12
    4102
    4102007
    Discontinued
    4004
    4004001
    13
    4102
    4102008
    Active
    4004
    4004002
    14
    4102
    4102009
    Active
    8001
    8001001
    15
    4102
    4102010
    Active
    8001
    8001004
    16
    4102
    4102011
    Scr
    17
    4103
    4103001
    Completed
    18
    4103
    4103002
    SF
    19
    4103
    4103003
    SF
    20
    4103
    4103004
    Completed
    21
    4103
    4103005
    Completed
    22
    4103
    4103006
    SF
    23
    4103
    4103007
    Active
    24
    1201
    1201001
    Completed
    25
    1201
    1201002
    Completed
    26
    1201
    1201003
    Active
    27
    1201
    1201004
    SF
    28
    1201
    1201005
    Active
    29
    4001
    4001001
    Completed
    30
    4001
    4001002
    Completed
    31
    4001
    4001003
    Completed
    32
    4001
    4001004
    Active
    33
    4001
    4001005
    Active
    34
    4004
    4004001
    Completed
    35
    4004
    4004002
    Completed
    36
    4004
    4004003
    Discontinued
    37
    4004
    4004004
    SF
    38
    4004
    4004005
    Completed
    39
    4004
    4004006
    SF
    40
    4004
    4004007
    SF
    41
    4004
    4004008
    SF
    42
    4004
    4004009
    Discontinued
    43
    8001
    8001001
    Completed
    44
    8001
    8001002
    SF
    45
    8001
    8001003
    SF
    46
    8001
    8001004
    Active
    47
    2301
    2301001
    SF
    48
    2301
    2301002
    SF
    49
    2303
    2303001
    SF
    50
    2303
    2303002
    51
    2303
    2303003
    Screened


    Formula in G2 copied down
    =IFERROR(INDEX($A$2:$A$100,AGGREGATE(15,6,(ROW(A$2:A$100)-ROW(A$2)+1)/(ISNUMBER(MATCH(C$2:C$100,E$2:E$4,0))*(COUNTIF(G$1:G1,A$2:A$100)<2)),1)),"")

    Formula in H2 copied down
    =IF(G2="","",INDEX($B$2:$B$100,AGGREGATE(15,6,(ROW(A$2:A$100)-ROW(A$2)+1)/(ISNUMBER(MATCH(C$2:C$100,E$2:E$4,0))*(A$2:A$100=G2)),COUNTIF(G$2:G2,G2))))

    M.

  2. #12
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,260
    Post Thanks / Like
    Mentioned
    72 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

    Great contribution from Marcelo, without a doubt they are the formulas you were looking for.


    However, I wanted to show you another approach with a pivot table. It is easy to build, you just have to drag the fields.




    In the status field you can filter the necessary ones.


    In the shop ID field, you can filter the items you need.

    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
  •