Dear Sir,

I have the following worksheet data:

A B C D

Vehicle Speedometer Mileage
1 BBN2361 9650
2 BBN2361 10600
3 BBN2631 12800 xxxxx (+B3-B1)
4 BSK1400 8500
5 BSK1400 9320
6 BSK1400 10250 xxxxx (+B6-B4)
7 BGA2367 12500
8 BGA2367 14260
9 BGA2367 16850 xxxxx (+B9-B7)

The mileage is calculated by getting the difference between the last and the
first speedometer reading of the same verhicle.

May I know if there is any formula that I can input at C1 and copy down to
get straight annswer at C3, C6 and C9 ? Other cells in column C is left
blank.


Thanks

Low



--
A36B58K641

Assuming you have headers in row 1, put this in C4 and copy down.........

=IF(A4=A5,"",B4-B2)

Vaya con Dios,
Chuck, CABGx3



"Mr. Low" wrote:

Dear Sir,

I have the following worksheet data:

A B C D

Vehicle Speedometer Mileage
1 BBN2361 9650
2 BBN2361 10600
3 BBN2631 12800 xxxxx (+B3-B1)
4 BSK1400 8500
5 BSK1400 9320
6 BSK1400 10250 xxxxx (+B6-B4)
7 BGA2367 12500
8 BGA2367 14260
9 BGA2367 16850 xxxxx (+B9-B7)

The mileage is calculated by getting the difference between the last and the
first speedometer reading of the same verhicle.

May I know if there is any formula that I can input at C1 and copy down to
get straight annswer at C3, C6 and C9 ? Other cells in column C is left
blank.


Thanks

Low



--
A36B58K641

Hello Vaya,

Your formula works very wekk when the number of row is consistant at 3.

However this formula does not work when the number of rows of each vehicle
is not consistant as folows:


Vehicle Speedometer Mileage
BBN2361 9,650.00
BBN2361 10,600.00
BBN2631 12,800.00 xxxx
BSK1400 8,500.00
BSK1400 12,500.00
BSK1400 11,500.00
BSK1400 14,200.00
BSK1400 16,800.00
BSK1400 10,250.00 xxxx
BGA2367 12,500.00
BGA2367 12,500.00
BGA2367 12,500.00
BGA2367 16,850.00 xxxx

Is there any other formula of getting the difference other than running a
macro ?

Thanks

Low



--
A36B58K641


"CLR" wrote:

Assuming you have headers in row 1, put this in C4 and copy down.........

=IF(A4=A5,"",B4-B2)

Vaya con Dios,
Chuck, CABGx3



"Mr. Low" wrote:

Dear Sir,

I have the following worksheet data:

A B C D

Vehicle Speedometer Mileage
1 BBN2361 9650
2 BBN2361 10600
3 BBN2631 12800 xxxxx (+B3-B1)
4 BSK1400 8500
5 BSK1400 9320
6 BSK1400 10250 xxxxx (+B6-B4)
7 BGA2367 12500
8 BGA2367 14260
9 BGA2367 16850 xxxxx (+B9-B7)

The mileage is calculated by getting the difference between the last and the
first speedometer reading of the same verhicle.

May I know if there is any formula that I can input at C1 and copy down to
get straight annswer at C3, C6 and C9 ? Other cells in column C is left
blank.


Thanks

Low



--
A36B58K641

Assuming you have your columns A and B sorted first on A then on B ascending,
and you place this in B2 and copy down......

=IF(A2=A3,"",B2-VLOOKUP(A2,A:B,2,FALSE))

Vaya con Dios,
Chuck, CABGx3




"Mr. Low" wrote:

Hello Vaya,

Your formula works very wekk when the number of row is consistant at 3.

However this formula does not work when the number of rows of each vehicle
is not consistant as folows:


Vehicle Speedometer Mileage
BBN2361 9,650.00
BBN2361 10,600.00
BBN2631 12,800.00 xxxx
BSK1400 8,500.00
BSK1400 12,500.00
BSK1400 11,500.00
BSK1400 14,200.00
BSK1400 16,800.00
BSK1400 10,250.00 xxxx
BGA2367 12,500.00
BGA2367 12,500.00
BGA2367 12,500.00
BGA2367 16,850.00 xxxx

Is there any other formula of getting the difference other than running a
macro ?

Thanks

Low



--
A36B58K641


"CLR" wrote:

Assuming you have headers in row 1, put this in C4 and copy down.........

=IF(A4=A5,"",B4-B2)

Vaya con Dios,
Chuck, CABGx3



"Mr. Low" wrote:

Dear Sir,

I have the following worksheet data:

A B C D

Vehicle Speedometer Mileage
1 BBN2361 9650
2 BBN2361 10600
3 BBN2631 12800 xxxxx (+B3-B1)
4 BSK1400 8500
5 BSK1400 9320
6 BSK1400 10250 xxxxx (+B6-B4)
7 BGA2367 12500
8 BGA2367 14260
9 BGA2367 16850 xxxxx (+B9-B7)

The mileage is calculated by getting the difference between the last and the
first speedometer reading of the same verhicle.

May I know if there is any formula that I can input at C1 and copy down to
get straight annswer at C3, C6 and C9 ? Other cells in column C is left
blank.


Thanks

Low



--
A36B58K641

Oh, forgot to mention, in order for this to work, all your PartNumbers have
to be the same......I noticed some had leading and/or trailing spaces which
will mess up the VLOOKUP..........

Vaya con Dios,
Chuck, CABGx3



"CLR" wrote:

Assuming you have your columns A and B sorted first on A then on B ascending,
and you place this in B2 and copy down......

=IF(A2=A3,"",B2-VLOOKUP(A2,A:B,2,FALSE))

Vaya con Dios,
Chuck, CABGx3




"Mr. Low" wrote:

Hello Vaya,

Your formula works very wekk when the number of row is consistant at 3.

However this formula does not work when the number of rows of each vehicle
is not consistant as folows:


Vehicle Speedometer Mileage
BBN2361 9,650.00
BBN2361 10,600.00
BBN2631 12,800.00 xxxx
BSK1400 8,500.00
BSK1400 12,500.00
BSK1400 11,500.00
BSK1400 14,200.00
BSK1400 16,800.00
BSK1400 10,250.00 xxxx
BGA2367 12,500.00
BGA2367 12,500.00
BGA2367 12,500.00
BGA2367 16,850.00 xxxx

Is there any other formula of getting the difference other than running a
macro ?

Thanks

Low



--
A36B58K641


"CLR" wrote:

Assuming you have headers in row 1, put this in C4 and copy down.........

=IF(A4=A5,"",B4-B2)

Vaya con Dios,
Chuck, CABGx3



"Mr. Low" wrote:

Dear Sir,

I have the following worksheet data:

A B C D

Vehicle Speedometer Mileage
1 BBN2361 9650
2 BBN2361 10600
3 BBN2631 12800 xxxxx (+B3-B1)
4 BSK1400 8500
5 BSK1400 9320
6 BSK1400 10250 xxxxx (+B6-B4)
7 BGA2367 12500
8 BGA2367 14260
9 BGA2367 16850 xxxxx (+B9-B7)

The mileage is calculated by getting the difference between the last and the
first speedometer reading of the same verhicle.

May I know if there is any formula that I can input at C1 and copy down to
get straight annswer at C3, C6 and C9 ? Other cells in column C is left
blank.


Thanks

Low



--
A36B58K641

Hello Vaya,

Your formula =IF(A2=A3,"",B2-VLOOKUP(A2,A:B,2,FALSE)) works very well.

It is simple and easy to understand.

Many thanks

Low Seng Kuang


--
A36B58K641


"CLR" wrote:

Assuming you have your columns A and B sorted first on A then on B ascending,
and you place this in B2 and copy down......

=IF(A2=A3,"",B2-VLOOKUP(A2,A:B,2,FALSE))

Vaya con Dios,
Chuck, CABGx3




"Mr. Low" wrote:

Hello Vaya,

Your formula works very wekk when the number of row is consistant at 3.

However this formula does not work when the number of rows of each vehicle
is not consistant as folows:


Vehicle Speedometer Mileage
BBN2361 9,650.00
BBN2361 10,600.00
BBN2631 12,800.00 xxxx
BSK1400 8,500.00
BSK1400 12,500.00
BSK1400 11,500.00
BSK1400 14,200.00
BSK1400 16,800.00
BSK1400 10,250.00 xxxx
BGA2367 12,500.00
BGA2367 12,500.00
BGA2367 12,500.00
BGA2367 16,850.00 xxxx

Is there any other formula of getting the difference other than running a
macro ?

Thanks

Low



--
A36B58K641


"CLR" wrote:

Assuming you have headers in row 1, put this in C4 and copy down.........

=IF(A4=A5,"",B4-B2)

Vaya con Dios,
Chuck, CABGx3



"Mr. Low" wrote:

Dear Sir,

I have the following worksheet data:

A B C D

Vehicle Speedometer Mileage
1 BBN2361 9650
2 BBN2361 10600
3 BBN2631 12800 xxxxx (+B3-B1)
4 BSK1400 8500
5 BSK1400 9320
6 BSK1400 10250 xxxxx (+B6-B4)
7 BGA2367 12500
8 BGA2367 14260
9 BGA2367 16850 xxxxx (+B9-B7)

The mileage is calculated by getting the difference between the last and the
first speedometer reading of the same verhicle.

May I know if there is any formula that I can input at C1 and copy down to
get straight annswer at C3, C6 and C9 ? Other cells in column C is left
blank.


Thanks

Low



--
A36B58K641

From a post of mine yesterday. Modify to suit

Sub altrowformula()
For i = 2 To 12 Step 2
Cells(i, "H").Formula = "=a" & i / 2 & "+3"
Next i
End Sub


--
Don Guillett
SalesAid Software

"Mr. Low" wrote in message
...
Dear Sir,

I have the following worksheet data:

A B C D

Vehicle Speedometer Mileage
1 BBN2361 9650
2 BBN2361 10600
3 BBN2631 12800 xxxxx (+B3-B1)
4 BSK1400 8500
5 BSK1400 9320
6 BSK1400 10250 xxxxx (+B6-B4)
7 BGA2367 12500
8 BGA2367 14260
9 BGA2367 16850 xxxxx (+B9-B7)

The mileage is calculated by getting the difference between the last and
the
first speedometer reading of the same verhicle.

May I know if there is any formula that I can input at C1 and copy down to
get straight annswer at C3, C6 and C9 ? Other cells in column C is left
blank.


Thanks

Low



--
A36B58K641

Hello Don,

I have redorded this Macro code but was unable to run due to run time error.

I have email my workings and error mesage to you at your e-mail address.

I would be glad if you could help me to fix the problem.

Thank you

Low


--
A36B58K641


"Don Guillett" wrote:

From a post of mine yesterday. Modify to suit

Sub altrowformula()
For i = 2 To 12 Step 2
Cells(i, "H").Formula = "=a" & i / 2 & "+3"
Next i
End Sub


--
Don Guillett
SalesAid Software

"Mr. Low" wrote in message
...
Dear Sir,

I have the following worksheet data:

A B C D

Vehicle Speedometer Mileage
1 BBN2361 9650
2 BBN2361 10600
3 BBN2631 12800 xxxxx (+B3-B1)
4 BSK1400 8500
5 BSK1400 9320
6 BSK1400 10250 xxxxx (+B6-B4)
7 BGA2367 12500
8 BGA2367 14260
9 BGA2367 16850 xxxxx (+B9-B7)

The mileage is calculated by getting the difference between the last and
the
first speedometer reading of the same verhicle.

May I know if there is any formula that I can input at C1 and copy down to
get straight annswer at C3, C6 and C9 ? Other cells in column C is left
blank.


Thanks

Low



--
A36B58K641

This should do it.

Sub SumByCategory()
lr = Cells(Rows.Count, "a").End(xlUp).Row + 1
mr = 2
Do Until Cells(mr, 2) = 0
mname = Cells(mr, 1)
nr = Columns(1).Find(mname, after:=Cells(lr, 1), _
searchdirection:=xlPrevious).Row
Cells(nr, 3) = Cells(nr, 2) - Cells(mr, 2)
mr = nr + 1
Loop
End Sub
--
Don Guillett
SalesAid Software

"Mr. Low" wrote in message
...
Dear Sir,

I have the following worksheet data:

A B C D

Vehicle Speedometer Mileage
1 BBN2361 9650
2 BBN2361 10600
3 BBN2631 12800 xxxxx (+B3-B1)
4 BSK1400 8500
5 BSK1400 9320
6 BSK1400 10250 xxxxx (+B6-B4)
7 BGA2367 12500
8 BGA2367 14260
9 BGA2367 16850 xxxxx (+B9-B7)

The mileage is calculated by getting the difference between the last and
the
first speedometer reading of the same verhicle.

May I know if there is any formula that I can input at C1 and copy down to
get straight annswer at C3, C6 and C9 ? Other cells in column C is left
blank.


Thanks

Low



--
A36B58K641

Cool, Don.........but as with my formula solution, the data must be
pre-sorted and all PN's the same with no leading/trailing space
differences..........

Vaya con Dios,
Chuck, CABGx3



"Don Guillett" wrote:

This should do it.

Sub SumByCategory()
lr = Cells(Rows.Count, "a").End(xlUp).Row + 1
mr = 2
Do Until Cells(mr, 2) = 0
mname = Cells(mr, 1)
nr = Columns(1).Find(mname, after:=Cells(lr, 1), _
searchdirection:=xlPrevious).Row
Cells(nr, 3) = Cells(nr, 2) - Cells(mr, 2)
mr = nr + 1
Loop
End Sub
--
Don Guillett
SalesAid Software

"Mr. Low" wrote in message
...
Dear Sir,

I have the following worksheet data:

A B C D

Vehicle Speedometer Mileage
1 BBN2361 9650
2 BBN2361 10600
3 BBN2631 12800 xxxxx (+B3-B1)
4 BSK1400 8500
5 BSK1400 9320
6 BSK1400 10250 xxxxx (+B6-B4)
7 BGA2367 12500
8 BGA2367 14260
9 BGA2367 16850 xxxxx (+B9-B7)

The mileage is calculated by getting the difference between the last and
the
first speedometer reading of the same verhicle.

May I know if there is any formula that I can input at C1 and copy down to
get straight annswer at C3, C6 and C9 ? Other cells in column C is left
blank.


Thanks

Low



--
A36B58K641

Thanks. How about this from a list but no sorting required.
Of course, this is an array formula so it must be entered with
ctrl+shift+enter

=MAX(IF(A2:A221=A2,B2:B221))-MIN(IF(A2:A221=A2,B2:B221))

--
Don Guillett
SalesAid Software

"CLR" wrote in message
...
Cool, Don.........but as with my formula solution, the data must be
pre-sorted and all PN's the same with no leading/trailing space
differences..........

Vaya con Dios,
Chuck, CABGx3



"Don Guillett" wrote:

This should do it.

Sub SumByCategory()
lr = Cells(Rows.Count, "a").End(xlUp).Row + 1
mr = 2
Do Until Cells(mr, 2) = 0
mname = Cells(mr, 1)
nr = Columns(1).Find(mname, after:=Cells(lr, 1), _
searchdirection:=xlPrevious).Row
Cells(nr, 3) = Cells(nr, 2) - Cells(mr, 2)
mr = nr + 1
Loop
End Sub
--
Don Guillett
SalesAid Software

"Mr. Low" wrote in message
...
Dear Sir,

I have the following worksheet data:

A B C
D

Vehicle Speedometer Mileage
1 BBN2361 9650
2 BBN2361 10600
3 BBN2631 12800 xxxxx (+B3-B1)
4 BSK1400 8500
5 BSK1400 9320
6 BSK1400 10250 xxxxx (+B6-B4)
7 BGA2367 12500
8 BGA2367 14260
9 BGA2367 16850 xxxxx (+B9-B7)

The mileage is calculated by getting the difference between the last
and
the
first speedometer reading of the same verhicle.

May I know if there is any formula that I can input at C1 and copy down
to
get straight annswer at C3, C6 and C9 ? Other cells in column C is
left
blank.


Thanks

Low



--
A36B58K641

Hmmmm.........I liked your code solution better, those array-thingys make my
head hurt...<g

Vaya con Dios,
Chuck, CABGx3



"Don Guillett" wrote:

Thanks. How about this from a list but no sorting required.
Of course, this is an array formula so it must be entered with
ctrl+shift+enter

=MAX(IF(A2:A221=A2,B2:B221))-MIN(IF(A2:A221=A2,B2:B221))

--
Don Guillett
SalesAid Software

"CLR" wrote in message
...
Cool, Don.........but as with my formula solution, the data must be
pre-sorted and all PN's the same with no leading/trailing space
differences..........

Vaya con Dios,
Chuck, CABGx3



"Don Guillett" wrote:

This should do it.

Sub SumByCategory()
lr = Cells(Rows.Count, "a").End(xlUp).Row + 1
mr = 2
Do Until Cells(mr, 2) = 0
mname = Cells(mr, 1)
nr = Columns(1).Find(mname, after:=Cells(lr, 1), _
searchdirection:=xlPrevious).Row
Cells(nr, 3) = Cells(nr, 2) - Cells(mr, 2)
mr = nr + 1
Loop
End Sub
--
Don Guillett
SalesAid Software

"Mr. Low" wrote in message
...
Dear Sir,

I have the following worksheet data:

A B C
D

Vehicle Speedometer Mileage
1 BBN2361 9650
2 BBN2361 10600
3 BBN2631 12800 xxxxx (+B3-B1)
4 BSK1400 8500
5 BSK1400 9320
6 BSK1400 10250 xxxxx (+B6-B4)
7 BGA2367 12500
8 BGA2367 14260
9 BGA2367 16850 xxxxx (+B9-B7)

The mileage is calculated by getting the difference between the last
and
the
first speedometer reading of the same verhicle.

May I know if there is any formula that I can input at C1 and copy down
to
get straight annswer at C3, C6 and C9 ? Other cells in column C is
left
blank.


Thanks

Low



--
A36B58K641

or this array formula which will put the total at the 1st instance of the
category instead of the last instance.
=IF(COUNTIF($A$2:A2,A2)1,"",MAX(IF(A2:A21=A2,B2:B 21))-MIN(IF(A2:A21=A2,B2:B21)))
Then the group could be filtered.
--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
Thanks. How about this from a list but no sorting required.
Of course, this is an array formula so it must be entered with
ctrl+shift+enter

=MAX(IF(A2:A221=A2,B2:B221))-MIN(IF(A2:A221=A2,B2:B221))

--
Don Guillett
SalesAid Software

"CLR" wrote in message
...
Cool, Don.........but as with my formula solution, the data must be
pre-sorted and all PN's the same with no leading/trailing space
differences..........

Vaya con Dios,
Chuck, CABGx3



"Don Guillett" wrote:

This should do it.

Sub SumByCategory()
lr = Cells(Rows.Count, "a").End(xlUp).Row + 1
mr = 2
Do Until Cells(mr, 2) = 0
mname = Cells(mr, 1)
nr = Columns(1).Find(mname, after:=Cells(lr, 1), _
searchdirection:=xlPrevious).Row
Cells(nr, 3) = Cells(nr, 2) - Cells(mr, 2)
mr = nr + 1
Loop
End Sub
--
Don Guillett
SalesAid Software

"Mr. Low" wrote in message
...
Dear Sir,

I have the following worksheet data:

A B C D

Vehicle Speedometer Mileage
1 BBN2361 9650
2 BBN2361 10600
3 BBN2631 12800 xxxxx (+B3-B1)
4 BSK1400 8500
5 BSK1400 9320
6 BSK1400 10250 xxxxx (+B6-B4)
7 BGA2367 12500
8 BGA2367 14260
9 BGA2367 16850 xxxxx (+B9-B7)

The mileage is calculated by getting the difference between the last
and
the
first speedometer reading of the same verhicle.

May I know if there is any formula that I can input at C1 and copy
down to
get straight annswer at C3, C6 and C9 ? Other cells in column C is
left
blank.


Thanks

Low



--
A36B58K641

Better, but now I must take TWO pills <g

Vaya con Dios,
Chuck, CABGx3



"Don Guillett" wrote:

or this array formula which will put the total at the 1st instance of the
category instead of the last instance.
=IF(COUNTIF($A$2:A2,A2)1,"",MAX(IF(A2:A21=A2,B2:B 21))-MIN(IF(A2:A21=A2,B2:B21)))
Then the group could be filtered.
--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
Thanks. How about this from a list but no sorting required.
Of course, this is an array formula so it must be entered with
ctrl+shift+enter

=MAX(IF(A2:A221=A2,B2:B221))-MIN(IF(A2:A221=A2,B2:B221))

--
Don Guillett
SalesAid Software

"CLR" wrote in message
...
Cool, Don.........but as with my formula solution, the data must be
pre-sorted and all PN's the same with no leading/trailing space
differences..........

Vaya con Dios,
Chuck, CABGx3



"Don Guillett" wrote:

This should do it.

Sub SumByCategory()
lr = Cells(Rows.Count, "a").End(xlUp).Row + 1
mr = 2
Do Until Cells(mr, 2) = 0
mname = Cells(mr, 1)
nr = Columns(1).Find(mname, after:=Cells(lr, 1), _
searchdirection:=xlPrevious).Row
Cells(nr, 3) = Cells(nr, 2) - Cells(mr, 2)
mr = nr + 1
Loop
End Sub
--
Don Guillett
SalesAid Software

"Mr. Low" wrote in message
...
Dear Sir,

I have the following worksheet data:

A B C D

Vehicle Speedometer Mileage
1 BBN2361 9650
2 BBN2361 10600
3 BBN2631 12800 xxxxx (+B3-B1)
4 BSK1400 8500
5 BSK1400 9320
6 BSK1400 10250 xxxxx (+B6-B4)
7 BGA2367 12500
8 BGA2367 14260
9 BGA2367 16850 xxxxx (+B9-B7)

The mileage is calculated by getting the difference between the last
and
the
first speedometer reading of the same verhicle.

May I know if there is any formula that I can input at C1 and copy
down to
get straight annswer at C3, C6 and C9 ? Other cells in column C is
left
blank.


Thanks

Low



--
A36B58K641

Change to absolutes as shown
=IF(COUNTIF($A$2:A2,A2)1,"",MAX(IF($A$2:$A$20=A2, $B$2:$B$20))-MIN(IF($A$2:$A$20=A2,$B$2:$B$20)))

--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
or this array formula which will put the total at the 1st instance of the
category instead of the last instance.
=IF(COUNTIF($A$2:A2,A2)1,"",MAX(IF(A2:A21=A2,B2:B 21))-MIN(IF(A2:A21=A2,B2:B21)))
Then the group could be filtered.
--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
Thanks. How about this from a list but no sorting required.
Of course, this is an array formula so it must be entered with
ctrl+shift+enter

=MAX(IF(A2:A221=A2,B2:B221))-MIN(IF(A2:A221=A2,B2:B221))

--
Don Guillett
SalesAid Software

"CLR" wrote in message
...
Cool, Don.........but as with my formula solution, the data must be
pre-sorted and all PN's the same with no leading/trailing space
differences..........

Vaya con Dios,
Chuck, CABGx3



"Don Guillett" wrote:

This should do it.

Sub SumByCategory()
lr = Cells(Rows.Count, "a").End(xlUp).Row + 1
mr = 2
Do Until Cells(mr, 2) = 0
mname = Cells(mr, 1)
nr = Columns(1).Find(mname, after:=Cells(lr, 1), _
searchdirection:=xlPrevious).Row
Cells(nr, 3) = Cells(nr, 2) - Cells(mr, 2)
mr = nr + 1
Loop
End Sub
--
Don Guillett
SalesAid Software

"Mr. Low" wrote in message
...
Dear Sir,

I have the following worksheet data:

A B C D

Vehicle Speedometer Mileage
1 BBN2361 9650
2 BBN2361 10600
3 BBN2631 12800 xxxxx (+B3-B1)
4 BSK1400 8500
5 BSK1400 9320
6 BSK1400 10250 xxxxx (+B6-B4)
7 BGA2367 12500
8 BGA2367 14260
9 BGA2367 16850 xxxxx (+B9-B7)

The mileage is calculated by getting the difference between the last
and
the
first speedometer reading of the same verhicle.

May I know if there is any formula that I can input at C1 and copy
down to
get straight annswer at C3, C6 and C9 ? Other cells in column C is
left
blank.


Thanks

Low



--
A36B58K641

Hello Don,

This formula is partially successful.

All the mileage were correct except the fist vehicle.

Vehicle Meter Mileage
BBN2361 9,650 950 <--- error
BBN2361 10,600
BBN2631 12,800
BCA7114 3,200 7400
BCA7114 4,500
BCA7114 6,800
BCA7114 10,600
BFA2129 8,200 8700
BFA2129 10,200
BFA2129 14,800
BFA2129 16,900
BGA2367 12,500 4350
BGA2367 12,500
BGA2367 12,500
BGA2367 16,850
BGS9654 10,200 8000
BGS9654 11,200
BGS9654 14,200
BGS9654 16,200
BGS9654 18,200


Any suggestion for the error ?

Thanks

Low




--
A36B58K641


"Don Guillett" wrote:

or this array formula which will put the total at the 1st instance of the
category instead of the last instance.
=IF(COUNTIF($A$2:A2,A2)1,"",MAX(IF(A2:A21=A2,B2:B 21))-MIN(IF(A2:A21=A2,B2:B21)))
Then the group could be filtered.
--
Don Guillett
SalesAid Software

"Don Guillett" wrote in message
...
Thanks. How about this from a list but no sorting required.
Of course, this is an array formula so it must be entered with
ctrl+shift+enter

=MAX(IF(A2:A221=A2,B2:B221))-MIN(IF(A2:A221=A2,B2:B221))

--
Don Guillett
SalesAid Software

"CLR" wrote in message
...
Cool, Don.........but as with my formula solution, the data must be
pre-sorted and all PN's the same with no leading/trailing space
differences..........

Vaya con Dios,
Chuck, CABGx3



"Don Guillett" wrote:

This should do it.

Sub SumByCategory()
lr = Cells(Rows.Count, "a").End(xlUp).Row + 1
mr = 2
Do Until Cells(mr, 2) = 0
mname = Cells(mr, 1)
nr = Columns(1).Find(mname, after:=Cells(lr, 1), _
searchdirection:=xlPrevious).Row
Cells(nr, 3) = Cells(nr, 2) - Cells(mr, 2)
mr = nr + 1
Loop
End Sub
--
Don Guillett
SalesAid Software

"Mr. Low" wrote in message
...
Dear Sir,

I have the following worksheet data:

A B C D

Vehicle Speedometer Mileage
1 BBN2361 9650
2 BBN2361 10600
3 BBN2631 12800 xxxxx (+B3-B1)
4 BSK1400 8500
5 BSK1400 9320
6 BSK1400 10250 xxxxx (+B6-B4)
7 BGA2367 12500
8 BGA2367 14260
9 BGA2367 16850 xxxxx (+B9-B7)

The mileage is calculated by getting the difference between the last
and
the
first speedometer reading of the same verhicle.

May I know if there is any formula that I can input at C1 and copy
down to
get straight annswer at C3, C6 and C9 ? Other cells in column C is
left
blank.


Thanks

Low



--
A36B58K641

Hello Don,

I tried this out using a new data set of 31 rows. I modified your formula at
cell C2 to

=MAX(IF(A2:A31=A2,B2:B31))-MIN(IF(A2:A31=A2,B2:B31))
and =MAX(IF(A2:A$31=A2,B2:B$31))-MIN(IF(A2:A$31=A2,B2:B$31))

It did not work. The results are not correct as follows (using sorted or
unsorted data):

Vehicle Meter Mileage
BBN2361 9,650 15300
BBN2361 10,600 0
BBN2631 12,800 0
BCA7114 3,200 7400
BCA7114 4,500 6100
BCA7114 6,800 3800
BCA7114 10,600 0
BFA2129 8,200 8700
BFA2129 10,200 6700
BFA2129 14,800 2100
BFA2129 16,900 0
BGA2367 12,500 4350
BGA2367 12,500 4350
BGA2367 12,500 4350
BGA2367 16,850 0
BGS9654 10,200 6000
BGS9654 11,200 5000
BGS9654 14,200 2000
BGS9654 16,200 0
BSK1400 8,500 8300
BSK1400 10,250 6550
BSK1400 11,500 5300
BSK1400 12,500 4300
BSK1400 14,200 2600
BSK1400 16,800 0
PLM3265 9,800 8700
PLM3265 10,200 8300
PLM3265 14,200 4300
PLM3265 16,100 2400
PLM3265 18,500 0

Any suggestion on what errors that were made.

I entered formula using CTRL + SHIFT+ ENTER key.

Thanks


Low

Note : I used MS Excel 2002 at work place and have to try it at home using
MS Ecel 2007 (Beta 2) as I do not have the former at home







--
A36B58K641


"Don Guillett" wrote:

Thanks. How about this from a list but no sorting required.
Of course, this is an array formula so it must be entered with
ctrl+shift+enter

=MAX(IF(A2:A221=A2,B2:B221))-MIN(IF(A2:A221=A2,B2:B221))

--
Don Guillett
SalesAid Software

"CLR" wrote in message
...
Cool, Don.........but as with my formula solution, the data must be
pre-sorted and all PN's the same with no leading/trailing space
differences..........

Vaya con Dios,
Chuck, CABGx3



"Don Guillett" wrote:

This should do it.

Sub SumByCategory()
lr = Cells(Rows.Count, "a").End(xlUp).Row + 1
mr = 2
Do Until Cells(mr, 2) = 0
mname = Cells(mr, 1)
nr = Columns(1).Find(mname, after:=Cells(lr, 1), _
searchdirection:=xlPrevious).Row
Cells(nr, 3) = Cells(nr, 2) - Cells(mr, 2)
mr = nr + 1
Loop
End Sub
--
Don Guillett
SalesAid Software

"Mr. Low" wrote in message
...
Dear Sir,

I have the following worksheet data:

A B C
D

Vehicle Speedometer Mileage
1 BBN2361 9650
2 BBN2361 10600
3 BBN2631 12800 xxxxx (+B3-B1)
4 BSK1400 8500
5 BSK1400 9320
6 BSK1400 10250 xxxxx (+B6-B4)
7 BGA2367 12500
8 BGA2367 14260
9 BGA2367 16850 xxxxx (+B9-B7)

The mileage is calculated by getting the difference between the last
and
the
first speedometer reading of the same verhicle.

May I know if there is any formula that I can input at C1 and copy down
to
get straight annswer at C3, C6 and C9 ? Other cells in column C is
left
blank.


Thanks

Low



--
A36B58K641

YOUR error is in the data. You will notice that the third entry does not
match the 1st two. Also you must use absolutes as suggested. Correct this in
c2 to ONE line and enter with CSE and then copy DOWN.

=IF(COUNTIF($A$2:A2,A2)1,"",MAX(IF($A$2:$A$40=A2, $B$2:$B$40))-MIN(IF($A$2:$A$40=A2,$B$2:$B$40)))

I also re-ran the macro which must use sorted data and it worked just fine.
Your excel version_________?

I have not gotten any files from you.
--
Don Guillett
SalesAid Software

"Mr. Low" wrote in message
...
Hello Don,

I tried this out using a new data set of 31 rows. I modified your formula
at
cell C2 to

=MAX(IF(A2:A31=A2,B2:B31))-MIN(IF(A2:A31=A2,B2:B31))
and =MAX(IF(A2:A$31=A2,B2:B$31))-MIN(IF(A2:A$31=A2,B2:B$31))

It did not work. The results are not correct as follows (using sorted or
unsorted data):

Vehicle Meter Mileage
BBN2361 9,650 15300
BBN2361 10,600 0
BBN2631 12,800 0
BCA7114 3,200 7400
BCA7114 4,500 6100
BCA7114 6,800 3800
BCA7114 10,600 0
BFA2129 8,200 8700
BFA2129 10,200 6700
BFA2129 14,800 2100
BFA2129 16,900 0
BGA2367 12,500 4350
BGA2367 12,500 4350
BGA2367 12,500 4350
BGA2367 16,850 0
BGS9654 10,200 6000
BGS9654 11,200 5000
BGS9654 14,200 2000
BGS9654 16,200 0
BSK1400 8,500 8300
BSK1400 10,250 6550
BSK1400 11,500 5300
BSK1400 12,500 4300
BSK1400 14,200 2600
BSK1400 16,800 0
PLM3265 9,800 8700
PLM3265 10,200 8300
PLM3265 14,200 4300
PLM3265 16,100 2400
PLM3265 18,500 0

Any suggestion on what errors that were made.

I entered formula using CTRL + SHIFT+ ENTER key.

Thanks


Low

Note : I used MS Excel 2002 at work place and have to try it at home using
MS Ecel 2007 (Beta 2) as I do not have the former at home







--
A36B58K641


"Don Guillett" wrote:

Thanks. How about this from a list but no sorting required.
Of course, this is an array formula so it must be entered with
ctrl+shift+enter

=MAX(IF(A2:A221=A2,B2:B221))-MIN(IF(A2:A221=A2,B2:B221))

--
Don Guillett
SalesAid Software

"CLR" wrote in message
...
Cool, Don.........but as with my formula solution, the data must be
pre-sorted and all PN's the same with no leading/trailing space
differences..........

Vaya con Dios,
Chuck, CABGx3



"Don Guillett" wrote:

This should do it.

Sub SumByCategory()
lr = Cells(Rows.Count, "a").End(xlUp).Row + 1
mr = 2
Do Until Cells(mr, 2) = 0
mname = Cells(mr, 1)
nr = Columns(1).Find(mname, after:=Cells(lr, 1), _
searchdirection:=xlPrevious).Row
Cells(nr, 3) = Cells(nr, 2) - Cells(mr, 2)
mr = nr + 1
Loop
End Sub
--
Don Guillett
SalesAid Software

"Mr. Low" wrote in message
...
Dear Sir,

I have the following worksheet data:

A B C
D

Vehicle Speedometer Mileage
1 BBN2361 9650
2 BBN2361 10600
3 BBN2631 12800 xxxxx (+B3-B1)
4 BSK1400 8500
5 BSK1400 9320
6 BSK1400 10250 xxxxx (+B6-B4)
7 BGA2367 12500
8 BGA2367 14260
9 BGA2367 16850 xxxxx (+B9-B7)

The mileage is calculated by getting the difference between the last
and
the
first speedometer reading of the same verhicle.

May I know if there is any formula that I can input at C1 and copy
down
to
get straight annswer at C3, C6 and C9 ? Other cells in column C is
left
blank.


Thanks

Low



--
A36B58K641

Hello Don,

This macro does not work either, The worksheet encountered problem and had
to shut down prematurely when I run it.

I run the Macro again using the recovered file, the error message code now
is 1004.

I have enabled all the macro to run in the system, the error message still
persist.

The other screen mesages are sent to you at your e-mail address for study.

Thank you anyway at least I have just began to leran how to record and run a
macro program today.

Kind Regards

Low

--
A36B58K641


"Don Guillett" wrote:

This should do it.

Sub SumByCategory()
lr = Cells(Rows.Count, "a").End(xlUp).Row + 1
mr = 2
Do Until Cells(mr, 2) = 0
mname = Cells(mr, 1)
nr = Columns(1).Find(mname, after:=Cells(lr, 1), _
searchdirection:=xlPrevious).Row
Cells(nr, 3) = Cells(nr, 2) - Cells(mr, 2)
mr = nr + 1
Loop
End Sub
--
Don Guillett
SalesAid Software

"Mr. Low" wrote in message
...
Dear Sir,

I have the following worksheet data:

A B C D

Vehicle Speedometer Mileage
1 BBN2361 9650
2 BBN2361 10600
3 BBN2631 12800 xxxxx (+B3-B1)
4 BSK1400 8500
5 BSK1400 9320
6 BSK1400 10250 xxxxx (+B6-B4)
7 BGA2367 12500
8 BGA2367 14260
9 BGA2367 16850 xxxxx (+B9-B7)

The mileage is calculated by getting the difference between the last and
the
first speedometer reading of the same verhicle.

May I know if there is any formula that I can input at C1 and copy down to
get straight annswer at C3, C6 and C9 ? Other cells in column C is left
blank.


Thanks

Low



--
A36B58K641

YOUR error is in the data. You will notice that the third entry does not
match the 1st two. Also you must use absolutes as suggested. Correct this in
c2 to ONE line and enter with CSE and then copy DOWN.

=IF(COUNTIF($A$2:A2,A2)1,"",MAX(IF($A$2:$A$40=A2, $B$2:$B$40))-MIN(IF($A$2:$A$40=A2,$B$2:$B$40)))

I also re-ran the macro which must use sorted data and it worked just fine.
Your excel version_________?

I have not gotten any files from you.


--
Don Guillett
SalesAid Software

"Mr. Low" wrote in message
...
Hello Don,

This macro does not work either, The worksheet encountered problem and had
to shut down prematurely when I run it.

I run the Macro again using the recovered file, the error message code now
is 1004.

I have enabled all the macro to run in the system, the error message still
persist.

The other screen mesages are sent to you at your e-mail address for study.

Thank you anyway at least I have just began to leran how to record and run
a
macro program today.

Kind Regards

Low

--
A36B58K641


"Don Guillett" wrote:

This should do it.

Sub SumByCategory()
lr = Cells(Rows.Count, "a").End(xlUp).Row + 1
mr = 2
Do Until Cells(mr, 2) = 0
mname = Cells(mr, 1)
nr = Columns(1).Find(mname, after:=Cells(lr, 1), _
searchdirection:=xlPrevious).Row
Cells(nr, 3) = Cells(nr, 2) - Cells(mr, 2)
mr = nr + 1
Loop
End Sub
--
Don Guillett
SalesAid Software

"Mr. Low" wrote in message
...
Dear Sir,

I have the following worksheet data:

A B C
D

Vehicle Speedometer Mileage
1 BBN2361 9650
2 BBN2361 10600
3 BBN2631 12800 xxxxx (+B3-B1)
4 BSK1400 8500
5 BSK1400 9320
6 BSK1400 10250 xxxxx (+B6-B4)
7 BGA2367 12500
8 BGA2367 14260
9 BGA2367 16850 xxxxx (+B9-B7)

The mileage is calculated by getting the difference between the last
and
the
first speedometer reading of the same verhicle.

May I know if there is any formula that I can input at C1 and copy down
to
get straight annswer at C3, C6 and C9 ? Other cells in column C is
left
blank.


Thanks

Low



--
A36B58K641

Hello Don,

My mail failed to reach your inbox with the following error mesage:

This report relates to a message you sent with the following header fields:

Return-path:
Received: from tcp-daemon.ipop5.tm.net.my by ipop5.tm.net.my
(iPlanet Messaging Server 5.2 HotFix 1.21 (built Sep 8 2003))
id (original mail from
)
; Sat, 9 Dec 2006 17:13:36 +0800 (SGT)
Received: from av2.tm.net.my ([172.20.0.26])
by ipop5.tm.net.my (iPlanet Messaging Server 5.2 HotFix 1.21 (built Sep 8
2003)) with ESMTP id for
; Sat, 09 Dec 2006 17:13:35 +0800 (SGT)
Received: from bar1.tm.net.my ([192.168.1.176])
by av2.tm.net.my (Sun Java System Messaging Server 6.2-7.05 (built Sep 5
2006)) with ESMTP id for
; Sat, 09 Dec 2006 17:12:13 +0800 (MYT)
Received: from av1.tm.net.my (unknown [172.20.0.25])
by bar1.tm.net.my (Spam Firewall) with ESMTP id 63AF9D01FEDF for
; Sat, 09 Dec 2006 17:13:31 +0800 (MYT)
Received: from LOWSK ([218.111.150.210])
by av1.tm.net.my (Sun Java System Messaging Server 6.2-7.05 (built Sep 5
2006)) with ESMTP id for
; Sat, 09 Dec 2006 17:09:21 +0800 (MYT)
Date: Sat, 09 Dec 2006 17:12:27 +0800
From: Low Seng Kuang
Subject: Excel 2007 - Macro : Run Time Error 1004
To:
Message-id: <000e01c71b72$25d9a230$718ce690$@com
MIME-version: 1.0
X-Mailer: Microsoft Office Outlook 12.0
Content-type: multipart/mixed;
boundary="Boundary_(ID_uFiU5b7VmjMHlinP9kJvmA)"
Content-language: en-us
Thread-index: AccbciUMXrsaYYnNTfGa8MXcyXNdtw==
X-Virus-Scanned: by SF 1 Outbound at tm.net.my

Your message cannot be delivered to the following recipients:

Recipient address:
Reason: Rejection greeting returned by server.
Diagnostic code: smtp;550-hrndva-mx-11.mgw.rr.com E
Remote system: dns;hrndva-02.mgw.rr.com
(TCP|172.23.0.15|52042|24.28.204.30|25) (hrndva-mx-11.mgw.rr.com)

I am using Excel 2007 (Beta) as I do not have Excel 2002 where I use to work
with it in my office. I have modify the data to make all the vwehicle number
consistant
in my file. Perhaps my ISP do not allow me to send this file due to security
reason.
I just can't help it, any suggestion ?

Kind Regards

low


--
A36B58K641


"Don Guillett" wrote:

YOUR error is in the data. You will notice that the third entry does not
match the 1st two. Also you must use absolutes as suggested. Correct this in
c2 to ONE line and enter with CSE and then copy DOWN.

=IF(COUNTIF($A$2:A2,A2)1,"",MAX(IF($A$2:$A$40=A2, $B$2:$B$40))-MIN(IF($A$2:$A$40=A2,$B$2:$B$40)))

I also re-ran the macro which must use sorted data and it worked just fine.
Your excel version_________?

I have not gotten any files from you.


--
Don Guillett
SalesAid Software

"Mr. Low" wrote in message
...
Hello Don,

This macro does not work either, The worksheet encountered problem and had
to shut down prematurely when I run it.

I run the Macro again using the recovered file, the error message code now
is 1004.

I have enabled all the macro to run in the system, the error message still
persist.

The other screen mesages are sent to you at your e-mail address for study.

Thank you anyway at least I have just began to leran how to record and run
a
macro program today.

Kind Regards

Low

--
A36B58K641


"Don Guillett" wrote:

This should do it.

Sub SumByCategory()
lr = Cells(Rows.Count, "a").End(xlUp).Row + 1
mr = 2
Do Until Cells(mr, 2) = 0
mname = Cells(mr, 1)
nr = Columns(1).Find(mname, after:=Cells(lr, 1), _
searchdirection:=xlPrevious).Row
Cells(nr, 3) = Cells(nr, 2) - Cells(mr, 2)
mr = nr + 1
Loop
End Sub
--
Don Guillett
SalesAid Software

"Mr. Low" wrote in message
...
Dear Sir,

I have the following worksheet data:

A B C
D

Vehicle Speedometer Mileage
1 BBN2361 9650
2 BBN2361 10600
3 BBN2631 12800 xxxxx (+B3-B1)
4 BSK1400 8500
5 BSK1400 9320
6 BSK1400 10250 xxxxx (+B6-B4)
7 BGA2367 12500
8 BGA2367 14260
9 BGA2367 16850 xxxxx (+B9-B7)

The mileage is calculated by getting the difference between the last
and
the
first speedometer reading of the same verhicle.

May I know if there is any formula that I can input at C1 and copy down
to
get straight annswer at C3, C6 and C9 ? Other cells in column C is
left
blank.


Thanks

Low



--
A36B58K641

Same problem as the previous one. The mail was bounce back with similar
undelivery mesage.

This report relates to a message you sent with the following header fields:

Return-path:
Received: from tcp-daemon.ipop3.tm.net.my by ipop3.tm.net.my
(iPlanet Messaging Server 5.2 HotFix 1.21 (built Sep 8 2003))
id (original mail from
)
; Sat, 9 Dec 2006 16:31:08 +0800 (SGT)
Received: from av2.tm.net.my ([172.20.0.26])
by ipop3.tm.net.my (iPlanet Messaging Server 5.2 HotFix 1.21 (built Sep 8
2003)) with ESMTP id for
; Sat, 09 Dec 2006 16:31:05 +0800 (SGT)
Received: from LOWSK ([218.111.150.210])
by av2.tm.net.my (Sun Java System Messaging Server 6.2-7.05 (built Sep 5
2006)) with ESMTP id for
; Sat, 09 Dec 2006 16:29:43 +0800 (MYT)
Date: Sat, 09 Dec 2006 16:30:47 +0800
From: Low Seng Kuang
Subject: Excel 2007 : Run Time Error for Macro
To:
Message-id: <000001c71b6c$53f065b0$fbd13110$@com
MIME-version: 1.0
X-Mailer: Microsoft Office Outlook 12.0
Content-type: multipart/mixed;
boundary="Boundary_(ID_24A3uqEeyxKChHvS19xE6g)"
Content-language: en-us
Thread-index: AccbbFJRnMvVKBgvTEe3Z5iGUV+lqA==

Your message cannot be delivered to the following recipients:

Recipient address:
Reason: Rejection greeting returned by server.
Diagnostic code: smtp;550-hrndva-mx-13.mgw.rr.com E
Remote system: dns;hrndva-02.mgw.rr.com
(TCP|172.23.0.13|44989|24.28.204.36|25) (hrndva-mx-13.mgw.rr.com)

Any method of resending successfully ?

Rgds

Low
--
A36B58K641


"Don Guillett" wrote:

YOUR error is in the data. You will notice that the third entry does not
match the 1st two. Also you must use absolutes as suggested. Correct this in
c2 to ONE line and enter with CSE and then copy DOWN.

=IF(COUNTIF($A$2:A2,A2)1,"",MAX(IF($A$2:$A$40=A2, $B$2:$B$40))-MIN(IF($A$2:$A$40=A2,$B$2:$B$40)))

I also re-ran the macro which must use sorted data and it worked just fine.
Your excel version_________?

I have not gotten any files from you.


--
Don Guillett
SalesAid Software

"Mr. Low" wrote in message
...
Hello Don,

This macro does not work either, The worksheet encountered problem and had
to shut down prematurely when I run it.

I run the Macro again using the recovered file, the error message code now
is 1004.

I have enabled all the macro to run in the system, the error message still
persist.

The other screen mesages are sent to you at your e-mail address for study.

Thank you anyway at least I have just began to leran how to record and run
a
macro program today.

Kind Regards

Low

--
A36B58K641


"Don Guillett" wrote:

This should do it.

Sub SumByCategory()
lr = Cells(Rows.Count, "a").End(xlUp).Row + 1
mr = 2
Do Until Cells(mr, 2) = 0
mname = Cells(mr, 1)
nr = Columns(1).Find(mname, after:=Cells(lr, 1), _
searchdirection:=xlPrevious).Row
Cells(nr, 3) = Cells(nr, 2) - Cells(mr, 2)
mr = nr + 1
Loop
End Sub
--
Don Guillett
SalesAid Software

"Mr. Low" wrote in message
...
Dear Sir,

I have the following worksheet data:

A B C
D

Vehicle Speedometer Mileage
1 BBN2361 9650
2 BBN2361 10600
3 BBN2631 12800 xxxxx (+B3-B1)
4 BSK1400 8500
5 BSK1400 9320
6 BSK1400 10250 xxxxx (+B6-B4)
7 BGA2367 12500
8 BGA2367 14260
9 BGA2367 16850 xxxxx (+B9-B7)

The mileage is calculated by getting the difference between the last
and
the
first speedometer reading of the same verhicle.

May I know if there is any formula that I can input at C1 and copy down
to
get straight annswer at C3, C6 and C9 ? Other cells in column C is
left
blank.


Thanks

Low



--
A36B58K641

I have NO problems getting attachments from others. Is it working properly
now?

--
Don Guillett
SalesAid Software

"Mr. Low" wrote in message
...
Same problem as the previous one. The mail was bounce back with similar
undelivery mesage.

This report relates to a message you sent with the following header
fields:

Return-path:
Received: from tcp-daemon.ipop3.tm.net.my by ipop3.tm.net.my
(iPlanet Messaging Server 5.2 HotFix 1.21 (built Sep 8 2003))
id (original mail from
)
; Sat, 9 Dec 2006 16:31:08 +0800 (SGT)
Received: from av2.tm.net.my ([172.20.0.26])
by ipop3.tm.net.my (iPlanet Messaging Server 5.2 HotFix 1.21 (built Sep
8
2003)) with ESMTP id for
; Sat, 09 Dec 2006 16:31:05 +0800 (SGT)
Received: from LOWSK ([218.111.150.210])
by av2.tm.net.my (Sun Java System Messaging Server 6.2-7.05 (built Sep
5
2006)) with ESMTP id for
; Sat, 09 Dec 2006 16:29:43 +0800 (MYT)
Date: Sat, 09 Dec 2006 16:30:47 +0800
From: Low Seng Kuang
Subject: Excel 2007 : Run Time Error for Macro
To:
Message-id: <000001c71b6c$53f065b0$fbd13110$@com
MIME-version: 1.0
X-Mailer: Microsoft Office Outlook 12.0
Content-type: multipart/mixed;
boundary="Boundary_(ID_24A3uqEeyxKChHvS19xE6g)"
Content-language: en-us
Thread-index: AccbbFJRnMvVKBgvTEe3Z5iGUV+lqA==

Your message cannot be delivered to the following recipients:

Recipient address:
Reason: Rejection greeting returned by server.
Diagnostic code: smtp;550-hrndva-mx-13.mgw.rr.com E
Remote system: dns;hrndva-02.mgw.rr.com
(TCP|172.23.0.13|44989|24.28.204.36|25) (hrndva-mx-13.mgw.rr.com)

Any method of resending successfully ?

Rgds

Low
--
A36B58K641


"Don Guillett" wrote:

YOUR error is in the data. You will notice that the third entry does not
match the 1st two. Also you must use absolutes as suggested. Correct this
in
c2 to ONE line and enter with CSE and then copy DOWN.

=IF(COUNTIF($A$2:A2,A2)1,"",MAX(IF($A$2:$A$40=A2, $B$2:$B$40))-MIN(IF($A$2:$A$40=A2,$B$2:$B$40)))

I also re-ran the macro which must use sorted data and it worked just
fine.
Your excel version_________?

I have not gotten any files from you.


--
Don Guillett
SalesAid Software

"Mr. Low" wrote in message
...
Hello Don,

This macro does not work either, The worksheet encountered problem and
had
to shut down prematurely when I run it.

I run the Macro again using the recovered file, the error message code
now
is 1004.

I have enabled all the macro to run in the system, the error message
still
persist.

The other screen mesages are sent to you at your e-mail address for
study.

Thank you anyway at least I have just began to leran how to record and
run
a
macro program today.

Kind Regards

Low

--
A36B58K641


"Don Guillett" wrote:

This should do it.

Sub SumByCategory()
lr = Cells(Rows.Count, "a").End(xlUp).Row + 1
mr = 2
Do Until Cells(mr, 2) = 0
mname = Cells(mr, 1)
nr = Columns(1).Find(mname, after:=Cells(lr, 1), _
searchdirection:=xlPrevious).Row
Cells(nr, 3) = Cells(nr, 2) - Cells(mr, 2)
mr = nr + 1
Loop
End Sub
--
Don Guillett
SalesAid Software

"Mr. Low" wrote in message
...
Dear Sir,

I have the following worksheet data:

A B C
D

Vehicle Speedometer Mileage
1 BBN2361 9650
2 BBN2361 10600
3 BBN2631 12800 xxxxx (+B3-B1)
4 BSK1400 8500
5 BSK1400 9320
6 BSK1400 10250 xxxxx (+B6-B4)
7 BGA2367 12500
8 BGA2367 14260
9 BGA2367 16850 xxxxx (+B9-B7)

The mileage is calculated by getting the difference between the last
and
the
first speedometer reading of the same verhicle.

May I know if there is any formula that I can input at C1 and copy
down
to
get straight annswer at C3, C6 and C9 ? Other cells in column C is
left
blank.


Thanks

Low



--
A36B58K641

If not, send me your email and I will send you a workbook.

--
Don Guillett
SalesAid Software

"Mr. Low" wrote in message
...
Same problem as the previous one. The mail was bounce back with similar
undelivery mesage.

This report relates to a message you sent with the following header
fields:

Return-path:
Received: from tcp-daemon.ipop3.tm.net.my by ipop3.tm.net.my
(iPlanet Messaging Server 5.2 HotFix 1.21 (built Sep 8 2003))
id (original mail from
)
; Sat, 9 Dec 2006 16:31:08 +0800 (SGT)
Received: from av2.tm.net.my ([172.20.0.26])
by ipop3.tm.net.my (iPlanet Messaging Server 5.2 HotFix 1.21 (built Sep
8
2003)) with ESMTP id for
; Sat, 09 Dec 2006 16:31:05 +0800 (SGT)
Received: from LOWSK ([218.111.150.210])
by av2.tm.net.my (Sun Java System Messaging Server 6.2-7.05 (built Sep
5
2006)) with ESMTP id for
; Sat, 09 Dec 2006 16:29:43 +0800 (MYT)
Date: Sat, 09 Dec 2006 16:30:47 +0800
From: Low Seng Kuang
Subject: Excel 2007 : Run Time Error for Macro
To:
Message-id: <000001c71b6c$53f065b0$fbd13110$@com
MIME-version: 1.0
X-Mailer: Microsoft Office Outlook 12.0
Content-type: multipart/mixed;
boundary="Boundary_(ID_24A3uqEeyxKChHvS19xE6g)"
Content-language: en-us
Thread-index: AccbbFJRnMvVKBgvTEe3Z5iGUV+lqA==

Your message cannot be delivered to the following recipients:

Recipient address:
Reason: Rejection greeting returned by server.
Diagnostic code: smtp;550-hrndva-mx-13.mgw.rr.com E
Remote system: dns;hrndva-02.mgw.rr.com
(TCP|172.23.0.13|44989|24.28.204.36|25) (hrndva-mx-13.mgw.rr.com)

Any method of resending successfully ?

Rgds

Low
--
A36B58K641


"Don Guillett" wrote:

YOUR error is in the data. You will notice that the third entry does not
match the 1st two. Also you must use absolutes as suggested. Correct this
in
c2 to ONE line and enter with CSE and then copy DOWN.

=IF(COUNTIF($A$2:A2,A2)1,"",MAX(IF($A$2:$A$40=A2, $B$2:$B$40))-MIN(IF($A$2:$A$40=A2,$B$2:$B$40)))

I also re-ran the macro which must use sorted data and it worked just
fine.
Your excel version_________?

I have not gotten any files from you.


--
Don Guillett
SalesAid Software

"Mr. Low" wrote in message
...
Hello Don,

This macro does not work either, The worksheet encountered problem and
had
to shut down prematurely when I run it.

I run the Macro again using the recovered file, the error message code
now
is 1004.

I have enabled all the macro to run in the system, the error message
still
persist.

The other screen mesages are sent to you at your e-mail address for
study.

Thank you anyway at least I have just began to leran how to record and
run
a
macro program today.

Kind Regards

Low

--
A36B58K641


"Don Guillett" wrote:

This should do it.

Sub SumByCategory()
lr = Cells(Rows.Count, "a").End(xlUp).Row + 1
mr = 2
Do Until Cells(mr, 2) = 0
mname = Cells(mr, 1)
nr = Columns(1).Find(mname, after:=Cells(lr, 1), _
searchdirection:=xlPrevious).Row
Cells(nr, 3) = Cells(nr, 2) - Cells(mr, 2)
mr = nr + 1
Loop
End Sub
--
Don Guillett
SalesAid Software

"Mr. Low" wrote in message
...
Dear Sir,

I have the following worksheet data:

A B C
D

Vehicle Speedometer Mileage
1 BBN2361 9650
2 BBN2361 10600
3 BBN2631 12800 xxxxx (+B3-B1)
4 BSK1400 8500
5 BSK1400 9320
6 BSK1400 10250 xxxxx (+B6-B4)
7 BGA2367 12500
8 BGA2367 14260
9 BGA2367 16850 xxxxx (+B9-B7)

The mileage is calculated by getting the difference between the last
and
the
first speedometer reading of the same verhicle.

May I know if there is any formula that I can input at C1 and copy
down
to
get straight annswer at C3, C6 and C9 ? Other cells in column C is
left
blank.


Thanks

Low



--
A36B58K641