I want that excel returns in five cells the first 5 values corresponding to a given name

[IceSnakeR]

Hello,

I need a bit of help with the next situation:

There are two columns, in the first (A) there are two names(PAUL and JOHN) inserted from cell A2 till cell A16, in the second column there are hours. How can i return the first five values(hours) corresponding to JOHN?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]NAME[/TD]
[TD]HOUR[/TD]
[TD][/TD]
[TD]First 5 Hours corresponding to John[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]00:05:46[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PAUL[/TD]
[TD]02:05:46[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PAUL[/TD]
[TD]00:35:46[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JOHN[/TD]
[TD]00:25:46[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JOHN[/TD]
[TD]00:15:46[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JOHN[/TD]
[TD]03:05:46[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PAUL[/TD]
[TD]03:04:46[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JOHN[/TD]
[TD]00:02:46[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PAUL[/TD]
[TD]00:05:43[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PAUL[/TD]
[TD]00:35:46[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PAUL[/TD]
[TD]00:05:44[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JOHN[/TD]
[TD]02:55:46[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PAUL[/TD]
[TD]02:05:45[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thank you,
Chris

 

[jarjarbingie]

Hello,

I need a bit of help with the next situation:

There are two columns, in the first (A) there are two names(PAUL and JOHN) inserted from cell A2 till cell A16, in the second column there are hours. How can i return the first five values(hours) corresponding to JOHN?

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]NAME[/TD]
[TD]HOUR[/TD]
[TD][/TD]
[TD]First 5 Hours corresponding to John[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]00:05:46[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PAUL[/TD]
[TD]02:05:46[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PAUL[/TD]
[TD]00:35:46[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JOHN[/TD]
[TD]00:25:46[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JOHN[/TD]
[TD]00:15:46[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JOHN[/TD]
[TD]03:05:46[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PAUL[/TD]
[TD]03:04:46[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JOHN[/TD]
[TD]00:02:46[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PAUL[/TD]
[TD]00:05:43[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PAUL[/TD]
[TD]00:35:46[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PAUL[/TD]
[TD]00:05:44[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JOHN[/TD]
[TD]02:55:46[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PAUL[/TD]
[TD]02:05:45[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thank you,
Chris

D2 = INDEX($B$2:$B$14,SMALL(IF($A$2:$A$14="John",ROW($A$2:$A$14),""),$C2)-1) press CTEL+SHIFT+ENTER

 

[Peter_SSs]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules. Be sure to follow & read the link at the end of the rule too!

Cross posted at: I want that excel returns in five cells the first 5 values corresponding to a given name

 

[IceSnakeR]

Thank you. Have a nice day.