Sequential Numbering of Unique Entries

Hi All,

A little background, I have an event management doc that lists all of the information about events during the year (name, time, place, lead counts, cost, etc). I would like to use this document to create a dynamic calendar that populates some information about each event in the appropriate date box (I might need some help on that later). To do this, my current thought is to assign each event a unique id (based primarily on the date) so that I can match it up to a date in the calendar. This is a living document that is always being updated and added to, so id might change as more info is added.

Here is my hold up: I would like to assign a sequential count to each event on a given day, in a given county, by time. So that Event "AA" on 01/01, in LA would have an id of 1, and "AB" on 01/01 in LA would have an id of 2; "AD" on 01/01 in OC would have an id of 1.

In essence, the sequence restarts everyday, for each county and is ranked by time (then by key if time is the same).

(If there is an easier way to do this (or the calendar thing) please let me know...)

Thanks for the help!!

Art

Hi John,

Thanks for the help.

So I think your solution only works when the table is sorted correctly. however, because this doc is used by a lot of people, it is often sorted by a completely different column. Ideally what I am looking for will work regardless of how the table is sorted.


Second, thank you for the other thread. The last file there is very close to what I am going for. However, for the calendar I will need to be able to select between counties, so that each county has its own calendar. Also, I have been trying to replicate that formula in my table (rank([@date],[date]...countif..) but it's not working correctly. I guess what I need then is a unique rank (by time) of all events in the same county on the same day.

Hope this makes sense.

Thank you again!!
Art

I am not sure to can get the ID you want without sorting: to do this means a "continuous" search to find the next date/time combination for a given location.

Is it possible to get an ID which is a concatenation of Location/Date/time?

I have seen some people use a sumproduct with rank.. not sure if that can work (I haven't been able to figure it out).
http://www.ozgrid.com/forum/showthread.php?t=141020
(and again, I am using table references, so that may be a problem too)

So the problem with adding the time is that the calendar will not have a time reference. I will be using a concatenation of "DATE_LOCATION_ID" to match an event with a date and the row (within a given date box) on which to pull Event name. This is why I would like the rank to be in time" order so that the events are displayed chronologically on the calendar.

So this is almost getting there..
=1+SUMPRODUCT(([County]=[@County])*([Date]=[@Date])*([Time]<[@Time]))

It ranks counties and dates individually, but entries with the same time are not uniquely ranked by the "key".

Here is what I get:

Key	County	Date	Time	CalID
5	LA	01/03/2016	7:00	1
6	LA	01/03/2016	13:00	3
7	LA	01/03/2016	14:00	5
8	LA	01/03/2016	7:00	1
9	LA	01/03/2016	13:00	3

The calID should read (going down): 1,3,5,2,4

Thanks Jete! I actually did something very similar to this:

Formula:

Please Login or Register  to view this content.

As far as I can tell, this is working.