Find the first and last instance of a project in a data range

[excelbytes]

Please see the attached example. I have a list of projects, and the list may have multiple instances of each project. I need to populate the grid in rows 12:14 with the column heading of the first and last instance of a 1 for that project. For example, Proj1 has the first "1" in columns A, C, & A, so A would go in the first block since that is where the first instance is of the three. Proj1 has the last "1" in F, G, & A (in the third instance, A is the first and last) so G would go in the second block.

I can accomplish this using a helper grid of SUMIFS for each Proj, but I am trying to do this with just a formula and no intermediate helper grid.

Is this possible without VBA? I also have to do this using Excel 2013.

Thanks in advance for your help.

 

[Eric W]

Try this:

Book1
	A	B	C	D	E	F	G	H	I
1
2		A	B	C	D	E	F	G	H
3	Proj1	1					1
4	Proj2		1					1
5	Proj1			1				1
6	Proj2				1
7	Proj3			1			1
8	Proj1	1
9	Proj3		1			1
10
11		First	Last
12	Proj1	A	G
13	Proj2	B	G
14	Proj3	B	F
Sheet4

Cell Formulas
Range	Formula
B12:B14	B12	=INDEX($2:$2,AGGREGATE(15,6,COLUMN($B$2:$I$2)/($A$3:$A$9=$A12)/($B$3:$I$9=1),1))
C12:C14	C12	=INDEX($2:$2,AGGREGATE(14,6,COLUMN($B$2:$I$2)/($A$3:$A$9=$A12)/($B$3:$I$9=1),1))

 

[excelbytes]

It works perfectly. Again, thank you. It seems many of the formulas you have provided for me use the AGGREGATE function with dividing rather than the SMALL or LARGE functions with multiplication. I guess I need to spend more time understanding the AGGREGATE function. Can you explain why the AGGREGATE function with dividing works better than the SMALL or LARGE functions with multiplication?

 

[excelbytes]

I think I just figured it out - if I multiplied, I would get a series of 1's and 0's, which would be fine for the LARGE option, but I don't want a 0 for the SMALL option, and using the option of 6 to ignore errors, it will eliminate all those that were FALSE and only choose the SMALL from number greater than 0, is that right?

 

[Eric W]

I think I just figured it out - if I multiplied, I would get a series of 1's and 0's, which would be fine for the LARGE option, but I don't want a 0 for the SMALL option, and using the option of 6 to ignore errors, it will eliminate all those that were FALSE and only choose the SMALL from number greater than 0, is that right?

Exactly! I don't know if AGGREGATE was designed to work like this, or if some clever soul figured out a way to make to work like this, but it's quite handy. There's another benefit to AGGREGATE too, in that it has some array processing built in to it. This formula was actually about the 4th version I did. The first 3 did not have AGGREGATE, but a combination of other functions and required the Control-Shift-Enter to make it work right. Once I got it working, I refined it down to the version I actually posted. I don't mind the CSE, but I avoid it when possible, since it's easy to forget and the formula won't work.

Anyway, glad I could help!