Determine Multiple Ranges from Column Values

[DBB1]

A system report generates a spreadsheet that contains hundreds of rows of staff and parts info, similar to the following. The number of rows on the report vary each month, by user and in total:

I would like to be able to determine the range of each of the staff, so that I can eventually use COUNTIFS(<range of staff's data>, "Part") to determine the number of Part occurrences for each user ('Parts' is a small, known set of values).

Ultimately, I would like to be able to take this data and construct the following using the range and COUNTIFS info. Maybe even automate the creation of a pivot table at some point, but baby steps, first...

I've looked at Match, Index, and Offset as a method of doing this, but can't quite adapt the examples I've found to my data.

Any assistance would be greatly appreciated!

-DBB1

 

[awoohaw]

not going to recreate your data. But, try this starting in cell E43:

Book4
	E	F	G	H	I	J	K
43	Staffer	GlowPlug	Carb	Fuel Line	Clamp	Gasket	Insulator
44	Jones, Robert	0	0	0	0	0	0
45	Smith, Frank	0	0	0	0	0	0
Sheet1

Cell Formulas
Range	Formula
F44:K45	F44	=COUNTIFS($B$9:$B$40,$E44,$C$9:$C$40,F$43)

But, the PIVOT TABLE idea is the easiest since it is a "flat" file (although it does have those summary rows) and not a cross tab report. You may need to delete those summary rows.

 

[DBB1]

Wow, that was fast, and works well. I just need to figure out how to automate the single-name list creation in your example from all the duplicate names in my Column B.

Thank you, so much!

 

[awoohaw]

try:

=UNIQUE(FILTER($B$9:$B$40,--(isnumber($A$9:$A$40)),""),FALSE,FALSE)

(this was whinged, but something like that should get you a unique list as long as dates are the only number is column A.)
(you may not need the double unary "-", either)