Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numbering rows and skipping blanks
I'm sure this is easy...
I have a long list of names and I want to number them in the column to the left. Some of the names are grouped and between these groups there are blank rows. How can I number each non-blank row, increasing by 1? Thank you! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numbering rows and skipping blanks
Say the names are in column B and then in A1 enter 1 and in A2 enter:
=IF(B2<"",MAX($A$1:A1)+1,"") and copy down. For example: 1 apples 2 peaches 3 pumplin 4 pie 5 dogs 6 cats 7 fish 8 flowers 9 trees 10 grass -- Gary''s Student - gsnu200901 "Exceleighton" wrote: I'm sure this is easy... I have a long list of names and I want to number them in the column to the left. Some of the names are grouped and between these groups there are blank rows. How can I number each non-blank row, increasing by 1? Thank you! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numbering rows and skipping blanks
For some reason it's not working. I entered the formula you mentioned in the
cell adjacent to '25 DODICI' and dragged it down from there. I get this: 1 1809 25 DODICI 1 3 MONTS FLANDERS GOLDEN 1 46ER PALE ALE 2 A LE COQ IMPERIAL EXTRA DOUBLE STOUT 2 ABBAYE DE SAINT BON CHIEN 2 ABITA ABBEY ALE ABITA AMBER 2 ABITA ANDYGATOR HELLES ABITA BOCK 3 ABITA CHRISTMAS ABITA FALLFEST 3 ABITA GOLDEN 3 ABITA JOCKAMO IPA 3 ABITA LIGHT 3 ABITA PECAN HARVEST 3 ABITA PURPLE HAZE 4 ABITA RED 4 ABITA RESTORATION 4 ABITA SATSUMA HARVEST WIT 4 ABITA TURBODOG 4 ABITA WHEAT 4 4 AECHT SCHLENKERLA RAUCHBIER 5 AECHT SCHLEN. RAUCHBIER WEIZ 5 Any ideas? "Gary''s Student" wrote: Say the names are in column B and then in A1 enter 1 and in A2 enter: =IF(B2<"",MAX($A$1:A1)+1,"") and copy down. For example: 1 apples 2 peaches 3 pumplin 4 pie 5 dogs 6 cats 7 fish 8 flowers 9 trees 10 grass -- Gary''s Student - gsnu200901 "Exceleighton" wrote: I'm sure this is easy... I have a long list of names and I want to number them in the column to the left. Some of the names are grouped and between these groups there are blank rows. How can I number each non-blank row, increasing by 1? Thank you! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numbering rows and skipping blanks
NOT next to 25DODICI
put the formula immediately below the 1 -- Gary''s Student - gsnu200901 "Exceleighton" wrote: For some reason it's not working. I entered the formula you mentioned in the cell adjacent to '25 DODICI' and dragged it down from there. I get this: 1 1809 25 DODICI 1 3 MONTS FLANDERS GOLDEN 1 46ER PALE ALE 2 A LE COQ IMPERIAL EXTRA DOUBLE STOUT 2 ABBAYE DE SAINT BON CHIEN 2 ABITA ABBEY ALE ABITA AMBER 2 ABITA ANDYGATOR HELLES ABITA BOCK 3 ABITA CHRISTMAS ABITA FALLFEST 3 ABITA GOLDEN 3 ABITA JOCKAMO IPA 3 ABITA LIGHT 3 ABITA PECAN HARVEST 3 ABITA PURPLE HAZE 4 ABITA RED 4 ABITA RESTORATION 4 ABITA SATSUMA HARVEST WIT 4 ABITA TURBODOG 4 ABITA WHEAT 4 4 AECHT SCHLENKERLA RAUCHBIER 5 AECHT SCHLEN. RAUCHBIER WEIZ 5 Any ideas? "Gary''s Student" wrote: Say the names are in column B and then in A1 enter 1 and in A2 enter: =IF(B2<"",MAX($A$1:A1)+1,"") and copy down. For example: 1 apples 2 peaches 3 pumplin 4 pie 5 dogs 6 cats 7 fish 8 flowers 9 trees 10 grass -- Gary''s Student - gsnu200901 "Exceleighton" wrote: I'm sure this is easy... I have a long list of names and I want to number them in the column to the left. Some of the names are grouped and between these groups there are blank rows. How can I number each non-blank row, increasing by 1? Thank you! |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Numbering rows and skipping blanks
The formula is correct.
You may also try this: =IF(len(B2)<0,MAX($A$1:A1)+1,"") Unless the cell contains an invisible text (" " or "space"), both formula may not work. Exceleighton wrote: For some reason it's not working. I entered the formula you mentioned in the cell adjacent to '25 DODICI' and dragged it down from there. I get this: 1 1809 25 DODICI 1 3 MONTS FLANDERS GOLDEN 1 46ER PALE ALE 2 A LE COQ IMPERIAL EXTRA DOUBLE STOUT 2 ABBAYE DE SAINT BON CHIEN 2 ABITA ABBEY ALE ABITA AMBER 2 ABITA ANDYGATOR HELLES ABITA BOCK 3 ABITA CHRISTMAS ABITA FALLFEST 3 ABITA GOLDEN 3 ABITA JOCKAMO IPA 3 ABITA LIGHT 3 ABITA PECAN HARVEST 3 ABITA PURPLE HAZE 4 ABITA RED 4 ABITA RESTORATION 4 ABITA SATSUMA HARVEST WIT 4 ABITA TURBODOG 4 ABITA WHEAT 4 4 AECHT SCHLENKERLA RAUCHBIER 5 AECHT SCHLEN. RAUCHBIER WEIZ 5 Any ideas? Say the names are in column B and then in A1 enter 1 and in A2 enter: =IF(B2<"",MAX($A$1:A1)+1,"") and copy down. For example: [quoted text clipped - 19 lines] Thank you! -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200908/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Skipping Blanks (Again) | Excel Discussion (Misc queries) | |||
Skipping Blanks | Excel Discussion (Misc queries) | |||
copy a list and skipping blanks | Excel Worksheet Functions | |||
Skipping blanks | Excel Discussion (Misc queries) | |||
Paste Special Skip Blanks not skipping blanks, but overwriting... | Excel Discussion (Misc queries) |