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

 

[Akashwani]

Hi,

I'm not sure how accurate or correct this is likely to be, but based on the sample data you have provided, does this work?....

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Order#	Line#	Amount	Gross Weight	Net Weight	Country		Order#	Amount	Gross	Net	Country
2	12000	1	100	10	9	US		12000	620	10	9	US
3	12000	2	20	10	9	US		13000	450	30	28	UK
4	12000	3	500	10	9	US
5	13000	1	200	30	28	UK
6	13000	2	200	30	28	UK
7	13000	3	50	30	28	UK
8
Sheet15

The formula in H2 needs to be entered using ctrl shift enter NOT just enter, it can then be copied down.
The formula in I2 needs to be copied down.
The formula in J2 needs to be entered using ctrl shift enter NOT just enter, it can then be copied across and down.
The formula in L2 needs to be copied down.

You can find very useful information for some of these formulas here...

Create a list of distinct values from a list where an adjacent cell value meets a criteria in excel | Get Digital Help - Microsoft Excel resource

I hope that helps.

Ak

 

[Peter_SSs]

Ak

With your layout, wouldn't J2 be simpler with a direct VLOOKUP as well?

=VLOOKUP(H2,$A$2:$D$7,4,0)

or perhaps

=IF(H2="","",VLOOKUP(H2,$A$2:$D$7,4,0))

Similar for K2.

 

[Akashwani]

Hi Peter, I didn't see that!!
I don't know how, I must be getting Excel tunnel vision, I wonder if that is a genuine medical condition!!

Thanks for pointing it out.

Ak

 

[ghee up]

Thank you very very much!
it solved my issue, learned alot
But is that normal that the calculation can take 4hours (i have 7000+ rows)?? when i fill in the formula on first cell and copied it down until row 7000, it took 4hrs to finished.

regards,

ghee up

 

[Akashwani]

Hi ghee up,

I'm not an expert, but 4 hours to calculate 7000+ rows seems very, very exessive.

Did you change my suggested formulas to those by Peter?...

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Order#	Line#	Amount	Gross Weight	Net Weight	Country		Order#	Amount	Gross	Net	Country
2	12000	1	100	10	9	US		12000	620	10	9	US
3	12000	2	20	10	9	US		13000	450	30	28	UK
4	12000	3	500	10	9	US
5	13000	1	200	30	28	UK			Pivot Table
6	13000	2	200	30	28	UK			Values
7	13000	3	50	30	28	UK		Order#	Sum of Amount	Average of Gross Weight	Average of Net Weight
8								12000	620	10	9	US
9								13000	450	30	28	UK
10
Sheet3

Changes cell references to suit and copy formulas down.
You may be better off using Pivot Table and Vlookup as in the example above in cells H5:L9.

To create a Pivot Table...

Highlight your range of data.
Press alt N V T
Click Existing Worksheet.
Click in the white box (Location).
Click a cell where you want your Pivot Table to start (eg.. H1)

In your Pivot Table Field List...
Drag Order# to the Row Labels Field.
Drag Amount, Gross Weight, Net Weight to the Values Field.

Your Pivot Table should be nicely organised now.
Right click Grand Total, click Remove Grand Total.
Right click Sum of Gross Amount, click Field Value Settings, select Average.
Right click Sum of Net Amount, click Field Value Settings, select Average.

Create a VLOOKUP for the Country and you are done.

I hope that helps.

Ak

 

[Peter_SSs]

But is that normal that the calculation can take 4hours (i have 7000+ rows)?? when i fill in the formula on first cell and copied it down until row 7000, it took 4hrs to finished.

Wow, you had enormous patience to wait for that!

Consider trying a macro approach?
Try this in a copy of your workbook.

Sub ghee_up()
  Dim a, b
  Dim uba As Long, i As Long, k As Long
  Dim s As Double
  
  a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 6).Value
  uba = UBound(a, 1)
  ReDim b(1 To uba, 1 To 6)
  For i = 2 To uba
    If a(i, 1) <> a(i - 1, 1) Then
      k = k + 1
      b(k, 1) = a(i - 1, 1)
      b(k, 2) = s
      b(k, 3) = a(i - 1, 4)
      b(k, 4) = a(i - 1, 5)
      b(k, 5) = a(i - 1, 6)
      s = a(i, 3)
    Else
      s = s + a(i, 3)
    End If
  Next i
  Range("H1").Resize(k, 5).Value = b
  Range("I1").Value = "Amount"
End Sub

 

[ghee up]

Yes i did try Peter's formula but it is the first one that took long to calculate (IFERROR). i first typed it in the the cell and when i copied down to the last row (7000) then that when my excel file started to hung and then i saw that message calculating. So i just left it, did some other stuffs outside excel and yeah it finished to calculate in 4hrs the vlookups are okay, no issue.
thank you!!

Mr Peter,

i am not a macro person, i know little about it. But would you mind explain a bit about the macro that you just put in for me. i will copy it in my workbook but if i can understand as well, it would be really nice
Like, what are those letters stands for "k, i, b, s".. and what does Dim means in macro language?
thanks so much!!

 

[Peter_SSs]

i am not a macro person, i know little about it. But would you mind explain a bit about the macro that you just put in for me. i will copy it in my workbook but if i can understand as well, it would be really nice
Like, what are those letters stands for "k, i, b, s".. and what does Dim means in macro language?
thanks so much!!

Some of the questions you have asked go to the basics of writing a macro. I'm afraid I can't give lessons here but if you Google something like "writing my first macro in excel" I'm sure you'll find some interesting reading and practice examples.

I can give an outline of what my macro does but before doing that, let's see if it does what you want - otherwise I would be wasting my time.

As I said before, make a copy of your workbook and with that workbook open and the sheet your data is on as the active sheet ..

1. Press Alt+F11 to open the Visual Basic window

2. In the VB window, use the menus to Insert|Module

3. Copy my code and paste into the main right hand pane that opens at step 2

4. Close the VB window

5. Back at your sheet press Alt+F8 to bring up the Macro dialog, select the ghee_up macro and click Run

 

[jimmy90]

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.