Allocating sequential number to non-sequential cells, in non-sequential order

[LotsOfQuestions88]

Hi! I'm quite new to excel and looking for some help with a problem - thanks in advance for any help.

I need a good way / formula to allocate a sequential number, but in non-sequential cells and that will not change the value of previously allocated numbers? The resulting allocation column would likely not be in number order.

Eg if person in L2 'confirms', then they would be allocated number '1' (in cell X2)
If person in L3 'confirms', they get '2' (in cell X3)
Personin L4 has not 'confirmed' yet ie 'pending' - no number given (yet) (cell X4 blank)
Person in L5 'confirms', they get number '3' (incell X5)
Then, person in L4 later confirms and gets number'4' (rather than getting '3' and changing the person inL5's number to '4')


Hope that makes sense? I've got a way of doing it currently, which just involves giving a 'wait' message in the cell to, in this case, cells X4:X5 until person in L4 has 'confirmed'and then numbers are allocated, but it means lots of people could be waiting for numbers, pending one person confirming, which is not ideal.

The current formula is: (options in L2 are "Yes" (ie 'confirms'), "Refused" or "Unsure"

=IF(AND(OR(L2="Yes",L2="Unsure"),W2<(G$6+2)),(IF(AND((COUNTA(L$2:L2)=(ROW(L2)-1)),(NOT(L$2:L2="Unsure"))),("C "&((COUNTIF(L$2:L2,"Yes"))+$G$9)),"Wait..")),IF(L2="Unsure"," Check..",""))

I think the last value if false section is negated by the use of the OR function in the first part, but I've left it in for now.


Many thanks again for any help!

 

[Max1616]

This would be much easier to accomplish as a macro. If you have never used a macro here are the steps to do it:

1. Enable the developer tab in your ribbon (if you haven't already) - File-> Options->Customize Ribbon-> Check the Developer box in the right menu
2. Open up Visual Basic - Developer Tab on ribbon -> Visual Basic (furthest left option on the tab)
3. Expand the VBAProject(WorkbookName) in the left hand projects menu
4. Expand the "Microsoft Excel Objects" under the VBAProject(WorkbookName)
5. Double click the sheet you would like to apply this to under "Microsoft Excel Objects" (it will look like Sheets1(SheetName))
6. A blank window that looks like notepad will open up. Paste the following code into this window and then save your workbook:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns(12)) Is Nothing Then
    If LCase(Target) = "yes" And Cells(Target.Row, 24) = "" Then
        Cells(Target.Row, 24) = WorksheetFunction.Max(Columns(24)) + 1
    End If
End If
End Sub

Now any time the word "Yes" is populated in column L then column X will show the next available number not already used in Column X.

Hope this helps.

Sincerely,
Max

*Edited a typo

 

[LotsOfQuestions88]

Hi Max,

Thank you very much for providing that answer - that's great. If I need to change the destination column, do I just change all the 24's to a different number? Also, is it possible for the macro to make the allocated number clear if the column L value was entered as "Yes" in error?

Thanks again!