Creating a list of cells' data based on criteria

[scotthannaford1973]

Hi

hoping that the experts can help on this... you can see I have a basic table below A1:C39 which I'd like to summarise; basically, where the project has "Project Management" beside it in column B and "1" beside it in column C, I'd like the project to be listed in column E - with no blanks between the projects. I could do this in a pivot table, but there could be a dozen date columns and I'd rather use a formula to populate E that I could then easily replicate.

I hope that made sense... thanks in advance!

 

[Eric W]

Try:

Book1 (version 2).xlsb
	A	B	C	D	E
1	Project Name	Team	1/3/2022		Project Management and 1
2	a	a			a
3	a	b			b
4	a	c
5	a	d
6	a	d
7	a	i
8	a	i
9	a	Project Management	1
10	a	Project Support
11	a	T
12	a	t
13	b	a
14	b	b
15	b	c
16	b	d
17	b	d
18	b	i
19	b	i	1
20	b	Project Management	1
21	b	Project Support
22	b	t
23	b	t
24	c	a
25	c	b
26	c	c
27	c	d
28	c	d
29	c	i
30	c	i
31	c	Project Management
32	c	Project Support
33	c	t
34	c	t
35
Sheet8

Cell Formulas
Range	Formula
E2:E6	E2	=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A$2:$A$100)/($B$2:$B$100="Project Management")/($C$2:$C$100=1),ROWS($E$2:$E2))),"")

 

[Fluff]

How about

=IFERROR(INDEX($A$2:$A$100,AGGREGATE(15,6,(ROW($A$2:$A$100)-ROW($A$2)+1)/($B$2:$B$100="Project management")/($C$2:$C$100=1),ROWS(E$2:E2))),"")

 

[scotthannaford1973]

thanks, both - works an absolute dream! you're superstars!

 

[Eric W]

Glad we could help! Thanks for the update.

 

[scotthannaford1973]

I can work out how everything your formula does (which is fab) apart from the 15,6 what's that part do? are the numbers anything significant?

many thanks!

 

[Fluff]

The 15 tells the aggregate function to operate like the small function & the 6 tells it to ignore errors.

 

[scotthannaford1973]

The 15 tells the aggregate function to operate like the small function & the 6 tells it to ignore errors.

fantastic thanks - I haven't used Aggregate before, so good to see a new function I can use!

 

[Fluff]

You're welcome & thanks for the feedback.