If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.

Numbering Unique Occurances

Thread starter Stephen_IV
Start date Sep 21, 2011

S

Stephen_IV

Well-known Member

Sep 21, 2011

#1

This is a small sample of the data that I have. I sorted the data based on Activity and then Date. What I am trying to do is to number each activity based on date. So
on 8/26/2010 activity A occured which makes occurance 1
on 11/2/2010 activity A occured so that is the second occurance.

Here is another example

on 8/26/2011 Activity B occured which makes the first occurance
on 11/2/2010 Activity B occured which makes the second occurance
on 11/3/2010 Activity B occured which makes the third occurance
on 11/4/2010 Activity B occured which makes the forth occurance

Below is a sample of how I would like the data layed out.

Thanks in advance Stephen.

Book1

A

B

C

D

1

Date

Activity

Count

2

8/26/2010

a

1

3

8/26/2010

a

1

4

8/26/2010

a

1

5

8/26/2010

a

1

6

8/26/2010

a

1

7

8/26/2010

a

1

8

11/2/2010

a

2

9

11/2/2010

a

2

10

11/2/2010

a

2

11

11/2/2010

a

2

12

11/2/2010

a

2

13

11/2/2010

a

2

14

8/26/2010

b

1

15

8/26/2010

b

1

16

11/2/2010

b

2

17

11/2/2010

b

2

18

11/2/2010

b

2

19

11/3/2010

b

3

20

11/3/2010

b

3

21

11/4/2010

b

4

22

11/4/2010

b

4

23

9/14/2011

c

1

24

9/14/2011

c

1

25

9/15/2011

c

1

26

9/16/2011

c

2

27

9/17/2011

c

3

28

9/18/2011

c

4

29

9/19/2011

c

5

Sheet6

Excel Facts

What do {} around a formula in the formula bar mean?

Click here to reveal answer

{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Sort by date Sort by votes

J

Jose_Chasez

Board Regular

Sep 21, 2011

#2

Try this in C2 (and fill down):

=IF(B2<>B1,1,IF(AND(A2=A1,B2=B1),C1,C1+1))

P.S., from rows 25-29, is your count off by 1?

Upvote 0

S

Stephen_IV

Well-known Member

Sep 21, 2011

#3

Jose_Chasez,

Thanks you so much! Your right it was off by 1. Thanks again! Much appreciated!

Upvote 0

S

Stephen_IV

Well-known Member

Sep 21, 2011

#4

Jose_Chasez,

I found a small bug in the formula

9/14/2011 c 1
9/14/2011 c 1
9/15/2011 c 2
9/16/2011 c 3
9/17/2011 c 4
9/18/2011 c 5
9/19/2011 c 6
9/19/2012 c 7
9/19/2012 c 7

the occurance continues for c when the last 2 dates have the same month and day but different year?

The last 2 dates should be occurance 1 since they have a different year.

9/19/2012 c 1
9/19/2012 c 1

Can anyone please fix this.
Thanks again Stephen

<html><head><title>Excel Jeanie HTML</title></head><body>

Excel Workbook

A

B

C

1

Date

Activity

Count

2

8/26/2010

a

3

8/26/2010

a

4

8/26/2010

a

5

8/26/2010

a

6

8/26/2010

a

7

8/26/2010

a

8

11/2/2010

a

9

11/2/2010

a

10

11/2/2010

a

11

11/2/2010

a

12

11/2/2010

a

13

11/2/2010

a

14

8/26/2010

b

15

8/26/2010

b

16

11/2/2010

b

17

11/2/2010

b

18

11/2/2010

b

19

11/2/2010

b

20

11/3/2010

b

21

11/4/2010

b

22

11/4/2010

b

23

9/14/2011

c

24

9/14/2011

c

25

9/15/2011

c

26

9/16/2011

c

27

9/17/2011

c

28

9/18/2011

c

29

9/19/2011

c

30

9/19/2012

c

31

9/19/2012

c

Sheet2

</body></html>

Should look like below:

<html><head><title>Excel Jeanie HTML</title></head><body>

Excel Workbook

A

B

C

1

Date

Activity

Count

2

8/26/2010

a

3

8/26/2010

a

4

8/26/2010

a

5

8/26/2010

a

6

8/26/2010

a

7

8/26/2010

a

8

11/2/2010

a

9

11/2/2010

a

10

11/2/2010

a

11

11/2/2010

a

12

11/2/2010

a

13

11/2/2010

a

14

8/26/2010

b

15

8/26/2010

b

16

11/2/2010

b

17

11/2/2010

b

18

11/2/2010

b

19

11/2/2010

b

20

11/3/2010

b

21

11/4/2010

b

22

11/4/2010

b

23

9/14/2011

c

24

9/14/2011

c

25

9/15/2011

c

26

9/16/2011

c

27

9/17/2011

c

28

9/18/2011

c

29

9/19/2011

c

30

9/19/2012

c

1

31

9/19/2012

c

1

Sheet2

</body></html>

Upvote 0

J

Jose_Chasez

Board Regular

Sep 21, 2011

#5

This seems to work. Hard-code "1" in cell C2. Insert this formula in C3 and drag down

=IF(OR(B3<>B2,AND(B3=B2,YEAR(A3)<>YEAR(A2))),1,IF(AND(A3=A2,B3=B2),C2,C2+1))

Upvote 0

S

Stephen_IV

Well-known Member

Sep 22, 2011

#6

Jose_Chasez,

Worked perfectly. Thank you!

Upvote 0

You must log in or register to reply here.

Similar threads

Solved

Count number of occurence of value with condition

2

Replies: 10

Views: 300

Oct 11, 2024

Alex Blakenburg

A

X

Question

VBA for extracting specific data from PDF Docs

Replies: 3

Views: 728

Nov 3, 2024

xcellrodeo

X

M

Solved

Count Unique Values Based on Unique (sort of) Criteria

Replies: 4

Views: 257

Aug 5, 2024

Fluff

B

Solved

Looking to expand tool to track vacation pro-rations

Replies: 1

Views: 307

Jun 14, 2024

bh24524

B

S

Question

Horizontal Return

Replies: 6

Views: 229

Jun 28, 2024

Fluff

Share:

Facebook X (Twitter) LinkedIn Reddit Tumblr WhatsApp Email Link

Forum statistics

Threads: 1,224,609

Messages: 6,179,876

Members: 452,949

Latest member: Dupuhini

Share this page

Facebook X (Twitter) LinkedIn Reddit Tumblr WhatsApp Email Link

Top