Forum Discussion
Need major help!
Specifically, I'm looking for Column B on the "Contestant" Sheet. TIA
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
