AM I IN A DREAM WORLD? 1

[Harley78]

Before I begin, just wanted to check to see if I can do a macro that will take a row below and subtract the row above it for each line... However, the glitch is that there are different companies and some contain more rows than others so this would need to start over at that row with company name change.....

Just wanted to get your feedback as if I am in a dreamworld or if this can actually be done?

Thanks a bunch

Col A Col B Col C Col D
# OF DAYS
company xyz AUFER 12/21/2005 0
company xyz SD 12/21/2005 b3-b2
company xyz INFO 12/21/2005 b4-b3
company xyz KA 12/22/2005 b5-b4
company xyz KA 12/22/2005 b6-b5
company xyz KA 1/4/2006 b7-b6
company xyz KA 1/10/2006 b8-b7
company xyz KAAL 12/22/2005 b9-b8
company xyz KAAL 1/10/2006 b10-b9
company xyz SB 12/22/2005 b11-b10
company xyz SB 1/10/2006
company xyz AL 12/22/2005
company mno AUFER 12/21/2005 0
company mno SD 12/21/2005 b15-b14
company mno INFO 12/21/2005 b16-b15
company mno KA 12/22/2005 b17-b16
company mno KA 12/22/2005 b18-b17
company mno KA 1/4/2006
company mno KA 1/10/2006
company mno KAAL 12/22/2005
company mno KAAL 1/10/2006
company mno SB 12/22/2005
company mno SB 1/10/2006
company mno AL 12/22/2005
company mno AL 1/10/2006
company mno FREI 12/22/2005
company mno FREI 1/10/2006
company mno INFO1 12/22/2005
company mno INFO1 1/10/2006
company mno FBPL 12/22/2005
company mno ALOL 1/25/2006
company mno LT 1/25/2006
company mno INFO3 1/25/2006
company mno AB 1/25/2006
company mno ABFAX 1/25/2006
company mno FERT 2/1/2006
company mno FERTI 2/1/2006
company mno INFPR 5/24/2007
company xyz AUFER 12/21/2005 0
company xyz SD 12/21/2005 b3-b2
company xyz INFO 12/21/2005 b4-b3
company xyz KA 12/22/2005 b5-b4
company xyz KA 12/22/2005 b6-b5
company xyz KA 1/4/2006 b7-b6

 

[IRstuff]

Wouldn't it simply be an IF statement that determines whether you do subtract or not?

IF(A3=A2,B3-B2,"")?

TTFN

FAQ731-376

 

[swertel]

If you use the lookup function along with IF statements, it can probably be done. It will take me a while just to input your data into a spreadsheet to find out, so I may not get around to verifying the exact how to.

--Scott

http://wertel.eng.pro

 

[MintJulep]

=if(A1=A2, B2-B1, 0)

 

[Harley78]

Thats what I was afraid of....... I started to do that manually and thought,, OMG, 4700 rows and growing.....oh well, if it is what it is, then so be it... Thanks guys for your input!

Harley

 

[yogia]

Thats what I was afraid of....... I started to do that manually and thought,, OMG, 4700 rows and growing.....oh well, if it is what it is, then so be it... Thanks guys for your input!

Hi Harley:

I couldn't quite follow, so where are you at -- is it going to be a formula based approach, or are you going to use a macro?

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
ANAND Enterprises LLC

http://www.energyefficientbuild.com

 

[pmover]

just a thought . . . try experimenting with the auto filter, subtotals, and pivot table capabilities of excel. i believe that you find a feature to your satisfaction. all are within the Data menu!

good luck!
-pmover

 

[jhardy1]

"Thats what I was afraid of....... I started to do that manually and thought,, OMG, 4700 rows and growing.....oh well, if it is what it is, then so be it... "

I don't quite see the problem - enter the expression in the top row, then copy it down to the bottom of the table - wouldn't that fix it in one step?