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

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

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