• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need Short way to do it

Mohandas

Member
I used right function to get the pack code to normal pack code which is max of eight digits
I used to advance to filter to remove the duplicates pack code and used sum if to add up data of duplicate codes. Is there any way to reduce the time and work


Regards

Mohan
 

Attachments

Hui

Excel Ninja
Staff member
You can setup a VBA Macro to do it all for you
If you provide a sample of the data source
 

Smallman

Excel Ninja
OK

Using your file as a baseline the following should cover off your requirement.

Take note of the Yellow cells I put in your model.

Code:
Option Explicit

Sub SumandRemove()
    Dim ar As Variant
    Dim i As Long
    Dim j As Long
    Dim n As Long
    Dim str As String

    n = 2
    ar = Sheet1.Cells(9, 1).CurrentRegion.Value
    With CreateObject("Scripting.Dictionary")
        For i = 3 To UBound(ar, 1)
            str = Right(ar(i, 3), 8): ar(i, 3) = str
            If Not .exists(str) Then
                n = n + 1
                For j = 1 To UBound(ar, 2)
                    ar(n, j) = ar(i, j)
                Next
                .Item(str) = n
            Else
                For j = 4 To UBound(ar, 2)
                    ar(.Item(str), j) = ar(.Item(str), j) + ar(i, j)
                Next
            End If
        Next
    End With
    Sheet3.Range("A4").Resize(n, UBound(ar, 2)).Value = ar
    Sheet3.Columns(1).EntireColumn.Delete
End Sub
Will attach a file to prove workings.

Take care

Smallman
 

Attachments

Mohandas

Member
I used right function to get the pack code to normal pack code which is max of eight digits
I used to advance to filter to remove the duplicates pack code and used sum if to add up data of duplicate codes. Is there any way to reduce the time and work


Regards

Mohan
Data Source is the first Sheet
YGTTFC031 (14)

Regards
Mohandas
 

Smallman

Excel Ninja
Yeah - I changed the sheet names, your souce sheet became - start and the output sheet I originally titled end. Now you have a procedure to sum your data and delete your data, it looked to match your expected results.

Take care

Smallman
 
Top