Auto Generate Unique ID

[Glasgowsmile]

Good day,

I'm trying to set up a sheet so it will generate a unique ID for that entry in column P when new information is added to the A column. I've tried a couple of formulas but can't get anything to work correctly so I'm thinking it needs to be VBA but I'm not sure where to begin. Any help with this would be great, the only thing I've found that I think I need is the following:

Dim maxNumber
    maxNumber = Application.WorksheetFunction.max(Range("P:P"))
    Target.Offset(0, -1) = maxNumber + 1

 

[mumps]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet.
Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing'
and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Glasgowsmile]

It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet.
Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing'
and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

The problem is that its sensitive data I don't feel comfortable sharing, so that's why I was quite vague. It would be too much effort to de-sensitize it to be worthwhile.

 

[mumps]

I wouldn't need all the data. A dozen or so rows would be enough.

 

[Glasgowsmile]

I wouldn't need all the data. A dozen or so rows would be enough.

Here is a very rough sample. When a new row is added to the table, I want a feedback ID generated in column E that's unique to that entry and doesn't change when the table is sorted.

sample.xlsx
	A	B	C	D	E	F	G	H	I
1	customer	name	email	feedback	feedback ID
2	a	john	n/a	misc	1
3	b	mary	n/a		2
4	c	sally	n/a		3
5	e	dave	n/a		4
6	f	brad	n/a		5
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Sheet1

 

[mumps]

The sample you posted doesn't show any data. It shows only cell E7.

 

[Glasgowsmile]

The sample you posted doesn't show any data. It shows only cell E7.

I updated it, my mistake. It should work now.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Start entering your data in column A.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Target.Offset(, 4) = Application.WorksheetFunction.Max(Range("E:E")) + 1
End Sub

 

[Glasgowsmile]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Start entering your data in column A.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Target.Offset(, 4) = Application.WorksheetFunction.Max(Range("E:E")) + 1
End Sub

Thanks, this works for manual entry but what if the data is copied from another location and then pasted in the A column? It doesn't update during that, sadly.

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    Target.Offset(, 4).Resize(, 1) = Application.WorksheetFunction.Max(Range("E:E")) + 1
End Sub