Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
bill gras
 
Posts: n/a
Default count distance in column

I have in cells B1 to B90 the word " distance" in different rows at random ,
other rows have numbers in them. What I need is the word "distance" to have a
number before it , in column A . Every time the word "distance" comes up it
needs to be added to the last "distance" word, like : distance=1,
distance+distance=2, distance+distance+distance=3 and so on.
eg:- A1 1 B1 distance
A2 (blank) B2 22.7
A3 (blank) B3 33.8
A4 2 B4 distance
-- A5 (blank) B5 23
A6 (blank) B6 5.8
A7 (blank) B7 6
A8 (blank) B8 17
A9 3 B9 distance
A10 (blank) B10 34.9
Down to 300 rows

Hope some can help
thanks
bill gras
  #2   Report Post  
Anne Troy
 
Posts: n/a
Default

Hi, Bill. Instead of using VBA or something here, why don't you do the
following? First, fill int he blank cells using these instructions:

http://www.officearticles.com/excel/...soft_excel.htm

Then, use the Subtotals featu

http://www.officearticles.com/excel/...soft_excel.htm

By doing it this way, you can get subtotals (and clear them again) any time
you want. The only differance is that instead of Distance, it'll say
"Total".
*******************
~Anne Troy

www.OfficeArticles.com


"bill gras" wrote in message
...
I have in cells B1 to B90 the word " distance" in different rows at random

,
other rows have numbers in them. What I need is the word "distance" to

have a
number before it , in column A . Every time the word "distance" comes up

it
needs to be added to the last "distance" word, like : distance=1,
distance+distance=2, distance+distance+distance=3 and so on.
eg:- A1 1 B1 distance
A2 (blank) B2 22.7
A3 (blank) B3 33.8
A4 2 B4 distance
-- A5 (blank) B5 23
A6 (blank) B6 5.8
A7 (blank) B7 6
A8 (blank) B8 17
A9 3 B9 distance
A10 (blank) B10 34.9
Down to 300 rows

Hope some can help
thanks
bill gras



  #3   Report Post  
bill gras
 
Posts: n/a
Default

Hi Anne Troy

Thank you for your reply
I can't change the word "distance" because that is the result of a worksheet
formula
I would be much happier with a worksheet function if there is one

regards
bill gras


"Anne Troy" wrote:

Hi, Bill. Instead of using VBA or something here, why don't you do the
following? First, fill int he blank cells using these instructions:

http://www.officearticles.com/excel/...soft_excel.htm

Then, use the Subtotals featu

http://www.officearticles.com/excel/...soft_excel.htm

By doing it this way, you can get subtotals (and clear them again) any time
you want. The only differance is that instead of Distance, it'll say
"Total".
*******************
~Anne Troy

www.OfficeArticles.com


"bill gras" wrote in message
...
I have in cells B1 to B90 the word " distance" in different rows at random

,
other rows have numbers in them. What I need is the word "distance" to

have a
number before it , in column A . Every time the word "distance" comes up

it
needs to be added to the last "distance" word, like : distance=1,
distance+distance=2, distance+distance+distance=3 and so on.
eg:- A1 1 B1 distance
A2 (blank) B2 22.7
A3 (blank) B3 33.8
A4 2 B4 distance
-- A5 (blank) B5 23
A6 (blank) B6 5.8
A7 (blank) B7 6
A8 (blank) B8 17
A9 3 B9 distance
A10 (blank) B10 34.9
Down to 300 rows

Hope some can help
thanks
bill gras




  #4   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

Type the number 1 in cell A1.
In cell A2, enter the formula:
=IF(B2="distance",MAX($A$1:A1)+1,"")

Copy the formula down to row 300

bill gras wrote:
Hi Anne Troy

Thank you for your reply
I can't change the word "distance" because that is the result of a worksheet
formula
I would be much happier with a worksheet function if there is one

regards
bill gras


"Anne Troy" wrote:


Hi, Bill. Instead of using VBA or something here, why don't you do the
following? First, fill int he blank cells using these instructions:

http://www.officearticles.com/excel/...soft_excel.htm

Then, use the Subtotals featu

http://www.officearticles.com/excel/...soft_excel.htm

By doing it this way, you can get subtotals (and clear them again) any time
you want. The only differance is that instead of Distance, it'll say
"Total".
*******************
~Anne Troy

www.OfficeArticles.com


"bill gras" wrote in message
...

I have in cells B1 to B90 the word " distance" in different rows at random


,

other rows have numbers in them. What I need is the word "distance" to


have a

number before it , in column A . Every time the word "distance" comes up


it

needs to be added to the last "distance" word, like : distance=1,
distance+distance=2, distance+distance+distance=3 and so on.
eg:- A1 1 B1 distance
A2 (blank) B2 22.7
A3 (blank) B3 33.8
A4 2 B4 distance
-- A5 (blank) B5 23
A6 (blank) B6 5.8
A7 (blank) B7 6
A8 (blank) B8 17
A9 3 B9 distance
A10 (blank) B10 34.9
Down to 300 rows

Hope some can help
thanks
bill gras






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

  #5   Report Post  
bill gras
 
Posts: n/a
Default

Thank You Debra you are a gem
Bill Gras
--
bill gras


"Debra Dalgleish" wrote:

Type the number 1 in cell A1.
In cell A2, enter the formula:
=IF(B2="distance",MAX($A$1:A1)+1,"")

Copy the formula down to row 300

bill gras wrote:
Hi Anne Troy

Thank you for your reply
I can't change the word "distance" because that is the result of a worksheet
formula
I would be much happier with a worksheet function if there is one

regards
bill gras


"Anne Troy" wrote:


Hi, Bill. Instead of using VBA or something here, why don't you do the
following? First, fill int he blank cells using these instructions:

http://www.officearticles.com/excel/...soft_excel.htm

Then, use the Subtotals featu

http://www.officearticles.com/excel/...soft_excel.htm

By doing it this way, you can get subtotals (and clear them again) any time
you want. The only differance is that instead of Distance, it'll say
"Total".
*******************
~Anne Troy

www.OfficeArticles.com


"bill gras" wrote in message
...

I have in cells B1 to B90 the word " distance" in different rows at random

,

other rows have numbers in them. What I need is the word "distance" to

have a

number before it , in column A . Every time the word "distance" comes up

it

needs to be added to the last "distance" word, like : distance=1,
distance+distance=2, distance+distance+distance=3 and so on.
eg:- A1 1 B1 distance
A2 (blank) B2 22.7
A3 (blank) B3 33.8
A4 2 B4 distance
-- A5 (blank) B5 23
A6 (blank) B6 5.8
A7 (blank) B7 6
A8 (blank) B8 17
A9 3 B9 distance
A10 (blank) B10 34.9
Down to 300 rows

Hope some can help
thanks
bill gras





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html


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
Count how many "name" in a column pigbelly Excel Worksheet Functions 4 July 12th 05 02:53 PM
COUNT NON-BLANK CELLS WITH REFERENCE TO ANOTHER COLUMN carricka Excel Worksheet Functions 1 May 6th 05 04:50 PM
Count number of occurences in 1 column only if something in anothe Wenster Excel Worksheet Functions 2 February 7th 05 10:58 PM
COUNT INFORMATION IN COLUMN B ONLY IF A1 MATCHES K1 COUNT THAT CE. witchcat98 Excel Worksheet Functions 1 February 4th 05 02:38 PM
SUMPRODUCT Formula to Count Row of data Below Matched Criteria Sam via OfficeKB.com Excel Worksheet Functions 8 February 3rd 05 02:37 AM


All times are GMT +1. The time now is 12:29 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"