Automatically generate item or ID#'s

[dogleg43]

I use EXCEL to keep track of punchlist items or certain tasks.

How can I get EXCEL to automatically generate in an ID# or item number in a column "A" and keep it with the task in column "B"(without changing if items are added or deleted, etc.)

 

[WMO]

What format is the ID#? Is it a one up number?

When a punchlist item is complete is it and the task
deleted or struck through?
If it is deleted (or if you manually blank it out)
are you wanting to move everything up?

We can help if you are a little more specific in how you do it now and what you anticipate any automated routine to accomplish for you.

 

[ColourfulFigsnDiags]

Yeah, more info needed here ... how do you add/delete items? do you insert rows, cells what? Need a much better description of what you are trying to achieve and what you are doing.

Read the Eng-Tips Site Policies at FAQ731-376

 

[electricpete]

I created a macro which will insert 1 the first time you run it, 2 the 2nd time you run it, etc. Thus creating a unique serial number every time the macro is run.

Here's how I did it. Create a brand new sheet which I called "variables_sheet". In cell A1 of variables_sheet is the number 1. In cell A2 of the variables_sheet is the formula +A1+1.

Then go to sheet1 where the data will be stored including the serial number. Put your cursor in any empty cell and start macro recording. Go back to variables_sheet, select cell A1 (initially contains 1) and ctrl-c to copy. Now go back to sheet1 (the cell that you started in should already be highlighted) and press ctrl-v to copy the serial number (1) into the cell. Now once more go back to the variables sheet. Place cursor in cell A2 and ctrl-c top copy. Move to cell A1 and Edit/Paste-Special/Values. (This increases A1 by 1 and A2 remains A1+1). Now go back to the sheet1. For some reason at this point I had to press escape to convince excel I wasn't trying to paste something. Now end marco recording.

Now that the macro is recorded, with your cursor anywhere in sheet1, invoke the macro and it will put your serial number into that cell and increment the "counter" in variables_sheet.

Here is the vba code that excel created when I created this macro. Don't ask me what it means (I don't do vba or other psychedelic drugs)

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 9/27/2005 by Terry
'

'
Sheets("variables_sheet").Select
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste
ActiveSheet.Paste
Application.CutCopyMode = False
Sheets("variables_sheet").Select
Range("B1").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Sheet1").Select
Application.CutCopyMode = False
End Sub

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

My vba code was a little bit off. Should look like this:

Sub Macro5()
'
' Macro5 Macro
' Macro recorded 9/27/2005 by Terry
'

'
Sheets("variables_sheet").Select
Range("A1").Select
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste
Sheets("variables_sheet").Select
Range("B1").Select
Application.CutCopyMode = False
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Sheets("Sheet1").Select
Application.CutCopyMode = False
End Sub


=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[electricpete]

Whoops. The cell referred to as A2 in my word description is cell B1 in my macro.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[dogleg43]

Wow, thanks for all the responses so far. Below is some more info. Sorry if the problem seemed rather vague. I'll try some of the solutions already mentioned.
==============================================

*Items would not be inserted, only added beneath the bottom (last item.

*Items will not be deleted, only documented (in a separate column as completed or open.

*The ID# doesn't have to be anything complicated. Simply 1,2,3,4...etc. would be fine. The main thing is to NOT have any duplicate # or have them change with the punchlist item they are linked to (yeah, it's better suited to a database, but everyone seems to have EXCEL and it seems easiest.)

 

[ColourfulFigsnDiags]

Bit more info ... sorry. How are the items "linked" to the punch list item? I can only assume this is not a trivial fill down with incresing numbers problem, but you haven't explained exactly what the structure of your worksheet is. Are the punchlist items across different worksheets / workbooks, or are they all on the one worksheet? Does each row have a unique punchlist item, or are there multiple rows for each item ...

If it is a trivial matter of just having a unique number associated with each row, just enter 1 in cell a1, and =a1+1 in cell a2, then select cell a2:a60000, and press control-d ... (you can the copy and paste the ID numbers as valuse if you really don't want them to change) ... but I doubt what you want is that trivial.

Sorry, I'm just a bit confused as to what exactly you are trying to do. Electricpetes suggestion will generate a unique ID number for a particular cell in a particular workbook, but if you want to generate lots at once, or want to do so over multiple workbooks, you will have to modify his code to put the variables worksheet in your personal excel worksheet so it is available to all workbooks.

Read the Eng-Tips Site Policies at FAQ731-376

 

[zappedagain]

This is more of a semi-automatic solution but I'll present it as an idea to expand upon. What I do when I want to enumerate an existing list is the following five step process:

(start the list on row #2, headers on row #1 are okay)

1 Insert a new column (C1, or Column A) for the enumeration
2 Enter '1' at C1R2 and '2' at C1R3 (this sets up an informal '+1' pattern in C1 (Column A))
3 Select c1r2:c1r3 (a2:a3; so '1' and '2' are highlighted)
4 Click-and-drag on the small square in the lower right corner of the highlight box and drag it down; the '+1' pattern will repeat for as far down as you drag it. You now have fixed numbers for each entry that will not change if rows are inserted or deleted

At this point when I add rows I manually place the number for the new row (or use the above technique to continue numbering multiple rows).

5 To let me know what highest number used so far is, I put something like this in C1R1:

=CONCATENATE("Max is ",MAX(A2:A65535))

In this example that function will show '2' so that I know '3' is the next value. This comes in handy for situations where you end up inserting an entry in the middle of the existing rows.


Good luck.

 

[abeltio]

i do my minutes of meeting in excel for big projects.
each meeting is copied into a new worksheet.
items that are closed: line is autoformated to gray based on the contents of the column "status".
use AUTOFORMAT to color gray if status = closed
red if status = delayed
green if status = in progress
yellow if status = stand-by

to answer the original question about how to create item numbers...

this formula generates an id number for each new line added at the bottom

=row()-# of header rows

assuming the data start on row 5, because rows 1 thru 4 are header rows
= row()- 4

hth

saludos.
a.