Counting duplicate value only once in excel or VBA , will auto calculate my total of room type when i adding new room

Question

this is my Room record. i need to put formula or VBA code to count my type of room.

BedNum  RoomNum  RoomType         RoomType    totalcount
1001#1   1001     Studio           Studio         3
1001#2   1001     Studio           2br1cr         2
1001#3   1001     Studio           3br1cr         1
1002#1   1002     2br1cr
1002#2   1002     2br1cr
1003#1   1003     3br1cr
1003#2   1003     3br1cr
1004#1   1004     Studio
1004#2   1004     Studio
1005#1   1005     2br1cr
1005#2   1005     2br1cr
1006#1   1006     Studio
1006#2   1006     Studio
---------------------------------------------------------
above list is my Excel record , i wanna count in the rightside with formula or either VBA code ,
my RoomNumber will always increase and adding new room , what i want is total up the count of my roomtype.

Answer 1

As said in my hint you can use "database" technology to do this. I used the code below to achieve the following result

I started with the following layout

The sheet shResult is empty. I renamed the codenames accordingly!

Reading on codename

Reading on ADO

Sub ReadFromWorksheetADO()

    ' Goto Tools/Reference and
    ' add Microsoft ActiveX Data Objects
    Dim conn As New ADODB.Connection

    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
        & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=YES;"";"

    Dim query As String
    ' The name in the tab has to be shData
    query = "Select ID, Name From [shData$] GROUP BY Name,ID"

    Dim rs As New ADODB.Recordset
    rs.Open query, conn

    ' Here I use the codename of the second sheet shResult!!
    With shResult
        .Cells.ClearContents
        Dim i As Long
        For i = 0 To rs.Fields.Count - 1
            .Cells(1, i + 1).Value = rs.Fields(i).Name
        Next i
        .Range("A2").CopyFromRecordset rs
    End With
    rs.Close

    ' Now I take the result of the first query and count
    query = "Select Name, count(ID) as CountOf From [shResult$] GROUP BY Name"
    rs.Open query, conn

    ' Here I use the codename of the first sheet shData
    With shData
        .Range("D2").CopyFromRecordset rs
    End With

End Sub

PS1 I just saw that you changed your post but you just need to modify the code with the right fieldnames in order to get the following result

Modified code

Sub ReadFromWorksheetADO_B()

    ' Goto Tools/reference and
    ' add Microsoft ActiveX Dataa Objects
    Dim conn As New ADODB.Connection

    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
        & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=YES;"";"

    Dim query As String
    ' The name in the tab has to be shData
    query = "Select RoomNum, RoomType From [shData$] GROUP BY Roomtype,RoomNum"

    Dim rs As New ADODB.Recordset
    rs.Open query, conn

    ' Here I use the codename of the first sheet shResult!!
    With shResult
        .Cells.ClearContents
        Dim i As Long
        For i = 0 To rs.Fields.Count - 1
            .Cells(1, i + 1).Value = rs.Fields(i).Name
        Next i
        .Range("A2").CopyFromRecordset rs
    End With
    rs.Close

    ' Now I take the result of the first query and count
    query = "Select RoomType, count(RoomNum) as CountOf From [shResult$] GROUP BY RoomType"
    rs.Open query, conn

    ' Here I use the codename of the first sheet
    With shData
        .Range("E2").CopyFromRecordset rs
    End With

End Sub

PS2: One can combine the two queries above in one and one would save the extra sheet.

query = "Select RoomType, count(RoomNum) From (Select RoomNum, RoomType From [shData$] " & _
                "GROUP BY Roomtype,RoomNum) Group by RoomType"

And the final code looks like that then

Sub ReadFromWorksheetADO_C()

    ' Goto Tools/reference and
    ' add Microsoft ActiveX Dataa Objects
    Dim conn As New ADODB.Connection

    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _
        & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0;HDR=YES;"";"

    Dim query As String
    ' The name in the tab has to be shData
    query = "Select RoomType, count(RoomNum) From (Select RoomNum, RoomType From [shData$] " & _
                        "GROUP BY Roomtype,RoomNum) Group by RoomType"

    Dim rs As New ADODB.Recordset
    rs.Open query, conn

    ' Here I use the codename of the first sheet
    With shData
        .Range("E2").CopyFromRecordset rs
    End With

End Sub