# Solved combine duplicate and sum

Microsoft Office 2010 professional (full...
January 26, 2015 at 12:23:40
Specs: Windows 7
 Greetings, Greetings, I'm trying to combine duplicate rows on the basis of order # and sum the total columns. My worksheet included both text and numeric values which I would like to include in the final report.1. Consolidate only displays numeric values and shows blank rows for the text values 2. After using pivot table how can I display all my columns as in the original format?TIA, Regards

See More: combine duplicate and sum

February 1, 2015 at 14:34:23
 Sorry, we have a communications error.I was unaware that you had an actual Sheet 2,I probably should have said to copy to a NEW sheet.So if you already have a Sheet 1 & Sheet 2copy the data to Sheet 3, the formula should still work.Copy Columns A thru E from Sheet 1( Order Number, Order Date, Order Type, Supplier Account Code)of your data set to a NEW Sheet, do not copy the Amounts.Then on the Data tab, select Remove DuplicatesUse only the Order Numbers or Column AYou should now have your three rows of unique Order numberswith no Amounts and it should look something like this:``` A B C D E 1) Order Number Order Date Order Type Supplier Account Code 2) 51300 12/1/2014 STANDARD SHC 101-566100-6101-1 3) 51302 12/1/2014 STANDARD MBC 101-7200-7036-1 4) 51312 12/1/2014 STANDARD APT 101-5400-6020-1 ```In cell F2 enter your array formula:=SUM((Sheet1!\$A\$2:\$A\$387=A2)*(Sheet1!\$F\$2:\$F\$387))or the NON array formula:=SUMPRODUCT((Sheet1!\$A\$2:\$A\$387=A2)*(Sheet1!\$F\$2:\$F\$387))Since I do not know how many unique Order numbers you haveDrag down as many rows as necessary.MIKEmessage edited by mmcconaghy

#1
January 27, 2015 at 08:08:43
 For summing values, would not SUM() be what you want?I'm not at all sure what it is your trying to do.combine duplicate rows on the basis of order #You may be able to do this with a formula, but a marco is the usual route and unfortunately my marco skill are just above nil.Perhaps if you post a small sample of you workbook it would help. But before you do, please read this How-To which explains the use of the < PRE > tags to align your data.http://www.computing.net/howtos/sho...MIKEhttp://www.skeptic.com/

Report •

#2
January 27, 2015 at 17:22:03
 Thanks, let me know if the following helps...```Order Number Order Date Order Type Supplier Account Code Distribution Amount 51300 12/1/2014 STANDARD SHC 101-566100-6101-1 465.67 51302 12/1/2014 STANDARD MBC 101-7200-7036-1 208.17 51302 12/1/2014 STANDARD MBC 51312 12/1/2014 STANDARD APT 101-5400-6020-1 87.15 51312 12/1/2014 STANDARD APT 101-5600-6020-1 17.85 51312 12/1/2014 STANDARD APT 101-5400-6020-1 223.61 51312 12/1/2014 STANDARD APT 101-5600-6020-1 45.8 `````` Order Number Order Date Order Type Supplier Account Code Distribution Amount 51300 12/1/2014 STANDARD SHC 101-566100-6101-1 465.67 51302 12/1/2014 STANDARD MBH 101-7200-7036-1 208.17 51312 12/1/2014 STANDARD APT 101-5600-6020-1 374.41 ```message edited by ocm1

Report •

#3
January 27, 2015 at 20:33:41
 Try this and see if it helps:Copy Columns A thru E from Sheet 1( Order Number, Order Date, Order Type, Supplier Account Code)of your data set to Sheet 2, do not copy the Amounts.Then on the Data tab, select Remove DuplicatesUse only the Order Numbers or Column AYou should now have your three rows of unique Order numberswith no Amounts.``` A B C D E 1) Order Number Order Date Order Type Supplier Account Code 2) 51300 12/1/2014 STANDARD SHC 101-566100-6101-1 3) 51302 12/1/2014 STANDARD MBC 101-7200-7036-1 4) 51312 12/1/2014 STANDARD APT 101-5400-6020-1 ```In Cell F2, enter this Array Formula:=SUM((Sheet1!\$A\$2:\$A\$8=A2)*(Sheet1!\$F\$2:\$F\$8))You MUST use CTRL-SHIFT-ENTER for an Array formula.Drag down as many rows as needed.You can modify the range of cells to match your real data.MIKEhttp://www.skeptic.com/

Report •

Related Solutions

#4
January 31, 2015 at 20:12:03
 Mike, I tried the following formula but got circular reference error. =SUM((Sheet1!\$A\$2:\$A\$387 = A2)*(Sheet1!\$F\$2:\$F\$236))“Microsoft Excel cannot calculate a formula. There is a circular reference In an open workbook…” and I got “0” all the way down.TIA,

Report •

#5
February 1, 2015 at 08:27:52
 Not sure why your getting a circular reference errorbut why are your two arrays of different size?How can you have 387 rows of Order numbersbut only 236 rows of Amounts?The two arrays must be the same size.So you want:=SUM((Sheet1!\$A\$2:\$A\$387 = A2)*(Sheet1!\$F\$2:\$F\$387))and the formula MUST be entered using CTRL-SHIFT-ENTER and the formula goes on Sheet 2See if the new formula works.I tested in a workbook, using your data, which I repeated down to row 387.MIKEhttp://www.skeptic.com/

Report •

#6
February 1, 2015 at 10:13:01
 Here is a NON Array formula, see if it works any better for you:=SUMPRODUCT((Sheet1!\$A\$2:\$A\$387=A2)*(Sheet1!\$F\$2:\$F\$387))You DO NOT have to use CTRL-SHIFT-ENTER,The two arrays must be the same sizeand the formula goes on Sheet 2MIKEhttp://www.skeptic.com/

Report •

#7
February 1, 2015 at 13:53:53
 Mike, thanks, My sheet 1=order report and sheet 2= receiving report when you say copy A thru E from sheet1 to sheet 2 does it mean underneath Sheet 2 (receiving report) in my case? My end goal is to find out the difference (order report – receiving report) I’m interested in the delta values. if possible in a new sheet.Please let me know if you have any questions.TIA Regards,

Report •

#8
February 1, 2015 at 14:34:23
 Sorry, we have a communications error.I was unaware that you had an actual Sheet 2,I probably should have said to copy to a NEW sheet.So if you already have a Sheet 1 & Sheet 2copy the data to Sheet 3, the formula should still work.Copy Columns A thru E from Sheet 1( Order Number, Order Date, Order Type, Supplier Account Code)of your data set to a NEW Sheet, do not copy the Amounts.Then on the Data tab, select Remove DuplicatesUse only the Order Numbers or Column AYou should now have your three rows of unique Order numberswith no Amounts and it should look something like this:``` A B C D E 1) Order Number Order Date Order Type Supplier Account Code 2) 51300 12/1/2014 STANDARD SHC 101-566100-6101-1 3) 51302 12/1/2014 STANDARD MBC 101-7200-7036-1 4) 51312 12/1/2014 STANDARD APT 101-5400-6020-1 ```In cell F2 enter your array formula:=SUM((Sheet1!\$A\$2:\$A\$387=A2)*(Sheet1!\$F\$2:\$F\$387))or the NON array formula:=SUMPRODUCT((Sheet1!\$A\$2:\$A\$387=A2)*(Sheet1!\$F\$2:\$F\$387))Since I do not know how many unique Order numbers you haveDrag down as many rows as necessary.MIKEmessage edited by mmcconaghy