automaticaly assigning numbers to rows

A

Adz

New Member
Joined
Jun 18, 2011
Messages
16
Hi all,
This is probably a very easy question for the guru's here.

What i am trying to do is allocate a consecutive number to each row in a cell at the end of each row. this in itself is easy. The problem I have is if i need to delete or insert a row I need the consecutive numbers to change accordingly.

to give an idea of the layout, the rows are competition entries in various categories. some entries pull out and there are constantly entries being added to categories. The idea is that at the cutoff date entries will all have a unique entry number and we will have a total count of entries ( the total count is easy its just the changing unique number)

I hope i explained myself properly and I thank anyone who can help in advance

Cheers
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
Hi and welcome.

Try something like this...
<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">Entries</td><td style="font-weight: bold;text-align: center;;">Unique Number</td><td style="font-weight: bold;text-align: center;;"></td><td style="font-weight: bold;text-align: center;;">Count of Entries</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">Entry1</td><td style="text-align: center;;">1001</td><td style="text-align: center;;"></td><td style="text-align: center;;">7</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">Entry2</td><td style="text-align: center;;">1002</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">Entry3</td><td style="text-align: center;;">1003</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">Entry4</td><td style="text-align: center;;">1004</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">Entry5</td><td style="text-align: center;;">1005</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">Entry6</td><td style="text-align: center;;">1006</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">Entry7</td><td style="text-align: center;;">1007</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr></tbody></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D2</th><td style="text-align:left">=COUNT(<font color="Blue">B:B</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=1000+ROW(<font color="Blue"></font>)-1</td></tr></tbody></table></td></tr></table>

ROW() returns the row number the formula is in. If you add or delete rows, the numbers will remain consecutive.
 
Upvote 0
Hi Adz and welcome to the Board
Following on AlphaFrogs cell references, you could also use a macro
Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Set Rng = Range(Cells(1, 2), Cells(Rows.Count, 2).End(xlUp)(1, 1))
        Rng.Formula = "=IF(NOT(ISBLANK(A1)),COUNTA(A$1:A1),"""")"
End Sub

Copy this into the sheet module of the sheet you are using
 
Last edited:
Upvote 0
I have a different interpretation based off of this:
The problem I have is if i need to delete or insert a row I need the consecutive numbers to change accordingly.
Click to expand...

And this:
( the total count is easy its just the changing unique number)
Click to expand...



So with this Sample data before macro:

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Entries</TD><TD style="TEXT-ALIGN: center">Run</TD><TD style="TEXT-ALIGN: center">Walk</TD><TD style="TEXT-ALIGN: center">Swim</TD><TD style="TEXT-ALIGN: center">Unique #</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Entry 1</TD><TD style="TEXT-ALIGN: center">X</TD><TD> </TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Entry 2</TD><TD> </TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">2</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Entry 3</TD><TD style="TEXT-ALIGN: center">X</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">3</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>Entry 4</TD><TD> </TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">4</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>Entry 5</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">5</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>Entry 6</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">6</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>Entry 7</TD><TD style="TEXT-ALIGN: center">X</TD><TD> </TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">7</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


And after when Entry 2 and 4 where deleted:
Sheet1 (2)

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Entries</TD><TD style="TEXT-ALIGN: center">Run</TD><TD style="TEXT-ALIGN: center">Walk</TD><TD style="TEXT-ALIGN: center">Swim</TD><TD style="TEXT-ALIGN: center">Unique #</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Entry 1</TD><TD style="TEXT-ALIGN: center">X</TD><TD> </TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">1</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Entry 3</TD><TD style="TEXT-ALIGN: center">X</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">2</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Entry 5</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">3</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>Entry 6</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">4</TD></TR><TR style="HEIGHT: 22px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>Entry 7</TD><TD style="TEXT-ALIGN: center">X</TD><TD> </TD><TD style="TEXT-ALIGN: center">X</TD><TD style="TEXT-ALIGN: center">5</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


The Macro to accomplish:
Code: 
Sub test()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
     Range("E2").Value = 1
     Range("E2").AutoFill Destination:=Range("E2:E" & LR), Type:=xlFillSeries
End Sub
 
Last edited:
Upvote 0
Hi Nalani
Mine allows for the Insert / delete scenario....but I think your code is a little more elegant inasmuch as it doesn't require a formula.
 
Upvote 0
Thanks for the compliment M M

But then again, this is all based on what we preceive as the OP's data set-up. Would be nice to have a sample of what He/She is working with. ;)

Just trowling tonight.
 
Upvote 0
Excel Workbook
ABCDEFGHIJ
1BOTTLE NUMBERCATWINERYBrand NameNAME OF WINEWINE VARIETYVINTAGEjudging scoremedalCOUNT
21.1Australian Vintage LTDMcGuiganSparklingPinot Gris201011
33511.1Ballast Stone EstateCurrency CreekReserve BruyChardonnay/ Pinot NoirNV22
43521.1Ballast Stone EstateStonemasonBrut CuveeChardonnayNV33
53581.1Berton VineyardsHead Over HeelsSparklingChardonnayNV44
61.1BimbadgenSparklingSemillonNV55
71.1BimbadgenSparkling RosShiraz/ Cabernet Sauvignon/ SemillonNV66
84651.1Blue Pyrenees EstateMidnight CuveeChardonnay200877
94811.1Blue Pyrenees EstateLunaChardonnay/ Pinot Noir/ Pinot MeunierNV88
entries proofread
Excel 2007


there is a piece of the worksheet. I have only just read replies so will try all now. thank you very much for your help
 
Upvote 0
So, if I understand you correctly you need a ranking(score) in column "H" if there is a value in say column "G" /
Code: 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Set Rng = Range(Cells(2, 8), Cells(Rows.Count, 8).End(xlUp)(2, 1))
        Rng.Formula = "=IF(NOT(ISBLANK(G2)),COUNTA(G$2:G2),"""")"
End Sub
 
Upvote 0
sort of michael. I need column J to increase if there is an entry in that row.
there are several categories and there is a blank row between each.


Excel Workbook
ABCDEFGHIJ
3975.1Queens PinchRosCabernet Sauvignon201019380
3985.1Robert SteinRosCabernet Sauvignon/ Merlot201120381
3991885.1Tenafeate CreekRosSangiovese201021382
400
4015495.2Crane WinesHillside RedLight Fruity Red BlendNV1383
4025.2De Bortoli WinesSacred HillSyrah DolcettoShiraz/ Dolcetto20102384
4035235.2McWilliams Wines7.3 ? ?InheritanceShiraz Merlot20103385
404
4055836.1Australian Vintage LTDNepentheThe Good DoctorPinot Noir20091386
4066056.1Australian Vintage LTDNepenthePinot Noir20092387
entries proofread
Excel 2007


The numbers that are there now are entered manualy, Very painful to fix up if you delete entry 4 as there are thousands of entries lol
 
Upvote 0
Also, not sure if it helps, the only column that every entry has a value in is CAT (Column B) and these ar all numeric values
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
Macro to correct entries across multiple sheets
Replies
1
Views
205
Lux Aeterna
L
H
  • Question Question
Need Help Consolidating/Condensing rows together if similar information
Replies
2
Views
206
Handienak
H
Y
  • Question Question
How to count number of consecutive wet days in the a rainfall table?
2
Replies
19
Views
807
felixstraube
F
K
  • Solved
Count number of max values per unique value and column heading?
Replies
5
Views
288
RobP
R
S
  • Solved
Delete rows before a certain time
2 3
Replies
26
Views
859
Joe4
Joe4

Forum statistics

Threads
1,224,594
Messages
6,179,795
Members
452,943
Latest member
Newbie4296

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top