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>
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Navtir

New Member
Joined
Aug 18, 2019
Messages
5
please help to extract only first two shop ids with the status mentioned above from this table
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
4,723
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​
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
4,723
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 ;)
 

Navtir

New Member
Joined
Aug 18, 2019
Messages
5
looks good. thanks. However I don't know PowerQuery. :( I was looking for more of an answer using formulas if possible.
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
4,723
maybe it's time to learn PowerQuery? :biggrin:

anyway, good luck with formula

Have a nice day
 

Navtir

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

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,698
Office Version
365, 2019, 2016
Platform
Windows
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:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,251
Office Version
2007
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,090,239
Messages
5,413,254
Members
403,468
Latest member
GRamos

This Week's Hot Topics

Top