Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 505
Default Excel 2002 : How to get the difference in a block of data ?

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
  #2   Report Post  
Posted to microsoft.public.excel.misc
CLR CLR is offline
external usenet poster
 
Posts: 1,998
Default Excel 2002 : How to get the difference in a block of data ?

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

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 505
Default Excel 2002 : How to get the difference in a block of data ?

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

  #5   Report Post  
Posted to microsoft.public.excel.misc
CLR CLR is offline
external usenet poster
 
Posts: 1,998
Default Excel 2002 : How to get the difference in a block of data ?

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



  #6   Report Post  
Posted to microsoft.public.excel.misc
CLR CLR is offline
external usenet poster
 
Posts: 1,998
Default Excel 2002 : How to get the difference in a block of data ?

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

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,124
Default Excel 2002 : How to get the difference in a block of data ?

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



  #8   Report Post  
Posted to microsoft.public.excel.misc
CLR CLR is offline
external usenet poster
 
Posts: 1,998
Default Excel 2002 : How to get the difference in a block of data ?

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




  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,124
Default Excel 2002 : How to get the difference in a block of data ?

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






  #10   Report Post  
Posted to microsoft.public.excel.misc
CLR CLR is offline
external usenet poster
 
Posts: 1,998
Default Excel 2002 : How to get the difference in a block of data ?

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








  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,124
Default Excel 2002 : How to get the difference in a block of data ?

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







  #12   Report Post  
Posted to microsoft.public.excel.misc
CLR CLR is offline
external usenet poster
 
Posts: 1,998
Default Excel 2002 : How to get the difference in a block of data ?

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








  #13   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,124
Default Excel 2002 : How to get the difference in a block of data ?

As the doctor always says,
"Take 2 aspirin and call me in the morning"

--
Don Guillett
SalesAid Software

"CLR" wrote in message
...
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










  #14   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,124
Default Excel 2002 : How to get the difference in a block of data ?

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









  #15   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 505
Default Excel 2002 : How to get the difference in a block of data ?

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






  #16   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 505
Default Excel 2002 : How to get the difference in a block of data ?

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




  #17   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 505
Default Excel 2002 : How to get the difference in a block of data ?

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

  #18   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 505
Default Excel 2002 : How to get the difference in a block of data ?

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






  #19   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 505
Default Excel 2002 : How to get the difference in a block of data ?

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








  #20   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 505
Default Excel 2002 : How to get the difference in a block of data ?

Hello Don,

All answer were correct except the first verhicle .


Vehicle Meter Mileage
BBN2361 9,650 950 < error: the correct answer should be 3,150
BBN2361 10,600
BBN2631 12,800
BSK1400 8,500 8350 < correct
BSK1400 12,500
BSK1400 11,500
BSK1400 14,200
BSK1400 10,250
BSK1400 16,850
BGA2367 12,500 4350 < correct
BGA2367 12,500
BGA2367 12,500
BGA2367 16,850
BFA2129 8,200 8700 < correct
BFA2129 10,200
BFA2129 14,800
BFA2129 16,900
BCA7114 3,200 1300 < correct
BCA7114 4,500

Do you know the reason of the error ?

Note: Formula at C2:
=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)))



Thanks

Low

--
A36B58K641


"Don Guillett" wrote:

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












  #21   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,124
Default Excel 2002 : How to get the difference in a block of data ?

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








  #22   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,124
Default Excel 2002 : How to get the difference in a block of data ?

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






  #23   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 505
Default Excel 2002 : How to get the difference in a block of data ?

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






  #24   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 505
Default Excel 2002 : How to get the difference in a block of data ?

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






  #25   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,124
Default Excel 2002 : How to get the difference in a block of data ?

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










  #26   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,124
Default Excel 2002 : How to get the difference in a block of data ?

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








  #27   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 505
Default Excel 2002 : How to get the difference in a block of data ?

Hello Don,

After correcting the entry at cell A4, the formula now works fine :

Vehicle Meter Mileage
BBN2361 9,650 3150
BBN2361 10,600
BBN2361 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 6000
BGS9654 11,200
BGS9654 14,200
BGS9654 16,200
BSK1400 8,500 8300
BSK1400 10,250
BSK1400 11,500
BSK1400 12,500
BSK1400 14,200
BSK1400 16,800
PLM3265 9,800 8700
PLM3265 10,200
PLM3265 14,200
PLM3265 16,100
PLM3265 18,500

However I still have problem with the macro.

I recorded the following macro as PMO:

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

I selected range A1:C31 before running the macro. After clicking RUN,
processing began but error message "Run time error : (-2147417848(80010128)'
- method of select of object range failed" came out. I clicked at debug the
following codes came out:

ub PMO()
' PMO Macro
Sheets("Data").Select
Range("A35").Select
ActiveWindow.LargeScroll Down:=-1
Range("C2:C31").Select
Application.Run "PMO.xlsx!PMO"
Range("A2:C31").Select
End Sub

May I know what are my errors and how to fix this problem ?

Thanks

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,

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









  #28   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 10,124
Default Excel 2002 : How to get the difference in a block of data ?

The macro I presented does not need any selections. It is designed to run,
on the ACTIVE SHEET, as written for data starting at row 2 with the vehicle
in col A and the meter in col B. The mileage will be placed in col C.....
Why is this so hard???

To run from somewhere else, just select the data sheet first
Sub SumByCategory()

sheets("Data").select

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

OR use WITH to run from anywhere without selections. Pls notice the
placement of the dots .

Sub PMOSumByCategory()'untested

with sheets("Data")
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 with
End Sub



--
Don Guillett
SalesAid Software

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

After correcting the entry at cell A4, the formula now works fine :

Vehicle Meter Mileage
BBN2361 9,650 3150
BBN2361 10,600
BBN2361 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 6000
BGS9654 11,200
BGS9654 14,200
BGS9654 16,200
BSK1400 8,500 8300
BSK1400 10,250
BSK1400 11,500
BSK1400 12,500
BSK1400 14,200
BSK1400 16,800
PLM3265 9,800 8700
PLM3265 10,200
PLM3265 14,200
PLM3265 16,100
PLM3265 18,500

However I still have problem with the macro.

I recorded the following macro as PMO:

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

I selected range A1:C31 before running the macro. After clicking RUN,
processing began but error message "Run time error :
(-2147417848(80010128)'
- method of select of object range failed" came out. I clicked at debug
the
following codes came out:

ub PMO()
' PMO Macro
Sheets("Data").Select
Range("A35").Select
ActiveWindow.LargeScroll Down:=-1
Range("C2:C31").Select
Application.Run "PMO.xlsx!PMO"
Range("A2:C31").Select
End Sub

May I know what are my errors and how to fix this problem ?

Thanks

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,

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











  #29   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 505
Default Excel 2002 : How to get the difference in a block of data ?

Too difficult for me to understand as I do not have any basic in programming.

I still prefer a simple copy downwards formula to solve this kind of problem.

Thank you anyway for your contribution.

Kind Regards

Low





--
A36B58K641


"Don Guillett" wrote:

The macro I presented does not need any selections. It is designed to run,
on the ACTIVE SHEET, as written for data starting at row 2 with the vehicle
in col A and the meter in col B. The mileage will be placed in col C.....
Why is this so hard???

To run from somewhere else, just select the data sheet first
Sub SumByCategory()

sheets("Data").select

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

OR use WITH to run from anywhere without selections. Pls notice the
placement of the dots .

Sub PMOSumByCategory()'untested

with sheets("Data")
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 with
End Sub



--
Don Guillett
SalesAid Software

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

After correcting the entry at cell A4, the formula now works fine :

Vehicle Meter Mileage
BBN2361 9,650 3150
BBN2361 10,600
BBN2361 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 6000
BGS9654 11,200
BGS9654 14,200
BGS9654 16,200
BSK1400 8,500 8300
BSK1400 10,250
BSK1400 11,500
BSK1400 12,500
BSK1400 14,200
BSK1400 16,800
PLM3265 9,800 8700
PLM3265 10,200
PLM3265 14,200
PLM3265 16,100
PLM3265 18,500

However I still have problem with the macro.

I recorded the following macro as PMO:

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

I selected range A1:C31 before running the macro. After clicking RUN,
processing began but error message "Run time error :
(-2147417848(80010128)'
- method of select of object range failed" came out. I clicked at debug
the
following codes came out:

ub PMO()
' PMO Macro
Sheets("Data").Select
Range("A35").Select
ActiveWindow.LargeScroll Down:=-1
Range("C2:C31").Select
Application.Run "PMO.xlsx!PMO"
Range("A2:C31").Select
End Sub

May I know what are my errors and how to fix this problem ?

Thanks

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,

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



--

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Urgent! Prob with Excel 2002 Template Wizard with Data Tracking ad Duncan Excel Discussion (Misc queries) 0 May 23rd 06 04:24 AM
Excel 2002 - ReFormat DATA mateo107 Excel Discussion (Misc queries) 3 April 12th 06 04:54 PM
Want to Create a List in Excel 2002; Don't see List in Data Menu? Manoj Excel Discussion (Misc queries) 2 April 7th 06 07:34 PM
Excel 2002: Excel cannot paste the data Roger Whitehead Charts and Charting in Excel 1 April 7th 06 02:51 PM
Excel 2002 chart does not update when worksheet data changes proquant Charts and Charting in Excel 1 June 15th 05 12:23 AM


All times are GMT +1. The time now is 02:18 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"