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

Report •


✔ Best Answer
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 2
copy 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 Duplicates
Use only the Order Numbers or Column A

You should now have your three rows of unique Order numbers
with 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 have
Drag down as many rows as necessary.

MIKE

http://www.skeptic.com/

message 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...

MIKE

http://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 Duplicates
Use only the Order Numbers or Column A

You should now have your three rows of unique Order numbers
with 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.

MIKE

http://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 error
but why are your two arrays of different size?

How can you have 387 rows of Order numbers
but 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 2

See if the new formula works.

I tested in a workbook, using your data, which I repeated down to row 387.

MIKE

http://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 size
and the formula goes on Sheet 2

MIKE

http://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
✔ Best Answer
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 2
copy 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 Duplicates
Use only the Order Numbers or Column A

You should now have your three rows of unique Order numbers
with 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 have
Drag down as many rows as necessary.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •


Ask Question