Combining Duplicates

[LordDragon]

Greetings,

I have a page with 3 columns and an unknown number of rows. There is a header row at the top. This is an order sheet for parts. The items being added come from several lists, with some duplicate parts.

What I need is to take any duplicate parts (based on part number) and combine the quantities so the item is only on the list once, but the correct quantity is listed.

For example:

Part Name	Part Number	QTY Needed
Cable Comm 422 150ft	CBLASS174	6
Cable Comm 422 40ft	CBLASS446	1
Label, Cable ID, Do Not Unplug	LBL468	3
Label, Cable ID, Do Not Unplug	LBL468	7

Notice the duplicate parts. I want to be able to make this be:

Part Name	Part Number	QTY Needed
Cable Comm 422 150ft	CBLASS174	6
Cable Comm 422 40ft	CBLASS446	1
Label, Cable ID, Do Not Unplug	LBL468	10

Thanks.

[SamT]

Let me welcome you the VBA Express, IMO, the best help site on the web for all things Microsoft Office.

I have a page with 3 columns and an unknown number of rows. There is a header row at the top. This is an order sheet for parts. The items being added come from several lists, with some duplicate parts.

What I need is to take any duplicate parts (based on part number) and combine the quantities so the item is only on the list once, but the correct quantity is listed.

Can you tell us, with many and all specifics, how you are currently handling the task?

[SamT]

Crossposted 4 minutes apart:
Combining Duplicates

[Aussiebear]

Have you tried using sum product?

[mdmackillop]

I use this for 2 column data

Option Explicit


Sub SumUnique()
    Dim col As New Collection
    Dim Rc As Range, Rf As Range, R As Range
    Dim cel As Range
    Dim Srce1 As Range, Srce2 As Range, Tgt As Range
    Dim Col1 As Long, Col2 As Long, i As Long


    Set Srce1 = Application.InputBox("Select cell in Duplicate column", "Select Source", Type:=8)
    Set Srce2 = Application.InputBox("Select cell in Value column", "Select Data", Type:=8)
    Col1 = Srce1.Column: Col2 = Srce2.Column


    Set Tgt = Application.InputBox("Select target cell", "Select Target", Type:=8)
    Set Rc = Srce1.EntireColumn.SpecialCells(xlCellTypeConstants)
    Set Rf = Srce1.EntireColumn.SpecialCells(xlCellTypeConstants)
    Set R = Union(Rc, Rf)
    On Error Resume Next
    For Each cel In R
        col.Add cel, cel
    Next
    For i = 1 To col.Count
        Tgt.Offset(i - 1) = col(i)
        Tgt.Offset(i - 1, 1).FormulaR1C1 = "=SUMIF(C" & Col1 & ",RC[-1],C" & Col2 & ")"
    Next
    Tgt.Offset(col.Count).FormulaR1C1 = "=SUM(C" & Col2 & ")"
    Tgt.Offset(col.Count, 1).FormulaR1C1 = "=SUM(R1C:R[-1]C)"
End Sub

[LordDragon]

SamT,

Currently, the task is not being handled at all. Each person that has to order things either goes online and orders them (if they know how and are not too lazy at the time). But most people just email the warehouse to tell them what they need. These parts are then pulled from the shelves for the order.

However, when you order parts for a particular system, you often end up with the same parts being ordered for the next system too. These often are going to the same place at the same time for the same job, just different parts of the job.

Rather than the warehouse having to pull 4 parts for one system, then 3 of the same parts for the next system. The order should be combined so they pull the 7 parts needed at one time.

As far as how I want to make it be handled. I don't really have a preference, but it needs to be "user friendly" and easier than putting in two separate orders.

My current design is each system on a separate page in a workbook. They mark if they want the part and how many. Then on the order form, they click a button that pulls all the parts ordered from the different systems and puts them on the order form. That part works fine.

Now I need to combine all duplicates created on the order form into one row per item and the quantities added together.

Thanks.

[mperrah]

This should work.
I use Column E, F and G temporarily to add values,
then move the results back over.
I got lazy at the end so used macro recording, it could be cleaned up, but it works.

Sub vbax52835()
' mark perrah 6/9/15 combining duplicates
Dim aSum, lr, lrF, i, t, x As Long
Dim aPart As Variant

    lr = Cells(Rows.Count, 1).End(xlUp).Row
        
    Range("B2:B" & lr).Copy Destination:=Range("F1")
    ActiveSheet.Range("$F$1:$F" & lr).RemoveDuplicates Columns:=1, Header:=xlNo
    
    lrF = Cells(Rows.Count, 6).End(xlUp).Row
        ReDim aPart(1 To lrF)
        For x = 1 To lrF
            aPart(x) = Cells(x, 6).Value
        Next x
        
        For t = LBound(aPart) To UBound(aPart)
            aSum = 0
            For i = 2 To lr
                If aPart(t) = Cells(i, 2) Then
                    aSum = aSum + Cells(i, 3).Value
                    Cells(t, 5).Value = Cells(i, 1).Value
                End If
            Next i
            
            Cells(t, 7).Value = aSum
        Next t
    
    Range("A2:C" & lr).Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("E1:G" & lrF).Select
    Selection.Cut
    Range("A2").Select
    ActiveSheet.Paste
    Range("A1").Select

End Sub

[SamT]

Currently, the task is not being handled at all.

Of course it is. Being handle, that is.

Details of how the matter is currently being handled:

• you are at the warehouse
• workers who pull the parts are at the warehouse
• people who order the parts are not at the warehouse
• Orders are received at the warehouse either via online Order Forms or via email request
  
  • If via Email, they are manually entered into an Order Form
  
  
• OrderForms need consolidating by Ship To location into lines of unique Parts with totaled qtys.
• Somebody(s) somewhere(s) has a workbook with an OrderForm and many System parts sheets. That person selects parts and qtys from the System sheets and the workbook fills out the OrderForm, but does not consolidate all similar parts for various systems.
• The details of the OrderForm reaching a desk at the warehouse are not available to me.
• Multiple persons from the same location may want various qtys of the same part delivered to that location.

Have I missed anything?

Fortunately, all OrderForms are electronic, which makes it possible to automate their consolidation.

Unfortunately, you will continue to have to waste manhours transferring email requests to OrderForms until you refuse to do it.Once you refuse, traffic will slow down for three days, then you will have a rush for three days.

Details still needed:

1. Location of the queue folder for individual received OrderForms (unconsolidated)
2. Location of the Queue Folder for Consolidated OrderForms
3. A Business Rule for when to stop consolidating
   
   1. When the Parts man pulls the consolidated OrderForm from the Queue
   2. At start of Workday
   3. At End of WorkDay
   4. At Midnight of the previous workday
   5. At some specific Time
   
   
4. If not 3.1 "Partsman Pulls from Queue" then location of Ready OrderForms
5. a Blank copy of an OrderForm as it is received online.

Happy to help.

[LordDragon]

mperrah,

That worked perfectly. Thanks.

[SamT]

Problem Solved? Use Thread Tool at top to so mark. Thanks