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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

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,673
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,673
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,673
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,687
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,194
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,089,475
Messages
5,408,462
Members
403,208
Latest member
JFoley182

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top