Change values in column C based on comparison of previous cells in column A

[Bakacaca]

I have a spread sheet with "Property IDs" that each have multiple "rooms" assigned to them. I need to change the "Room Name" in a seperate column based on how many times the Property ID has been Listed.

Example:

I need this:

To be this:

The reason I am looking for a macro or formula is because there are 46,000 rows in this sheet.
Thank you so very much for your help. I do have some code written, but I only started researching how to write it about 4 hours ago and It's nowhere near accurate I believe. Here is what I currently have, but I was messing around with the methods and objects to try and figure it out so it's kind of all over the place atm.

Sub Change_Room_Number()
'
' Change_Room_Number Macro
'

'

'Select Sheet 1
Worksheets("Sheet1").Activate

'Select Column A
Range("A:A").Select


'Declare Variable Site ID
SiteID = Range("A:A").Value
    
'Run If statement to determine Value of Room Number
    If SiteID = ActiveCell.Offset(-16, 0).Value Then
        RoomNumber = "Room 17"
    ElseIf SiteID = ActiveCell.Offset(-15, 0).Activate Then
        RoomNumber = "Room 16"
    ElseIf SiteID = ActiveCell.Offset(-14, 0).Activate Then
        RoomNumber = "Room 15"
    ElseIf SiteID = ActiveCell.Offset(-13, 0).Activate Then
        RoomNumber = "Room 14"
    ElseIf SiteID = ActiveCell.Offset(-12, 0).Activate Then
        RoomNumber = "Room 13"
    ElseIf SiteID = ActiveCell.Offset(-11, 0).Activate Then
        RoomNumber = "Room 12"
    ElseIf SiteID = ActiveCell.Offset(-10, 0).Activate Then
        RoomNumber = "Room 11"
    ElseIf SiteID = ActiveCell.Offset(-9, 0).Activate Then
        RoomNumber = "Room 10"
    ElseIf SiteID = ActiveCell.Offset(-8, 0).Activate Then
        RoomNumber = "Room 9"
    ElseIf SiteID = ActiveCell.Offset(-7, 0).Activate Then
        RoomNumber = "Room 8"
    ElseIf SiteID = ActiveCell.Offset(-6, 0).Activate Then
        RoomNumber = "Room 7"
    ElseIf SiteID = ActiveCell.Offset(-5, 0).Activate Then
        RoomNumber = "Room 6"
    ElseIf SiteID = ActiveCell.Offset(-4, 0).Activate Then
        RoomNumber = "Room 5"
    ElseIf SiteID = ActiveCell.Offset(-3, 0).Activate Then
        RoomNumber = "Room 4"
    ElseIf SiteID = ActiveCell.Offset(-2, 0).Activate Then
        RoomNumber = "Room 3"
    ElseIf SiteID = ActiveCell.Offset(-1, 0).Activate Then
        RoomNumber = "Room 2"
    Else
        RoomNumber = "Room 1"
    End If
    
'Declare the result of the If statement as the Variable RoomNumber and place it in the corresponding column
Range("C").Value = RoomNumber
    
End Sub

 

[Eric W]

Welcome to the MrExcel forum!

You should be able to do that with just a formula:

Book2
	A	B	C	D	E
1	Property ID	Sq.Ft	Room Number	Floor Type	Quantity
2	1	450	Room 1	Tile	1
3	1	450	Room 2	Carpet	2
4	1	450	Room 3	Wood	2
5	4	582	Room 1	Tile	3
6	4	282	Room 2	Tile	2
7	2	256	Room 1	Tile	1
8	2	256	Room 2	Wood	4
9	2	256	Room 3	Wood	2
10	2	256	Room 4	Wood	1
11	2	256	Room 5	Carpet	3
12	5	600	Room 1	Tile	3
13	5	600	Room 2	Tile	3
14	5	600	Room 3	Tile	3
Sheet1

Cell Formulas
Range	Formula
C2:C14	C2	="Room "&COUNTIF($A$2:$A2,$A2)