| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need to number but skip blank rows
Hi, I am trying to number rows in column A using a formula. The numbering
should start with 150, not 1. The problem is that I only want the rows numbered if the values in column B are unique. When I say unique, any duplicate entries would be right on top of each other as shown below. The list is already sorted. Here is an example of what I would like the numbering to look like: A B 150 Apples 151 Oranges Oranges 152 Pears Pears Pears 153 Lemons 154 Limes Ive played around with formulas using IF and COUNTIF, but so far I havent had much success. I know this is probably simple, but so far I havent been able to figure it out. Thanks in advance for any help. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need to number but skip blank rows
Put 150 in A1, and then put this formula in A2:
=IF(COUNTIF(B$1:B2,B2)=1,A$1+COUNT(A$1:A1),"") and copy it down as far as you need to. Hope this helps. Pete On Aug 19, 1:21*am, FJ wrote: Hi, I am trying to number rows in column A using a formula. *The numbering should start with 150, not 1. *The problem is that I only want the rows numbered if the values in column B are unique. *When I say unique, any duplicate entries would be right on top of each other as shown below. *The list is already sorted. *Here is an example of what I would like the numbering to look like: A * * * B 150 * * Apples 151 * * Oranges * * * * Oranges 152 * * Pears * * * * Pears * * * * Pears 153 * * Lemons 154 * * Limes Ive played around with formulas using IF and COUNTIF, but so far I havent had much success. *I know this is probably simple, but so far I havent been able to figure it out. Thanks in advance for any help. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need to number but skip blank rows
You have to put 150 manually in the first cell. Then in a2 put
IF(B3=B2,"",MAX($A$1:A1)+1) and copy it down. The only diffeence is this puts the number for multiple items in the last position not first but I'm sure you could modify the idea if this is critical. This is what you will get A B 150 Apples Oranges 151 Oranges Pears Pears 152 Pears 153 Lemons 154 Limes "FJ" wrote: Hi, I am trying to number rows in column A using a formula. The numbering should start with 150, not 1. The problem is that I only want the rows numbered if the values in column B are unique. When I say unique, any duplicate entries would be right on top of each other as shown below. The list is already sorted. Here is an example of what I would like the numbering to look like: A B 150 Apples 151 Oranges Oranges 152 Pears Pears Pears 153 Lemons 154 Limes Ive played around with formulas using IF and COUNTIF, but so far I havent had much success. I know this is probably simple, but so far I havent been able to figure it out. Thanks in advance for any help. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need to number but skip blank rows
Why not compare B2 with B1, and then you could give the OP what s/he
asked for: =IF(B2=B1,"",MAX($A$1:A1)+1) Hope this helps. Pete On Aug 19, 1:45*am, NDBC wrote: You have to put 150 manually in the first cell. Then in a2 put IF(B3=B2,"",MAX($A$1:A1)+1) and copy it down. The only diffeence is this puts the number for multiple items in the last position not first but I'm sure you could modify the idea if this is critical. This is what you will get A * * * B *150 * *Apples * * * * Oranges *151 * *Oranges * * * * Pears * * * * Pears *152 * *Pears *153 * *Lemons *154 * *Limes "FJ" wrote: Hi, I am trying to number rows in column A using a formula. *The numbering should start with 150, not 1. *The problem is that I only want the rows numbered if the values in column B are unique. *When I say unique, any duplicate entries would be right on top of each other as shown below. *The list is already sorted. *Here is an example of what I would like the numbering to look like: A *B 150 * * * *Apples 151 * * * *Oranges * *Oranges 152 * * * *Pears * *Pears * *Pears 153 * * * *Lemons 154 * * * *Limes Ive played around with formulas using IF and COUNTIF, but so far I havent had much success. *I know this is probably simple, but so far I havent been able to figure it out. Thanks in advance for any help.- Hide quoted text - - Show quoted text - |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need to number but skip blank rows
Hi, Pete, thank you for your response. This was just what I needed. :)
"Pete_UK" wrote: Put 150 in A1, and then put this formula in A2: =IF(COUNTIF(B$1:B2,B2)=1,A$1+COUNT(A$1:A1),"") and copy it down as far as you need to. Hope this helps. Pete On Aug 19, 1:21 am, FJ wrote: Hi, I am trying to number rows in column A using a formula. The numbering should start with 150, not 1. The problem is that I only want the rows numbered if the values in column B are unique. When I say unique, any duplicate entries would be right on top of each other as shown below. The list is already sorted. Here is an example of what I would like the numbering to look like: A B 150 Apples 151 Oranges Oranges 152 Pears Pears Pears 153 Lemons 154 Limes Ive played around with formulas using IF and COUNTIF, but so far I havent had much success. I know this is probably simple, but so far I havent been able to figure it out. Thanks in advance for any help. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need to number but skip blank rows
Hi, thanks for your response. I ended up using Pete's formula but yours will
definitely come in handy. Thank you. "NDBC" wrote: You have to put 150 manually in the first cell. Then in a2 put IF(B3=B2,"",MAX($A$1:A1)+1) and copy it down. The only diffeence is this puts the number for multiple items in the last position not first but I'm sure you could modify the idea if this is critical. This is what you will get A B 150 Apples Oranges 151 Oranges Pears Pears 152 Pears 153 Lemons 154 Limes "FJ" wrote: Hi, I am trying to number rows in column A using a formula. The numbering should start with 150, not 1. The problem is that I only want the rows numbered if the values in column B are unique. When I say unique, any duplicate entries would be right on top of each other as shown below. The list is already sorted. Here is an example of what I would like the numbering to look like: A B 150 Apples 151 Oranges Oranges 152 Pears Pears Pears 153 Lemons 154 Limes Ive played around with formulas using IF and COUNTIF, but so far I havent had much success. I know this is probably simple, but so far I havent been able to figure it out. Thanks in advance for any help. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need to number but skip blank rows
Hi, thanks for this formula, too. It's interesting, it handles blank cells
in column B differently than your IF/COUNTIF formula, which is the one I ended up using for my purposes this time. Thanks again! "Pete_UK" wrote: Why not compare B2 with B1, and then you could give the OP what s/he asked for: =IF(B2=B1,"",MAX($A$1:A1)+1) Hope this helps. Pete On Aug 19, 1:45 am, NDBC wrote: You have to put 150 manually in the first cell. Then in a2 put IF(B3=B2,"",MAX($A$1:A1)+1) and copy it down. The only diffeence is this puts the number for multiple items in the last position not first but I'm sure you could modify the idea if this is critical. This is what you will get A B 150 Apples Oranges 151 Oranges Pears Pears 152 Pears 153 Lemons 154 Limes "FJ" wrote: Hi, I am trying to number rows in column A using a formula. The numbering should start with 150, not 1. The problem is that I only want the rows numbered if the values in column B are unique. When I say unique, any duplicate entries would be right on top of each other as shown below. The list is already sorted. Here is an example of what I would like the numbering to look like: A B 150 Apples 151 Oranges Oranges 152 Pears Pears Pears 153 Lemons 154 Limes Ive played around with formulas using IF and COUNTIF, but so far I havent had much success. I know this is probably simple, but so far I havent been able to figure it out. Thanks in advance for any help.- Hide quoted text - - Show quoted text - |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Need to number but skip blank rows
You're welcome - thanks for feeding back.
Pete On Aug 19, 4:43*am, FJ wrote: Hi, Pete, thank you for your response. *This was just what I needed. :) "Pete_UK" wrote: Put 150 in A1, and then put this formula in A2: =IF(COUNTIF(B$1:B2,B2)=1,A$1+COUNT(A$1:A1),"") and copy it down as far as you need to. Hope this helps. Pete On Aug 19, 1:21 am, FJ wrote: Hi, I am trying to number rows in column A using a formula. *The numbering should start with 150, not 1. *The problem is that I only want the rows numbered if the values in column B are unique. *When I say unique, any duplicate entries would be right on top of each other as shown below. *The list is already sorted. *Here is an example of what I would like the numbering to look like: A * * * B 150 * * Apples 151 * * Oranges * * * * Oranges 152 * * Pears * * * * Pears * * * * Pears 153 * * Lemons 154 * * Limes Ive played around with formulas using IF and COUNTIF, but so far I havent had much success. *I know this is probably simple, but so far I havent been able to figure it out. Thanks in advance for any help.- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| copy from one sheet to another and skip the blank rows | Excel Discussion (Misc queries) | |||
| Fill Cells with same number in three rows then skip to next number | Excel Worksheet Functions | |||
| Get number of rows that data uses, including blank rows | Excel Discussion (Misc queries) | |||
| Get number of rows that data takes up, including blank rows | Excel Worksheet Functions | |||
| skip blank rows when pasting formulas | Excel Worksheet Functions | |||
Linear Mode
