question on getting totals of 2 columns and sum of 1 column

[ghee up]

hi experts,

my data consists of the ff;

Order# Line# Amount Gross Weight Net Weight Country
12000 1 100 10 9 US
12000 2 20 10 9 US
12000 3 500 10 9 US
13000 1 200 30 28 UK
13000 2 200 30 28 UK
13000 3 50 30 28 UK

what happen is, the Amount is breakdown per line # but the Gross and Net weigths are taken as a whole (the weight of the whole order)

what i would just like to come up is this as final output;

Order# Amount Gross Weight Net Weight Country
12000 620 10 9 US
13000 450 30 28 UK

pivot sum up the weights, i hope its possible to just sum the amount and not the weights.
thank you for your continued help.

regards,
ghee up

 

[Peter_SSs]

In your OP you mention using a Pivot Table but having the values sum. Re-create your Pivot table then right click on the Field Names/Headers. And Select "Value Field Settings", from the context menu. In that Menu change the ListBox that is Highlighted as Sum to either Max/Min/Average. Do this for all the fields you want to be kept the same and not summed. This will be faster then any formula or macro approach.

Jimmy, your post caused me to re-read the problem. A Pivot Table may be the simplest way to go, though presumably it would need a VLOOKUP (or similar) formula column appended to get the Country text per the OP's output layout.

As to speed, I'm not so sure. Perhaps my sample data is not representative or my timing of the Pivot Table option may not be appropriate but for my 7,000 rows of data (with 9 different order numbers & data sorted by order number) the code I posted averaged 0.030 seconds over 3 runs whereas running the code below (after creating the Pivot Table manually) averaged 0.047 seconds.

Sub PT()
  Dim t As Single
  t = Timer
  ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
  MsgBox "Code took " & Format(Timer - t, "0.000 secs")
End Sub

 

[ghee up]

thank you all for your help on this.
i have not tried the macro yet, will start to watch some video tutorials on it.