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 (http://www.excelforum.com/showthread.php?t=1087133)

[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:


Location of the queue folder for individual received OrderForms (unconsolidated)
Location of the Queue Folder for Consolidated OrderForms
A Business Rule for when to stop consolidating

When the Parts man pulls the consolidated OrderForm from the Queue
At start of Workday
At End of WorkDay
At Midnight of the previous workday
At some specific Time


If not 3.1 "Partsman Pulls from Queue" then location of Ready OrderForms
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