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

N

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)))

[TABLE="width: 0"]
<tbody>[TR]
[TD]Country Code
[/TD]
[TD]Shop ID
[/TD]
[TD]Status
[/TD]
[/TR]
[TR]
[TD]4101
[/TD]
[TD]4101001
[/TD]
[TD]SF
[/TD]
[/TR]
[TR]
[TD]4101
[/TD]
[TD]4101002
[/TD]
[TD]Discontinued
[/TD]
[/TR]
[TR]
[TD]4101
[/TD]
[TD]4101003
[/TD]
[TD]Active
[/TD]
[/TR]
[TR]
[TD]4101
[/TD]
[TD]4101004
[/TD]
[TD]Active
[/TD]
[/TR]
[TR]
[TD]4102
[/TD]
[TD]4102001
[/TD]
[TD]Completed
[/TD]
[/TR]
[TR]
[TD]4102
[/TD]
[TD]4102002
[/TD]
[TD]Completed
[/TD]
[/TR]
[TR]
[TD]4102
[/TD]
[TD]4102003
[/TD]
[TD]Completed
[/TD]
[/TR]
[TR]
[TD]4102
[/TD]
[TD]4102004
[/TD]
[TD]Completed
[/TD]
[/TR]
[TR]
[TD]4102
[/TD]
[TD]4102005
[/TD]
[TD]Completed
[/TD]
[/TR]
[TR]
[TD]4102
[/TD]
[TD]4102006
[/TD]
[TD]Active
[/TD]
[/TR]
[TR]
[TD]4102
[/TD]
[TD]4102007
[/TD]
[TD]Discontinued
[/TD]
[/TR]
[TR]
[TD]4102
[/TD]
[TD]4102008
[/TD]
[TD]Active
[/TD]
[/TR]
[TR]
[TD]4102
[/TD]
[TD]4102009
[/TD]
[TD]Active
[/TD]
[/TR]
[TR]
[TD]4102
[/TD]
[TD]4102010
[/TD]
[TD]Active
[/TD]
[/TR]
[TR]
[TD]4102
[/TD]
[TD]4102011
[/TD]
[TD]Scr
[/TD]
[/TR]
[TR]
[TD]4103
[/TD]
[TD]4103001
[/TD]
[TD]Completed
[/TD]
[/TR]
[TR]
[TD]4103
[/TD]
[TD]4103002
[/TD]
[TD]SF
[/TD]
[/TR]
[TR]
[TD]4103
[/TD]
[TD]4103003
[/TD]
[TD]SF
[/TD]
[/TR]
[TR]
[TD]4103
[/TD]
[TD]4103004
[/TD]
[TD]Completed
[/TD]
[/TR]
[TR]
[TD]4103
[/TD]
[TD]4103005
[/TD]
[TD]Completed
[/TD]
[/TR]
[TR]
[TD]4103
[/TD]
[TD]4103006
[/TD]
[TD]SF
[/TD]
[/TR]
[TR]
[TD]4103
[/TD]
[TD]4103007
[/TD]
[TD]Active
[/TD]
[/TR]
[TR]
[TD]1201
[/TD]
[TD]1201001
[/TD]
[TD]Completed
[/TD]
[/TR]
[TR]
[TD]1201
[/TD]
[TD]1201002
[/TD]
[TD]Completed
[/TD]
[/TR]
[TR]
[TD]1201
[/TD]
[TD]1201003
[/TD]
[TD]Active
[/TD]
[/TR]
[TR]
[TD]1201
[/TD]
[TD]1201004
[/TD]
[TD]SF
[/TD]
[/TR]
[TR]
[TD]1201
[/TD]
[TD]1201005
[/TD]
[TD]Active
[/TD]
[/TR]
[TR]
[TD]4001
[/TD]
[TD]4001001
[/TD]
[TD]Completed
[/TD]
[/TR]
[TR]
[TD]4001
[/TD]
[TD]4001002
[/TD]
[TD]Completed
[/TD]
[/TR]
[TR]
[TD]4001
[/TD]
[TD]4001003
[/TD]
[TD]Completed
[/TD]
[/TR]
[TR]
[TD]4001
[/TD]
[TD]4001004
[/TD]
[TD]Active
[/TD]
[/TR]
[TR]
[TD]4001
[/TD]
[TD]4001005
[/TD]
[TD]Active
[/TD]
[/TR]
[TR]
[TD]4004
[/TD]
[TD]4004001
[/TD]
[TD]Completed
[/TD]
[/TR]
[TR]
[TD]4004
[/TD]
[TD]4004002
[/TD]
[TD]Completed
[/TD]
[/TR]
[TR]
[TD]4004
[/TD]
[TD]4004003
[/TD]
[TD]Discontinued
[/TD]
[/TR]
[TR]
[TD]4004
[/TD]
[TD]4004004
[/TD]
[TD]SF
[/TD]
[/TR]
[TR]
[TD]4004
[/TD]
[TD]4004005
[/TD]
[TD]Completed
[/TD]
[/TR]
[TR]
[TD]4004
[/TD]
[TD]4004006
[/TD]
[TD]SF
[/TD]
[/TR]
[TR]
[TD]4004
[/TD]
[TD]4004007
[/TD]
[TD]SF
[/TD]
[/TR]
[TR]
[TD]4004
[/TD]
[TD]4004008
[/TD]
[TD]SF
[/TD]
[/TR]
[TR]
[TD]4004
[/TD]
[TD]4004009
[/TD]
[TD]Discontinued
[/TD]
[/TR]
[TR]
[TD]8001
[/TD]
[TD]8001001
[/TD]
[TD]Completed
[/TD]
[/TR]
[TR]
[TD]8001
[/TD]
[TD]8001002
[/TD]
[TD]SF
[/TD]
[/TR]
[TR]
[TD]8001
[/TD]
[TD]8001003
[/TD]
[TD]SF
[/TD]
[/TR]
[TR]
[TD]8001
[/TD]
[TD]8001004
[/TD]
[TD]Active
[/TD]
[/TR]
[TR]
[TD]2301
[/TD]
[TD]2301001
[/TD]
[TD]SF
[/TD]
[/TR]
[TR]
[TD]2301
[/TD]
[TD]2301002
[/TD]
[TD]SF
[/TD]
[/TR]
[TR]
[TD]2303
[/TD]
[TD]2303001
[/TD]
[TD]SF
[/TD]
[/TR]
[TR]
[TD]2303
[/TD]
[TD]2303002
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]2303
[/TD]
[TD]2303003
[/TD]
[TD]Screened
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
please help to extract only first two shop ids with the status mentioned above from this table
 
Upvote 0
something like this?

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Country Code[/td][td=bgcolor:#70AD47]Shop ID[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
4101​
[/td][td=bgcolor:#E2EFDA]
4101002​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
4101​
[/td][td]
4101003​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
4102​
[/td][td=bgcolor:#E2EFDA]
4102001​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
4102​
[/td][td]
4102002​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
4103​
[/td][td=bgcolor:#E2EFDA]
4103001​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
4103​
[/td][td]
4103004​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
1201​
[/td][td=bgcolor:#E2EFDA]
1201001​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
1201​
[/td][td]
1201002​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
4001​
[/td][td=bgcolor:#E2EFDA]
4001001​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
4001​
[/td][td]
4001002​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
4004​
[/td][td=bgcolor:#E2EFDA]
4004001​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
4004​
[/td][td]
4004002​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]
8001​
[/td][td=bgcolor:#E2EFDA]
8001001​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]
8001​
[/td][td]
8001004​
[/td][/tr]
[/table]
 
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
You must log in or register to reply here.

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

Share this page

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