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

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,344
Maybe something like this...


A
B
C
D
E
F
G
H
1
Country Code​
Shop ID​
Status​
Status​
Country Code​
Shop ID​
2
4101​
4101001​
SF​
Active​
4101​
4101002​
3
4101​
4101002​
Discontinued​
Completed​
4101​
4101003​
4
4101​
4101003​
Active​
Discontinued​
4102​
4102001​
5
4101​
4101004​
Active​
4102​
4102002​
6
4102​
4102001​
Completed​
4103​
4103001​
7
4102​
4102002​
Completed​
4103​
4103004​
8
4102​
4102003​
Completed​
1201​
1201001​
9
4102​
4102004​
Completed​
1201​
1201002​
10
4102​
4102005​
Completed​
4001​
4001001​
11
4102​
4102006​
Active​
4001​
4001002​
12
4102​
4102007​
Discontinued​
4004​
4004001​
13
4102​
4102008​
Active​
4004​
4004002​
14
4102​
4102009​
Active​
8001​
8001001​
15
4102​
4102010​
Active​
8001​
8001004​
16
4102​
4102011​
Scr​
17
4103​
4103001​
Completed​
18
4103​
4103002​
SF​
19
4103​
4103003​
SF​
20
4103​
4103004​
Completed​
21
4103​
4103005​
Completed​
22
4103​
4103006​
SF​
23
4103​
4103007​
Active​
24
1201​
1201001​
Completed​
25
1201​
1201002​
Completed​
26
1201​
1201003​
Active​
27
1201​
1201004​
SF​
28
1201​
1201005​
Active​
29
4001​
4001001​
Completed​
30
4001​
4001002​
Completed​
31
4001​
4001003​
Completed​
32
4001​
4001004​
Active​
33
4001​
4001005​
Active​
34
4004​
4004001​
Completed​
35
4004​
4004002​
Completed​
36
4004​
4004003​
Discontinued​
37
4004​
4004004​
SF​
38
4004​
4004005​
Completed​
39
4004​
4004006​
SF​
40
4004​
4004007​
SF​
41
4004​
4004008​
SF​
42
4004​
4004009​
Discontinued​
43
8001​
8001001​
Completed​
44
8001​
8001002​
SF​
45
8001​
8001003​
SF​
46
8001​
8001004​
Active​
47
2301​
2301001​
SF​
48
2301​
2301002​
SF​
49
2303​
2303001​
SF​
50
2303​
2303002​
51
2303​
2303003​
Screened​

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.
 

DanteAmor

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




In the status field you can filter the necessary ones.


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

 

Forum statistics

Threads
1,085,429
Messages
5,383,624
Members
401,843
Latest member
stevensmith1

Some videos you may like

This Week's Hot Topics

Top