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>
 

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
3,816
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
3,816
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
3,816
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,588
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
8,879
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.
 

Forum statistics

Threads
1,082,243
Messages
5,363,972
Members
400,772
Latest member
solbebe

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top