Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging duplicate rows
Hi, Firstly I'd like to thank the people on here for replying so quickly its a great facility I have a problem with a order worksheet in my workbook.. basically, it allows products to be added into a summary, where i lists the model, description and quantity. What I want is to merge the duplicate rows, and increment the quantit field. So if i have 3 rows the same, it will filter to one row and put "3" i the quantity field.. can anyone help..? Mat ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging duplicate rows
Guess not! hmm, can anyone give me an inclin on how to start the task ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging duplicate rows
You can use the Data=Filter=Advanced filter, checking the uniques checkbox
and copy to another location to get a list of your uniques. If you start from the destination sheet, you can have the data filter put the information on another sheet. You need to specify the three columns that form the unique data. You can then put in a sumproduct formula next to these unique entries to sum up. =sumproduct(--(Sheet1!$A$1:$A$200=A1),--(Sheet1!$B$1:$B$200=B1),--(Sheet1!$C $1:$C$200=C1),$D$1:$D$200) then drag fill it down the list. If you just want a count of the rows (which is what you described), remove the last argument. =sumproduct(--(Sheet1!$A$1:$A$200=A1),--(Sheet1!$B$1:$B$200=B1),--(Sheet1!$C $1:$C$200=C1)) -- Regards, Tom Ogilvy mattis2k wrote in message ... Hi, Firstly I'd like to thank the people on here for replying so quickly, its a great facility I have a problem with a order worksheet in my workbook.. basically, it allows products to be added into a summary, where it lists the model, description and quantity. What I want is to merge the duplicate rows, and increment the quantity field. So if i have 3 rows the same, it will filter to one row and put "3" in the quantity field.. can anyone help..? Matt ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging duplicate rows
Cheers Tom, But I was really looking for a way in VB, sorry i forgot to mention that bit.... Thanks Matt ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merging duplicate rows
It is even easier in VBA to call the data filter and put the results on a
separate sheet. Adding the sumproduct formula to the cells in VBA would again be simplicity. Sub AAAD() Dim rng As Range Dim rng1 As Range Dim rng2 As Range With Worksheets("NC90") Set rng = .Range("A1"). _ CurrentRegion.Resize(, 3) End With Set rng2 = rng.Offset(1, 0). _ Resize(rng.Rows.Count - 1, 1) With Worksheets("sheet3") rng.AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=.Range("A1:C1"), _ Unique:=True Set rng1 = .Range(.Cells(2, 1), _ ..Cells(Rows.Count, 1).End(xlUp)) End With rng1.Offset(0, 3).Formula = "=SumProduct(--(" & _ rng2.Address(External:=True) _ & "=A2),--(" & rng2.Offset(0, 1).Address(External:=True) _ & "=B2),--(" & rng2.Offset(0, 2).Address(External:=True) _ & "=C2))" rng1.Offset(0, 3).Formula = rng1.Offset(0, 3).Value End Sub -- Regards, Tom Ogilvy mattis2k wrote in message ... Cheers Tom, But I was really looking for a way in VB, sorry i forgot to mention that bit.... Thanks Matt ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merging duplicate lines in a Database | Excel Discussion (Misc queries) | |||
Combining and merging duplicate data. | Excel Discussion (Misc queries) | |||
Merging data in multiple rows where the first cell has duplicate d | Excel Discussion (Misc queries) | |||
merging information from partial duplicate rows | Excel Discussion (Misc queries) | |||
MERGING COLUMNS WITH DUPLICATE INFO. | Excel Worksheet Functions |