Excel formula to Automatically put series on Patterns

[Shinianne]

Hi,

I'd like to ask help with excel.
Is it possible to detect which comes first?

example:

John smith took his break 3 times a day, how can i identify the first break and the other 2, like is it possible that a formula can Make it like Break 1, Break 2, Break 3?

 

[Toadstool]

Hi Shinianne,

There a few ways to do this but here's one which assume you have the data in time sequence. If I didn't guess near your actual data format then you can copy&paste an example of your data to help.

Book1
	A	B	C	D
1	Name	Action	Time	Breaks
2	Sarah Jones	Clocked In	8:25
3	Sarah Jones	Help Desk	8:27
4	John Smith	Clocked In	8:30
5	John Smith	Help Desk	8:32
6	Alf Green	Clocked In	8:34
7	Alf Green	Help Desk	8:45
8	John Smith	Break	11:30	Break 1
9	Sarah Jones	Break	12:22	Break 1
10	Sarah Jones	Help Desk	13:05
11	John Smith	Break	13:20	Break 2
12	Alf Green	Break	14:17	Break 1
13	John Smith	Help Desk	15:15
14	Alf Green	Break	16:09	Break 2
15	John Smith	Break	16:12	Break 3
16	Sarah Jones	Clocked Out	17:22
17	John Smith	Clocked Out	18:08
18	Alf Green	Clocked Out	18:22
Sheet1

Cell Formulas
Range	Formula
D2:D18	D2	=IF(B2="Break","Break "&COUNTIFS($A$2:$A2,A2,$B$2:$B2,"Break"),"")

 

[Shinianne]

OMG! yes it's working thank you so much sir, I greatly appreciate it so much! thank you!

 

[Shinianne]

Hi Shinianne,

There a few ways to do this but here's one which assume you have the data in time sequence. If I didn't guess near your actual data format then you can copy&paste an example of your data to help.

Book1
	A	B	C	D
1	Name	Action	Time	Breaks
2	Sarah Jones	Clocked In	8:25
3	Sarah Jones	Help Desk	8:27
4	John Smith	Clocked In	8:30
5	John Smith	Help Desk	8:32
6	Alf Green	Clocked In	8:34
7	Alf Green	Help Desk	8:45
8	John Smith	Break	11:30	Break 1
9	Sarah Jones	Break	12:22	Break 1
10	Sarah Jones	Help Desk	13:05
11	John Smith	Break	13:20	Break 2
12	Alf Green	Break	14:17	Break 1
13	John Smith	Help Desk	15:15
14	Alf Green	Break	16:09	Break 2
15	John Smith	Break	16:12	Break 3
16	Sarah Jones	Clocked Out	17:22
17	John Smith	Clocked Out	18:08
18	Alf Green	Clocked Out	18:22
Sheet1

Cell Formulas
Range	Formula
D2:D18	D2	=IF(B2="Break","Break "&COUNTIFS($A$2:$A2,A2,$B$2:$B2,"Break"),"")

You have resolved my query! thank you so much

 

[Toadstool]

You're welcome and thanks for the response.