-2

I have a file in excel. For each line, I have to create a unique identifier. It must be created permanently. The problem is for example: I have the same files on computers A and B. I'm filling same lines in each file. But the identifiers of those lines should be different. Please help me. If you know how to do that please give me an example of macro or formula

Community
  • 1
  • 1
cczarnyy_
  • 1
  • 1
  • 2
  • Why don't you simply add a counter in file A with an ID such as A001, then A002, etc... and do the same with the other file but instead of prefixing with A, prefix with B, so it would be B001, B002, etc... – Xabier Jan 31 '18 at 17:13
  • It has to be automated. This file will be filled out by many people who do not cooperate with each other. They do not know what will be entered by other people so they will not know whether to use prefix A or B etc – cczarnyy_ Jan 31 '18 at 17:17

2 Answers2

1

Try this.

=CONCATENATE(DEC2HEX(RANDBETWEEN(0,4294967295),8),"-",DEC2HEX(RANDBETWEEN(0,65535),4),"-",DEC2HEX(RANDBETWEEN(16384,20479),4),"-",DEC2HEX(RANDBETWEEN(32768,49151),4),"-",DEC2HEX(RANDBETWEEN(0,65535),4),DEC2HEX(RANDBETWEEN(0,4294967295),8))

This a copy of How can I generate GUIDs in Excel? with the semi colons replaced with commas. What you are looking for is a Global Unique Identifier or GUID. If you search for excel and GUID you can get a lot more options.

If you want to be able to get a reference of the row and/or columns out of this so you can compare the two files, you can append 

& "-" & ROW()&COLUMN()

to the end of the above. This will effectively give you and address of the row and column being referenced.

Chris
  • 265
  • 2
  • 16
0

Not the most elegant way of doing things, but I believe this somewhat answers your question.

If you can make sure that both files are Macro enabled, the user doesn't need to know how to prefix them, simply use something like below to add the ID to whatever column you want as new data is entered in column B in my example below:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet: Set ws = Sheets("Sheet1")
'declare and set your worksheet, amend as required
LastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
'get the last row with data on Column A
If Target.Address = "$B$" & LastRow Then
    For i = 2 To LastRow
        ws.Cells(i, 1).Value = "A" & Format(i, "000")
    Next i
End If
End Sub

Do the same in the other file, just change the "A" to "B"

Xabier
  • 7,587
  • 1
  • 8
  • 20