Use VBA to assign an ascending number to items, but same number to duplicates

Question

In my sheet for work I have to assign numbers to products that need to be assembled. These numbers will later correspond with blueprints/cad drawings.

When there are duplicates the need to have the same "cad number". Also, some products are ordered so dont need assembly.

This is a smaller example of a list:

Number	Item	Length	Width	Height
Room 1
z	Table	100	100	100
z	Chair	100	100	200
z	Chair	100	100	100
	Lamp	100	100	100
Room 2
z	Table	100	100	100
z	Table	100	200	100
z	Chair	100	100	100

After the macro it should be:

Number	Item	Length	Width	Height
Room 1
1	Table	100	100	100
2	Chair	100	100	200
3	Chair	100	100	100
	Lamp	100	100	100
Room 2
1	Table	100	100	100
4	Table	100	200	100
3	Chair	100	100	100

I use the "z" to manually pick which products need assembly.

I've tried it with multiple columns of formulas. The first to concantate the last 4 columns. The second column a formula with countif to spot the duplicates. The third column has a formula to get the previous number and add 1 or if column 2 says true then "dupe" so i can manually get the right number.

Basicly it takes away 2 of the 4 steps.

Because i need to concantate, countif and somehow have an array of already used number and "concat" combo's its a bit too difficult to start from scratch.

I really want to learn so thats why Im trying to get this with VBA instead of multiple columns of formulas. Also because the file gets send to customers.

Answer 1

So after looking at my macro for half an hour, i came to this conclusion:

For Each c In rngNmbr
If c.value = "z" Then
    'InputValue is all details concanated
    Inputvalue = c.Offset(, 5) & c.Offset(, 6) & c.Offset(, 7) & c.Offset(, 8) & c.Offset(, 9)
    If inArray(arr, Inputvalue) Then
        For i = 0 To 2000
            If arr(i, 0) = Inputvalue Then
                c.value = arr(i, 1)
                Exit For
            End If
        Next
    Else
        c.value = n + 1
        arr(n, 0) = Inputvalue
        arr(n, 1) = c.value
        n = n + 1
    End If
End If
Next

Also I should mention that this code is in an Add-in that I made myself so the workbook itself is still a .xlsx

Thank you for the answers but for now this works a dream.