help me with coding this statement

[mahex]

hello everyone,

i have a table like below picture:

i want to give specific amount of number from user ( the amount of number is based on unique values in state field- for example in above picture we have 5 unique state then we should give 5 number from user) with inputbox and paste the number for each state.
the result should be the below picture

can anyone help me with this??

 

[Yongle]

Try this
Assumes data is in column B, row 2 onwards

Place in standard module
Run from the sheet containing table values
Amend if necessary

Sub AddNumber()
    Dim dict As Object, states As Collection
    Dim ws As Worksheet, rng As Range, state As Variant, msg As String
    Set ws = ActiveSheet
    Set rng = [COLOR=#ff0000]ws.Range("B2", ws.Range("B" & Rows.Count).End(xlUp)[/COLOR])
UniqueStates:
    Set states = New Collection
    On Error Resume Next
    For Each state In rng
        states.Add state, state
    Next
    On Error GoTo 0
CreateDictionary:
    Set dict = CreateObject("Scripting.Dictionary")
    For Each state In states
        dict.Add state, InputBox(state, "ENTER A VALUE")
     Next
IsUserHappy:
    msg = ""
    For Each key In dict.Keys:      msg = msg & vbCr & dict(key) & vbTab & key: Next
    If MsgBox(msg, vbYesNo, "Are you happy?") = vbNo then  GoTo CreateDictionary
AddToWorksheet:
    For Each state In rng:
        For Each key In dict.Keys
            If key = state.Value Then state.Offset(, -1) = dict(key)
        Next key
    Next state
End Sub