Create Summary

[javeed]

Hi,

The below script works fine but only when the Column B (product name) and values compulsory from next Column C (count value) then it creates the summary.
Whereas in my case the Column B (product name) and the values are in Column F (count value), it needs some modification in script so that it will get the values from Column F instead of next Column.

Thanks in advance.

Sub Summary()
Dim Rng As Excel.Range
Dim arrProducts() As String
Dim I As Long
Set Rng = Sheet1.Range("B2:B20000")
arrProducts = getSumOfCountArray(Rng)
Sheet2.Range("A1:B1").Value = Array("product name", "count value")
' go through array and output to Sheet2
'For I = 0 To UBound(arrProducts, 2)
    For I = 0 To UBound(arrProducts, 2)
        Sheet2.Cells(I + 2, "A").Value = arrProducts(0, I)
        Sheet2.Cells(I + 2, "B").Value = arrProducts(1, I)
    Next
Next
End Sub

' Pass in the range of the products
Function getSumOfCountArray(ByRef rngProduct As Excel.Range) As String()
Dim arrProducts() As String
Dim I As Long, j As Long
Dim index As Long
ReDim arrProducts(1, 0)
For j = 1 To rngProduct.Rows.Count
    index = getProductIndex(arrProducts, rngProduct.Cells(j, 1).Value)
    If (index = -1) Then
        ' create value in array
        ReDim Preserve arrProducts(1, I)
        arrProducts(0, I) = rngProduct.Cells(j, 1).Value ' product name
        arrProducts(1, I) = rngProduct.Cells(j, 2).Value ' count value
        I = I + 1
    Else
        ' value found, add to id
        arrProducts(1, index) = arrProducts(1, index) + rngProduct.Cells(j, 2).Value
    End If
Next
getSumOfCountArray = arrProducts
End Function

Function getProductIndex(ByRef arrProducts() As String, ByRef strSearch As String) As Long
' returns the index of the array if found
Dim I As Long
For I = 0 To UBound(arrProducts, 2)
    If (arrProducts(0, I) = strSearch) Then
        getProductIndex = I
        Exit Function
    End If
Next
' not found
getProductIndex = -1
End Function

[SamT]

rngProduct.Cells(j, 2).Value means Cells(j, Column Number) Where Column Number is referenced from the first (only) column in rngProduct