Macro: combining multiple cells to create a unique string of numbers

[The Cap]

Hello. I'm trying to automate a tedious task of typing each device number manually. I wonder if there is a possibility of creating a macro that'd speed up the process.

All other values are given beforehand. The logic behind each device number goes as follows: First two numbers are fixed and wont change. The third and fourth number state the room the device is located (from the Room No. column, the last two chars of a Room No.). Fifth and the final number is created based on Device Type. 1 - n (there can be more than two) = CCC500, 5 = FFF850, 7 = FFF800, 8 = FFF810 and so on...

The values of the last variable aren't set on stone as described above, as the number of devices in a room varies and duplicates aren't allowed. Is there a possibility to create a simple macro that'd to fill the Device Number column with unique IDs using the logic I explained above?

Thank you in advance!

 

[Manuel_Spreadsheet]

You can do this with a formula:

="10"&RIGHT(A2,2)&VLOOKUP(E2,lookup_table,2,FALSE)

For the last piece (The Vlookup), I'd create a little remapping table. First column is your Device Type, and second column is the number you want returned as the last digit of the Device Number. Name this table "lookup_table".