Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002 : How to get the difference in a block of data ?
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Urgent! Prob with Excel 2002 Template Wizard with Data Tracking ad | Excel Discussion (Misc queries) | |||
Excel 2002 - ReFormat DATA | Excel Discussion (Misc queries) | |||
Want to Create a List in Excel 2002; Don't see List in Data Menu? | Excel Discussion (Misc queries) | |||
Excel 2002: Excel cannot paste the data | Charts and Charting in Excel | |||
Excel 2002 chart does not update when worksheet data changes | Charts and Charting in Excel |