Microsoft Office 2010 professional (full...

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

✔ Best Answer

Sorry, we have a communications error. I was unaware that you had an

actualSheet 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 aNEW Sheet, do not copy the Amounts.Then on the Data tab, select Remove Duplicates

Use only the Order Numbers or Column AYou 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

message edited by mmcconaghy

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

maybe 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

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.8Order 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

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

Datatab, selectRemove Duplicates

Use only the Order Numbers or Column AYou 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-1In 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

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,

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 2See if the new formula works.

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

MIKE

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 2MIKE

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,

Sorry, we have a communications error. I was unaware that you had an

actualSheet 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 aNEW Sheet, do not copy the Amounts.Then on the Data tab, select Remove Duplicates

Use only the Order Numbers or Column AYou 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

message edited by mmcconaghy

Ask Your Question

Weekly Poll