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]
 
Sort by date Sort by votes
Maybe something like this...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]
A​
[/td][td="bgcolor: #DCE6F1"]
B​
[/td][td="bgcolor: #DCE6F1"]
C​
[/td][td="bgcolor: #DCE6F1"]
D​
[/td][td="bgcolor: #DCE6F1"]
E​
[/td][td="bgcolor: #DCE6F1"]
F​
[/td][td="bgcolor: #DCE6F1"]
G​
[/td][td="bgcolor: #DCE6F1"]
H​
[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]
1​
[/td][td]
Country Code​
[/td][td]
Shop ID​
[/td][td]
Status​
[/td][td][/td][td]
Status​
[/td][td][/td][td]
Country Code​
[/td][td]
Shop ID​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
2​
[/td][td]
4101​
[/td][td]
4101001​
[/td][td]
SF​
[/td][td][/td][td]
Active​
[/td][td][/td][td]
4101​
[/td][td]
4101002​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
3​
[/td][td]
4101​
[/td][td]
4101002​
[/td][td]
Discontinued​
[/td][td][/td][td]
Completed​
[/td][td][/td][td]
4101​
[/td][td]
4101003​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
4​
[/td][td]
4101​
[/td][td]
4101003​
[/td][td]
Active​
[/td][td][/td][td]
Discontinued​
[/td][td][/td][td]
4102​
[/td][td]
4102001​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
5​
[/td][td]
4101​
[/td][td]
4101004​
[/td][td]
Active​
[/td][td][/td][td][/td][td][/td][td]
4102​
[/td][td]
4102002​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
6​
[/td][td]
4102​
[/td][td]
4102001​
[/td][td]
Completed​
[/td][td][/td][td][/td][td][/td][td]
4103​
[/td][td]
4103001​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
7​
[/td][td]
4102​
[/td][td]
4102002​
[/td][td]
Completed​
[/td][td][/td][td][/td][td][/td][td]
4103​
[/td][td]
4103004​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
8​
[/td][td]
4102​
[/td][td]
4102003​
[/td][td]
Completed​
[/td][td][/td][td][/td][td][/td][td]
1201​
[/td][td]
1201001​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
9​
[/td][td]
4102​
[/td][td]
4102004​
[/td][td]
Completed​
[/td][td][/td][td][/td][td][/td][td]
1201​
[/td][td]
1201002​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
10​
[/td][td]
4102​
[/td][td]
4102005​
[/td][td]
Completed​
[/td][td][/td][td][/td][td][/td][td]
4001​
[/td][td]
4001001​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
11​
[/td][td]
4102​
[/td][td]
4102006​
[/td][td]
Active​
[/td][td][/td][td][/td][td][/td][td]
4001​
[/td][td]
4001002​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
12​
[/td][td]
4102​
[/td][td]
4102007​
[/td][td]
Discontinued​
[/td][td][/td][td][/td][td][/td][td]
4004​
[/td][td]
4004001​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
13​
[/td][td]
4102​
[/td][td]
4102008​
[/td][td]
Active​
[/td][td][/td][td][/td][td][/td][td]
4004​
[/td][td]
4004002​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
14​
[/td][td]
4102​
[/td][td]
4102009​
[/td][td]
Active​
[/td][td][/td][td][/td][td][/td][td]
8001​
[/td][td]
8001001​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
15​
[/td][td]
4102​
[/td][td]
4102010​
[/td][td]
Active​
[/td][td][/td][td][/td][td][/td][td]
8001​
[/td][td]
8001004​
[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
16​
[/td][td]
4102​
[/td][td]
4102011​
[/td][td]
Scr​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
17​
[/td][td]
4103​
[/td][td]
4103001​
[/td][td]
Completed​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
18​
[/td][td]
4103​
[/td][td]
4103002​
[/td][td]
SF​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
19​
[/td][td]
4103​
[/td][td]
4103003​
[/td][td]
SF​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
20​
[/td][td]
4103​
[/td][td]
4103004​
[/td][td]
Completed​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
21​
[/td][td]
4103​
[/td][td]
4103005​
[/td][td]
Completed​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
22​
[/td][td]
4103​
[/td][td]
4103006​
[/td][td]
SF​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
23​
[/td][td]
4103​
[/td][td]
4103007​
[/td][td]
Active​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
24​
[/td][td]
1201​
[/td][td]
1201001​
[/td][td]
Completed​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
25​
[/td][td]
1201​
[/td][td]
1201002​
[/td][td]
Completed​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
26​
[/td][td]
1201​
[/td][td]
1201003​
[/td][td]
Active​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
27​
[/td][td]
1201​
[/td][td]
1201004​
[/td][td]
SF​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
28​
[/td][td]
1201​
[/td][td]
1201005​
[/td][td]
Active​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
29​
[/td][td]
4001​
[/td][td]
4001001​
[/td][td]
Completed​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
30​
[/td][td]
4001​
[/td][td]
4001002​
[/td][td]
Completed​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
31​
[/td][td]
4001​
[/td][td]
4001003​
[/td][td]
Completed​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
32​
[/td][td]
4001​
[/td][td]
4001004​
[/td][td]
Active​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
33​
[/td][td]
4001​
[/td][td]
4001005​
[/td][td]
Active​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
34​
[/td][td]
4004​
[/td][td]
4004001​
[/td][td]
Completed​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
35​
[/td][td]
4004​
[/td][td]
4004002​
[/td][td]
Completed​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
36​
[/td][td]
4004​
[/td][td]
4004003​
[/td][td]
Discontinued​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
37​
[/td][td]
4004​
[/td][td]
4004004​
[/td][td]
SF​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
38​
[/td][td]
4004​
[/td][td]
4004005​
[/td][td]
Completed​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
39​
[/td][td]
4004​
[/td][td]
4004006​
[/td][td]
SF​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
40​
[/td][td]
4004​
[/td][td]
4004007​
[/td][td]
SF​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
41​
[/td][td]
4004​
[/td][td]
4004008​
[/td][td]
SF​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
42​
[/td][td]
4004​
[/td][td]
4004009​
[/td][td]
Discontinued​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
43​
[/td][td]
8001​
[/td][td]
8001001​
[/td][td]
Completed​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
44​
[/td][td]
8001​
[/td][td]
8001002​
[/td][td]
SF​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
45​
[/td][td]
8001​
[/td][td]
8001003​
[/td][td]
SF​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
46​
[/td][td]
8001​
[/td][td]
8001004​
[/td][td]
Active​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
47​
[/td][td]
2301​
[/td][td]
2301001​
[/td][td]
SF​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
48​
[/td][td]
2301​
[/td][td]
2301002​
[/td][td]
SF​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
49​
[/td][td]
2303​
[/td][td]
2303001​
[/td][td]
SF​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
50​
[/td][td]
2303​
[/td][td]
2303002​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]
51​
[/td][td]
2303​
[/td][td]
2303003​
[/td][td]
Screened​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in G2 copied down
=IFERROR(INDEX($A$2:$A$100,AGGREGATE(15,6,(ROW(A$2:A$100)-ROW(A$2)+1)/(ISNUMBER(MATCH(C$2:C$100,E$2:E$4,0))*(COUNTIF(G$1:G1,A$2:A$100)<2)),1)),"")

Formula in H2 copied down
=IF(G2="","",INDEX($B$2:$B$100,AGGREGATE(15,6,(ROW(A$2:A$100)-ROW(A$2)+1)/(ISNUMBER(MATCH(C$2:C$100,E$2:E$4,0))*(A$2:A$100=G2)),COUNTIF(G$2:G2,G2))))

M.
 
Upvote 0

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Great contribution from Marcelo, without a doubt they are the formulas you were looking for.


However, I wanted to show you another approach with a pivot table. It is easy to build, you just have to drag the fields.

2cc5215593d16da43d668cea5dd968b9.jpg



In the status field you can filter the necessary ones.
f99384ba2e566502047470a37011947c.jpg


In the shop ID field, you can filter the items you need.

11dd92219cd6ee919c85c650d9672b45.jpg
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,823
Messages
6,181,170
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