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

1. ## 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. ## 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. ## 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

4. ## 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. ## 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

6. ## 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. ## 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

8. ## 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. ## 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)

10. ## 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.

 A B C D 1 Country Code Shop ID Status Count 2 4101 4101001 SF 0 3 4101 4101002 Discontinued 1 4 4101 4101003 Active 2 5 4101 4101004 Active 3 6 4102 4102001 Completed 1 7 4102 4102002 Completed 2 8 4102 4102003 Completed 3 9 4102 4102004 Completed 4 10 4102 4102005 Completed 5 11 4102 4102006 Active 6 12 4102 4102007 Discontinued 7 13 4102 4102008 Active 8 14 4102 4102009 Active 9 15 4102 4102010 Active 10 16 4102 4102011 Scr 0 17 4103 4103001 Completed 1 18 4103 4103002 SF 0 19 4103 4103003 SF 0 20 4103 4103004 Completed 2

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

Result:

 A B C D 1 Country Code Shop ID Status Count 3 4101 4101002 Discontinued 1 4 4101 4101003 Active 2 6 4102 4102001 Completed 1 7 4102 4102002 Completed 2 17 4103 4103001 Completed 1 20 4103 4103004 Completed 2 24 1201 1201001 Completed 1 25 1201 1201002 Completed 2 29 4001 4001001 Completed 1 30 4001 4001002 Completed 2 34 4004 4004001 Completed 1 35 4004 4004002 Completed 2 43 8001 8001001 Completed 1 46 8001 8001004 Active 2

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