Earliest and Latest Time where cell contains certain text

**asalbus** · 04-23-2013, 01:31 AM

I am starting from a worksheet that has three columns: A - Name, B - Time and C - Volume. There are up to 208 unique names, but each may have between 1 and 100 rows, with different times - a total of ~1600 different times. I would like to create a new worksheet that gives me the total count per name and then the earliest and latest times for each name. I already have a master list of the names (Sheet 2 column A) and figured out how to do the count (column B). How can I get the earliest time (column C) and the latest time (column D) for each name?

I've tried max and min but can't figure out how to limit the range for each line in Sheet2 to ONLY consider those in column A of Sheet1 that match the name in column A of Sheet 2 (my master list).

Sample of what I'm looking for:

Name Volume EarliestSubmissionTime LatestSubmissionTime
ABC Corp 25 10:07 AM 4:32 PM

Thanks!

**Lemice** · 04-23-2013, 01:44 AM

Hello,

What you can do is using an Array formula like

=SMALL(IF('MasterList'A2='List'A:A,'List'C:C),1)

and hold Ctrl-Shift then hit Enter (If you see the formula warped in { }, then you did it right). The formula above assumes that the name you want to search for is in A2 (ABC Corp for e.g), 'List'A:A is the column where you have the name, 'List'C:C is the column where you have the EarliestSubmissionTime.
Similarly, to find the LastestSubmissionTime, you can use

=LARGE(IF('MasterList'A2='List'A:A,'List'D:D),1)

For better results, please provide a sample file.