Forum Discussion
Need major help!
Hi Holly,
Did I understand correctly numbers in B shall follow the dates in D? Later the date - lower the number. If so, what if there are several records with the same date in D?
And shall the numbers in B be sequential (starting from 1 the for latest date) or here is another numbering?
If like above that could be
=IFNA(RANK.EQ($D3,$D:$D,0)+COUNTIF($D3:D$4,$D3)-1,)
in D3 and drag it down. Attached.
Not exactly. The DOB is only used to determine Age Group value. Once I have the age groups assigned, I need to start with the last person who registered in the youngest group (from column A) and assign them the contestant #1, followed by reverse order of registration for that particular group. Then, the next group will follow the same process, but will start with the next number not used by the first group. For example, if I have 5 people in the first group, I will need to start with #6 on the second group. I have the possibility of 11 age groups that need to be done in consecutive contestant numbers from 1 to the total number of contestants. Does that make sense?
Okay, another attempt.
I make some correction in your formulas.
Since ages (column E) starts from zero and blank cell in formulas also taken as zero, we add empty text to such cells if no data registration
=IF(ISBLANK(D3),"",INT(('Data Values'!$B$31-D3)/365))Similar logic for Age Group (column E) plus initial formula was simplified:
=IF(ISBLANK(D3),"",VLOOKUP(E3+(F3="Yes"),'Data Values'!$A$4:$B$29,2,FALSE))
With that numbers in column B are generated as
=IF(E3="","",COUNTIF(E$3:$E$1111, $E3)-COUNTIF(E$3:$E3, $E3)+1+COUNTIF(E$3:$E$1111, "<"&$E3))
and attached
WOW!!! Awesome job!! I'm not sure i completely understand it, but it seems to work. I'm going to have to study this so that I can use it again! Thank you so much!
Assume you have only one group. You calculate how many records were registered for that group totally and deduct number number of records registered before. Plus 1 - that gives you the number to assign.
If you have few groups to above number you add total number of records registered for all groups with numbers below the current one.
Assigned numbers are recalculated with each record added/deleted if your Excel in automatic calculation mode (which is default behavior).