Possible UNIQUE(COUNT to Identify and number unique column values

[ollyhughes1982]

Hi,

I have the below list of parkruns at which I have volunteered.

I am trying to add two columns next to the Date Completed one:

Column G should assign ascending numbers whenever the first instance of a unique date is found. i.e. Count the number of unique dates, but only count the first role for that day. For this example column G should read as follows:

G4 ‘1’
G5 ‘2’
G6 ’3’
G7 ‘4’
G8 ’5’
G9 [Cell should appear blank]
G10 [Cell should appear blank]
G11 ’6’
G12 onwards… [Cells should appear blank]

I thought I had this solved with the following formula, but it doesn’t leave the blank cells. But it puts ‘5’ in cells G9 and G10 as well, where I want to show blanks.

=IF(C4<>"",COUNT(UNIQUE($F$4:F4)),"")

Column H should assign ascending numbers whenever the first instance of a unique event (venue) AND unique date are found. i.e. count the number of unique dates upon which stints have been completed at each unique event (venue). For this example column H should read as follows:

H4 ‘1’
H5 ‘2’
H6 ’3’
H7 ‘2’
H8 ’2’
H9 [Cell should appear blank]
H10 [Cell should appear blank]
H11 ’3’
H12 onwards… [Cells should appear blank]

The reason for the gaps is that multiple roles can be completed on a single day.

Thanks in advance,

Olly.

 

[Fluff]

For column G try

=IF(Or(C4="",F4=F3),"",COUNT(UNIQUE($F$4:F4)))

Your answer for col H makes no sense to me.

 

[ollyhughes1982]

For column G try

=IF(Or(C4="",F4=F3),"",COUNT(UNIQUE($F$4:F4)))

Your answer for col H makes no sense to me.

Thanks. That worked great. I'll try and explain the other column better.

 

[Fluff]

Ok,

 

[ollyhughes1982]

Don't worry, I think I've already got what I need in another helper column. Was confusing myself. Thanks.

 

[Fluff]

You're welcome & thanks for the feedback.