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

Navtir

New Member
Joined
Aug 18, 2019
Messages
5
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

<tbody>
</tbody>
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
please help to extract only first two shop ids with the status mentioned above from this table
 
Upvote 0
something like this?

Country CodeShop 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​
 
Upvote 0
with PowerQuery aka Get&Transform

Code:
[SIZE=1]// 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[/SIZE]

and no, this is not vba ;)
 
Upvote 0
looks good. thanks. However I don't know PowerQuery. :( I was looking for more of an answer using formulas if possible.
 
Upvote 0
maybe it's time to learn PowerQuery? :biggrin:

anyway, good luck with formula

Have a nice day
 
Upvote 0
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
 
Upvote 0
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.

<style type="text/css">table.LEGO-table {font-size: 12px;border: 1px solid #CCC;font-family: Arial, Helvetica, sans-serif;border-collapse: collapse;}.LEGO-table td {padding: 4px;margin: 3px;border: 0.1px solid #000000;}.LEGO-table th {background-color: #70AD47;color: #FFF;font-weight: bold;border-collapse: separate;border: 0.1px solid #000000;}td.blank {background-color: #e6e6e6;text-align: center;}.LEGO-table tr:nth-child(even) {background-color: #ababab;}</style><table class="LEGO-table"><caption>LEGO HTML</caption><thead><tr class="LEGO-firstrow"><tr><td class="blank"></td><td class="blank">F</td><td class="blank">G</td><td class="blank">H</td></tr><tr><td class="blank">1</td><th>Country Code</th><th>1</th><th>2</th></thead><tbody><tr><td class="blank">2</td><td>4101</td><td>4101002</td><td>4101003</td></tr><tr><td class="blank">3</td><td>4102</td><td>4102001</td><td>4102002</td></tr><tr><td class="blank">4</td><td>4103</td><td>4103001</td><td>4103004</td></tr><tr><td class="blank">5</td><td>1201</td><td>1201001</td><td>1201002</td></tr><tr><td class="blank">6</td><td>4001</td><td>4001001</td><td>4001002</td></tr><tr><td class="blank">7</td><td>4004</td><td>4004001</td><td>4004002</td></tr><tr><td class="blank">8</td><td>8001</td><td>8001001</td><td>8001004</td></tr><tr><td class="blank">9</td><td>2301</td><td>Error 2036</td><td>Error 2036</td></tr><tr><td class="blank">10</td><td>2303</td><td>Error 2036</td><td>Error 2036</td></tr></tbody></table></br></br><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00; color:#000000; "><tbody><tr><td><b>Formulas</b></td></tr><tr><td><table class="LEGO-table"><tr><td>G2</td><td>=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)</td></tr><tr><td>H2</td><td>=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)</td></tr><tr><td>G3</td><td>=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)</td></tr><tr><td>H3</td><td>=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)</td></tr><tr><td>G4</td><td>=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)</td></tr><tr><td>H4</td><td>=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)</td></tr><tr><td>G5</td><td>=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)</td></tr><tr><td>H5</td><td>=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)</td></tr><tr><td>G6</td><td>=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)</td></tr><tr><td>H6</td><td>=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)</td></tr><tr><td>G7</td><td>=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)</td></tr><tr><td>H7</td><td>=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)</td></tr><tr><td>G8</td><td>=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)</td></tr><tr><td>H8</td><td>=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)</td></tr><tr><td>G9</td><td>=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)</td></tr><tr><td>H9</td><td>=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)</td></tr><tr><td>G10</td><td>=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)</td></tr><tr><td>H10</td><td>=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)</td></tr></table></tbody></table>
 
Last edited:
Upvote 0
You can use the following formula and filter column D by values ​​1 and 2.

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:107.41px;" /><col style="width:97.9px;" /><col style="width:158.73px;" /><col style="width:150.18px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Country Code</td><td >Shop ID</td><td >Status</td><td >Count</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">4101</td><td style="text-align:right; ">4101001</td><td >SF</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">4101</td><td style="text-align:right; ">4101002</td><td >Discontinued</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">4101</td><td style="text-align:right; ">4101003</td><td >Active</td><td style="text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">4101</td><td style="text-align:right; ">4101004</td><td >Active</td><td style="text-align:right; ">3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">4102</td><td style="text-align:right; ">4102001</td><td >Completed</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">4102</td><td style="text-align:right; ">4102002</td><td >Completed</td><td style="text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:right; ">4102</td><td style="text-align:right; ">4102003</td><td >Completed</td><td style="text-align:right; ">3</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:right; ">4102</td><td style="text-align:right; ">4102004</td><td >Completed</td><td style="text-align:right; ">4</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:right; ">4102</td><td style="text-align:right; ">4102005</td><td >Completed</td><td style="text-align:right; ">5</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="text-align:right; ">4102</td><td style="text-align:right; ">4102006</td><td >Active</td><td style="text-align:right; ">6</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="text-align:right; ">4102</td><td style="text-align:right; ">4102007</td><td >Discontinued</td><td style="text-align:right; ">7</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="text-align:right; ">4102</td><td style="text-align:right; ">4102008</td><td >Active</td><td style="text-align:right; ">8</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="text-align:right; ">4102</td><td style="text-align:right; ">4102009</td><td >Active</td><td style="text-align:right; ">9</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >15</td><td style="text-align:right; ">4102</td><td style="text-align:right; ">4102010</td><td >Active</td><td style="text-align:right; ">10</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="text-align:right; ">4102</td><td style="text-align:right; ">4102011</td><td >Scr</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">4103</td><td style="text-align:right; ">4103001</td><td >Completed</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >18</td><td style="text-align:right; ">4103</td><td style="text-align:right; ">4103002</td><td >SF</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td style="text-align:right; ">4103</td><td style="text-align:right; ">4103003</td><td >SF</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="text-align:right; ">4103</td><td style="text-align:right; ">4103004</td><td >Completed</td><td style="text-align:right; ">2</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >D2</td><td >=IF(OR(C2={"Completed","Active","Discontinued"}),SUM(COUNTIFS($A$2:A2,A2,$C$2:C2,{"Completed","Active","Discontinued"})),0)</td></tr></table></td></tr></table>


Result:


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:107.41px;" /><col style="width:97.9px;" /><col style="width:158.73px;" /><col style="width:150.18px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Country Code</td><td >Shop ID</td><td >Status</td><td >Count</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">4101</td><td style="text-align:right; ">4101002</td><td >Discontinued</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">4101</td><td style="text-align:right; ">4101003</td><td >Active</td><td style="text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:right; ">4102</td><td style="text-align:right; ">4102001</td><td >Completed</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:right; ">4102</td><td style="text-align:right; ">4102002</td><td >Completed</td><td style="text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >17</td><td style="text-align:right; ">4103</td><td style="text-align:right; ">4103001</td><td >Completed</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="text-align:right; ">4103</td><td style="text-align:right; ">4103004</td><td >Completed</td><td style="text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td style="text-align:right; ">1201</td><td style="text-align:right; ">1201001</td><td >Completed</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td style="text-align:right; ">1201</td><td style="text-align:right; ">1201002</td><td >Completed</td><td style="text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >29</td><td style="text-align:right; ">4001</td><td style="text-align:right; ">4001001</td><td >Completed</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >30</td><td style="text-align:right; ">4001</td><td style="text-align:right; ">4001002</td><td >Completed</td><td style="text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >34</td><td style="text-align:right; ">4004</td><td style="text-align:right; ">4004001</td><td >Completed</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >35</td><td style="text-align:right; ">4004</td><td style="text-align:right; ">4004002</td><td >Completed</td><td style="text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >43</td><td style="text-align:right; ">8001</td><td style="text-align:right; ">8001001</td><td >Completed</td><td style="text-align:right; ">1</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >46</td><td style="text-align:right; ">8001</td><td style="text-align:right; ">8001004</td><td >Active</td><td style="text-align:right; ">2</td></tr></table>


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

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top