In Excel can VBA be used to make an Array to sum device quanities

[timleonard]

In Excel using VBA, how can I sum the following up to device types, quantities and then put the data on a seperate tab in a specific cell range

Device     Qty     Amps
Device A    1    0.060
Device B    1    0.092
Device A    1    0.060
 
Device B    1    0.092
Device B    1    0.092
Device C    1    0.025
Device C    1    0.025
 
Device D    1    0.089
Device A    1    0.060
Device B    1    0.092
 
 
For Example
Device     Qty     Amps
Device A    3    0.060
Device B    4    0.092
Device C    2    0.025
Device D    1    0.089
 

Any help or direction is greatly appreciated.

[Oralloy]

Have you tried a pivot table?

Perhaps you can use a macro to structure your data and copy it to another sheet. Then you can run a pivot table on your sanitized data.

[MikeTheBike]

Hi

I don't know anything about where on the sheet or on which sheet the data is or on which sheet you want to write the result to.

I also assume that you do not have a fix number/type of devices which may permit the use of SUMIF() or SUMPRODUCT() worksheet function.

That being the case then I would probable tackle it this way

Option Explicit
Type Device_Type
    Name As String
    Quantity As Integer
    Amps As Single
    TotalQuantity As Integer
End Type
 
Dim Devices() As Device_Type
 
Sub TestCode()
    Dim ThisDevice As Device_Type
    Dim i As Long
    Dim TopRow As Long
    Dim BotRow As Long
 
    'SET ROW RANGE TO SEARCH (ASSUME ACTIVE SHEET)
    TopRow = 3
    BotRow = 14
 
    'INITIALIZE ARRAY
    ReDim Devices(0)
    Devices(0).TotalQuantity = 0
 
    For i = TopRow To BotRow
        If Cells(i, 1) <> "" Then
            With ThisDevice
                .Name = Trim(Cells(i, 1))
                .Quantity = CInt(Cells(i, 2))
                .Amps = CSng(Cells(i, 3))
            End With
            AddDevice ThisDevice
        End If
    Next i
 
    'OUTPUT RESULTS (OR WRITE TO REQUIRED SHEET)
    For i = 0 To UBound(Devices)
        With Devices(i)
            Debug.Print .Name & " " & .TotalQuantity & " " & .Amps
        End With
    Next i
 
End Sub
 
Sub AddDevice(ByRef ADevice As Device_Type)
    Dim i As Integer
    'CHECK FIRST ARRAY ELEMENT HAS BEEN USED, IF NOT THEN USE IT
    If Devices(0).Name = "" Then
        Devices(0) = ADevice
        Devices(0).TotalQuantity = ADevice.Quantity
        Exit Sub
    Else
        'FIND REQUIRED DEVICE
        For i = 0 To UBound(Devices)
            With Devices(i)
                If .Name = ADevice.Name Then
                    .TotalQuantity = .TotalQuantity + .Quantity
                    Exit Sub
                End If
            End With
        Next i
 
        'DEVICE NOT FOUND SO ADD TO ARRAY
        ReDim Preserve Devices(UBound(Devices) + 1)
        Devices(UBound(Devices)) = ADevice
        Devices(UBound(Devices)).TotalQuantity = ADevice.Quantity
    End If
End Sub
 

As I say you will need to resolve the rows columns and sheet references.

The other thing not clear is whether the quantity could be greater that one, and if so whether this should be included in the total count (I assumed so in the code).

HTH

MTB

[timleonard]

Thanks for your comments and help. I will try them both and see which works best for the need