From one column generate a table with two columns

Question

I need macro to look in sheet A to a specific column with values and generate in a already existing sheet B in a specific location of sheet B a table with two columns the first column with the values found in the column without repetitions and the second column with the counting for the correspondent values

I tried this macro but get error message

"BASIC runtime error. Property or method not found: setValue."

Sub GenerateUniqueValueTable()
    Dim oSheetA As Object
    Dim oSheetB As Object
    Dim oSourceRange As Object
    Dim oTargetRange As Object
    Dim oUniqueValues As Object
    Dim oCell As Object
    Dim i As Long

    ' Set the source sheet (Sheet A) and source range (adjust as needed)
    oSheetA = ThisComponent.getSheets().getByName("BD-direta")  
    oSourceRange = oSheetA.getCellRangeByName("P1:P2047")

    ' Set the target sheet (Sheet B) and target range (adjust location as needed)
    oSheetB = ThisComponent.Sheets.getByName("Estatistica") 
    oTargetRange = oSheetB.getCellRangeByName("B64:C64")  

    ' Create a new table header
    oTargetRange.setValue("Unique Value")
    oTargetRange.getCellByPosition(1, 0).setString("Count")

    ' Get unique values and their counts
    oUniqueValues = CreateUnoService("com.sun.star.container.UniqueElementsContainer")
    For Each oCell In oSourceRange
        If Not oUniqueValues.has(oCell.String) Then
            oUniqueValues.insert(oCell.String, 1)
        Else
            oUniqueValues.replaceByIndex(oUniqueValues.indexOf(oCell.String), oUniqueValues.getByIndex(oUniqueValues.indexOf(oCell.String)) + 1)
        End If
    Next

    ' Populate the target sheet
    For i = 0 To oUniqueValues.getCount() - 1
        oSheetB.getCellByPosition(2, i + 1).setString(oUniqueValues.getByIndex(i))
        oSheetB.getCellByPosition(3, i + 1).setValue(oUniqueValues.getByIndex(i))
    Next
End Sub

Answer 1

Indeed, there is no setValue() method on the "cell range" object, this is only available for a single cell.

But that's not the only reason this code won't work. To write the string "Unique Value" to a cell, use the setString() method instead of setValue(), which is designed to write numeric values.

Executing a line

oUniqueValues = CreateUnoService("com.sun.star.container.UniqueElementsContainer")

doomed to failure - there is no UniqueElementsContainer interface in the module com.sun.star.container. Therefore, you must write the code to count unique values yourself. It could be something similar to this:

Sub countUnique(key As Variant, aData As Variant)
Dim l&, r&, m&, N&, i&
    l = LBound(aData)
    r = UBound(aData) + 1
    N = r
    While (l < r) 
        m = l + Int((r - l) / 2)
        If aData(m)(0) < key Then l = m + 1 Else r = m
    Wend
    If r = N Then
        ReDim Preserve aData(0 To N)
        aData(N) = Array(key, 1)
    ElseIf aData(r)(0) = key Then
        aData(r)(1) = aData(r)(1) + 1
    Else
        ReDim Preserve aData(0 To N)
        For i = N - 1 To r Step -1
            aData(i + 1) = aData(i)
        Next i
        aData(r) = Array(key, 1)
    End If
End Sub

For not very large data sets (seven to ten thousand values) this will work quite quickly.

Now your macro that will perform the task could be like this:

Sub GenerateUniqueValueTable()
Dim oSheets As Object, oSheetA As Object, oSheetB As Object
Dim oSourceRange As Object, aData As Variant, aUniqueValues() As Variant
Dim oTargetRange As Object
Dim i As Long
    oSheets = ThisComponent.getSheets()
    ' Set the source sheet (Sheet A) and source range (adjust as needed)
    oSheetA = oSheets.getByName("BD-direta")  
    oSourceRange = oSheetA.getCellRangeByName("P1:P2047")

    ' Set the target sheet (Sheet B) and target range (adjust location as needed)
    oSheetB = oSheets.getByName("Estatistica") 

    ' Create a new table header
    oSheetB.getCellRangeByName("B64").setString("Unique Value")
    oSheetB.getCellRangeByName("C64").setString("Count")

    ' Get unique values and their counts
    aData = oSourceRange.getDataArray()
    For i = LBound(aData) To UBound(aData)
        Call countUnique(aData(i)(0), aUniqueValues)
    Next

    ' Populate the target sheet
    oTargetRange = oSheetB.getCellRangeByPosition(1, 64, 2, 64+UBound(aUniqueValues))
    oTargetRange.setDataArray(aUniqueValues)
End Sub

I understand that your question related specifically to writing a macro. However, I have to say that if you were to add some kind of data header (like Keys) to the data in the 'BD-direta'.P1 cell, you would get the same result in just a few clicks using Pivot Table