Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
RC RC is offline
external usenet poster
 
Posts: 39
Default Restart row count each time the value in a specific column changes

Is there a formula that will restart the row count when the value in a
specific column changes --- such as shown below when the value in column A
changes the row count in column B restarts?

A B
1 452 1
2 452 2
3 452 3
4 452 4
5 329 1
6 329 2
7 548 1
8 548 2
9 548 3

Thanks in advance

--
RC
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Restart row count each time the value in a specific column changes

  1. Insert a new column next to column B (let's say it's column C).
  2. In cell C1, enter the formula: =IF(A1<A2,1,B1+1)
  3. Drag the formula down to the last row of your data.
  4. Column C will now display the row count that restarts each time the value in column A changes.

Explanation of the formula:
- IF(A1<A2,1,B1+1): This formula checks if the value in cell A1 is different from the value in cell A2. If it is, it means that the value in column A has changed, so it resets the row count to 1. If it's not different, it means that the value in column A is the same as the previous row, so it adds 1 to the row count in the previous row (cell B1).
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Restart row count each time the value in a specific column changes

Here's one ..
In B1, copied down: =COUNTIF(A$1:A1,A1)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,100 Files:360 Subscribers:56
xdemechanik
---
"RC" wrote:
Is there a formula that will restart the row count when the value in a
specific column changes --- such as shown below when the value in column A
changes the row count in column B restarts?

A B
1 452 1
2 452 2
3 452 3
4 452 4
5 329 1
6 329 2
7 548 1
8 548 2
9 548 3

Thanks in advance

--
RC

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
RC RC is offline
external usenet poster
 
Posts: 39
Default Restart row count each time the value in a specific column cha

Thanks Max, that works great!
--
RC


"Max" wrote:

Here's one ..
In B1, copied down: =COUNTIF(A$1:A1,A1)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,100 Files:360 Subscribers:56
xdemechanik
---
"RC" wrote:
Is there a formula that will restart the row count when the value in a
specific column changes --- such as shown below when the value in column A
changes the row count in column B restarts?

A B
1 452 1
2 452 2
3 452 3
4 452 4
5 329 1
6 329 2
7 548 1
8 548 2
9 548 3

Thanks in advance

--
RC

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Restart row count each time the value in a specific column cha

Welcome, thanks for feeding back
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:18,000 Files:359 Subscribers:56
xdemechanik
---
"RC" wrote in message
...
Thanks Max, that works great!
--
RC



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 a specific month in a column Elaine Excel Worksheet Functions 7 July 1st 08 09:30 AM
HOW TO RESTART COUNT FORMULA tgcali Excel Discussion (Misc queries) 3 April 21st 08 11:26 PM
HOW TO RESTART COUNT FORMULA akphidelt Excel Discussion (Misc queries) 0 April 21st 08 10:56 PM
SUM/COUNT column(s) based on specific value present within the column markx Excel Worksheet Functions 6 March 22nd 05 11:23 AM
How to count occcurence of specific text block in a column Anshuman Excel Discussion (Misc queries) 2 February 12th 05 01:55 PM


All times are GMT +1. The time now is 12:23 PM.

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

About Us

"It's about Microsoft Excel"