Generate ID based on string in excelHow to create Excel (.XLS and .XLSX) file in C# without installing Ms Office?How to concatenate text from multiple rows into a single text string in SQL server?How to return multiple values from a function?Converting string into datetimeHow to substring a string in Python?Python progression path - From apprentice to guruDoes Python have a string 'contains' substring method?Peak detection in a 2D array“Large data” work flows using pandasWhy is “1000000000000000 in range(1000000000000001)” so fast in Python 3?
Simple function that simulates survey results based on sample size and probability
Why does Mjolnir fall down in Age of Ultron but not in Endgame?
Did people go back to where they were?
Why does the 6502 have the BIT instruction?
What is the object moving across the ceiling in this stock footage?
How to use Palladio font in text body but Computer Modern for Equations?
Plot twist where the antagonist wins
How to Pin Point Large File eating space in Fedora 18
Text at the right of icon
Why do Windows registry hives appear empty?
Is the field of q-series 'dead'?
Is neural networks training done one-by-one?
Looking for a soft substance that doesn't dissolve underwater
Should breaking down something like a door be adjudicated as an attempt to beat its AC and HP, or as an ability check against a set DC?
How should I introduce map drawing to my players?
the meaning of 'carry' in a novel
What kind of metaphor is "trees in the wind"?
Were pens caps holes designed to prevent death by suffocation if swallowed?
Why did David Cameron offer a referendum on the European Union?
Is it true that cut time means "play twice as fast as written"?
Employer demanding to see degree after poor code review
How do Human Traits Work?
Ticket to ride, 1910: What are the big cities
Make 24 using exactly three 3s
Generate ID based on string in excel
How to create Excel (.XLS and .XLSX) file in C# without installing Ms Office?How to concatenate text from multiple rows into a single text string in SQL server?How to return multiple values from a function?Converting string into datetimeHow to substring a string in Python?Python progression path - From apprentice to guruDoes Python have a string 'contains' substring method?Peak detection in a 2D array“Large data” work flows using pandasWhy is “1000000000000000 in range(1000000000000001)” so fast in Python 3?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
So, I have data consists of names of the persons and I want to assign a unique numeric ID to each of them based on their first names. But the thing is I want to give same unique numeric ID to same first name person. For example, if say, there are two person with same name e.g. John, they will have same unique numeric ID value. Note that I want to assign this ID dynamically because the people data will get added constantly so every time the new people data added I need to check whether the I already have a ID for that person or do I have to generate a new one. I want do this excel with some formula or macros.
Also if anyone knows how to do this python like generating an same unique numeric ID for same string. I also try to find answer using UUID module of python, but didn't find any proper solution.
ID Name
1 John
2 Michelle
1 John
3 Hasan
2 Michelle
As you can see I John value has same numeric ID which is '1' so as 'Michelle'
python excel csv
edited Mar 24 at 6:57
Tim Williams
91.3k97087
asked Mar 24 at 5:39
SmitSherlockSmitSherlock
2629
add a comment |
So, I have data consists of names of the persons and I want to assign a unique numeric ID to each of them based on their first names. But the thing is I want to give same unique numeric ID to same first name person. For example, if say, there are two person with same name e.g. John, they will have same unique numeric ID value. Note that I want to assign this ID dynamically because the people data will get added constantly so every time the new people data added I need to check whether the I already have a ID for that person or do I have to generate a new one. I want do this excel with some formula or macros.
Also if anyone knows how to do this python like generating an same unique numeric ID for same string. I also try to find answer using UUID module of python, but didn't find any proper solution.
ID Name
1 John
2 Michelle
1 John
3 Hasan
2 Michelle
As you can see I John value has same numeric ID which is '1' so as 'Michelle'
python excel csv
edited Mar 24 at 6:57
Tim Williams
91.3k97087
asked Mar 24 at 5:39
SmitSherlockSmitSherlock
2629
How aboutJohnas the ID?
– Klaus D.
Mar 24 at 5:58
I want to have numeric unique ID
– SmitSherlock
Mar 24 at 6:34
It might be a good idea to include that in your question.
– Klaus D.
Mar 24 at 6:40
add a comment |
So, I have data consists of names of the persons and I want to assign a unique numeric ID to each of them based on their first names. But the thing is I want to give same unique numeric ID to same first name person. For example, if say, there are two person with same name e.g. John, they will have same unique numeric ID value. Note that I want to assign this ID dynamically because the people data will get added constantly so every time the new people data added I need to check whether the I already have a ID for that person or do I have to generate a new one. I want do this excel with some formula or macros.
Also if anyone knows how to do this python like generating an same unique numeric ID for same string. I also try to find answer using UUID module of python, but didn't find any proper solution.
ID Name
1 John
2 Michelle
1 John
3 Hasan
2 Michelle
As you can see I John value has same numeric ID which is '1' so as 'Michelle'
python excel csv
edited Mar 24 at 6:57
Tim Williams
91.3k97087
asked Mar 24 at 5:39
SmitSherlockSmitSherlock
2629
So, I have data consists of names of the persons and I want to assign a unique numeric ID to each of them based on their first names. But the thing is I want to give same unique numeric ID to same first name person. For example, if say, there are two person with same name e.g. John, they will have same unique numeric ID value. Note that I want to assign this ID dynamically because the people data will get added constantly so every time the new people data added I need to check whether the I already have a ID for that person or do I have to generate a new one. I want do this excel with some formula or macros.
Also if anyone knows how to do this python like generating an same unique numeric ID for same string. I also try to find answer using UUID module of python, but didn't find any proper solution.
ID Name
1 John
2 Michelle
1 John
3 Hasan
2 Michelle
As you can see I John value has same numeric ID which is '1' so as 'Michelle'
python excel csv
python excel csv
edited Mar 24 at 6:57
Tim Williams
91.3k97087
asked Mar 24 at 5:39
SmitSherlockSmitSherlock
2629
edited Mar 24 at 6:57
Tim Williams
91.3k97087
asked Mar 24 at 5:39
SmitSherlockSmitSherlock
2629
edited Mar 24 at 6:57
Tim Williams
91.3k97087
edited Mar 24 at 6:57
Tim Williams
91.3k97087
edited Mar 24 at 6:57
Tim Williams
91.3k97087
91.3k97087
asked Mar 24 at 5:39
SmitSherlockSmitSherlock
2629
asked Mar 24 at 5:39
SmitSherlockSmitSherlock
2629
asked Mar 24 at 5:39
SmitSherlockSmitSherlock
2629
2629
How aboutJohnas the ID?
– Klaus D.
Mar 24 at 5:58
I want to have numeric unique ID
– SmitSherlock
Mar 24 at 6:34
It might be a good idea to include that in your question.
– Klaus D.
Mar 24 at 6:40
add a comment |
How aboutJohnas the ID?
– Klaus D.
Mar 24 at 5:58
I want to have numeric unique ID
– SmitSherlock
Mar 24 at 6:34
It might be a good idea to include that in your question.
– Klaus D.
Mar 24 at 6:40
How about
John as the ID?– Klaus D.
Mar 24 at 5:58
How about
John as the ID?– Klaus D.
Mar 24 at 5:58
I want to have numeric unique ID
– SmitSherlock
Mar 24 at 6:34
I want to have numeric unique ID
– SmitSherlock
Mar 24 at 6:34
It might be a good idea to include that in your question.
– Klaus D.
Mar 24 at 6:40
It might be a good idea to include that in your question.
– Klaus D.
Mar 24 at 6:40
add a comment |
2 Answers
2
active
oldest
votes
Although macro's/UDF's would be a handy way, here is a rather simple formula:
The formula I used in A2 translates to:
=IF(COUNTIF($B$2:B2,B2)>1,INDEX($A$2:A2,MATCH(B2,$B$2:B2,0)),MAX($A$1:A1)+1)
answered Mar 24 at 9:34
JvdVJvdV
4,5932517
add a comment |
This UDF is a bit shaky but will work depending on how many names you have and the spread of the names ...
Public Function GenerateId(ByVal strText As String) As Long
Dim i As Long
For i = 1 To Len(strText)
strChar = UCase(Mid(strText, i, 1))
GenerateId = GenerateId + Asc(strChar)
Next
End Function
... there is a chance it will double up but it's not easy to predict. You'd have to run all names through and check all outcomes.
Also, I know it's not a sequential ID approach starting from 1 but you didn't specify that so I used some creative licence. :-)
Also, this will ensure that the name retains it's ID if the data is sorted differently, not sure if that's a requirement or not but it's a consideration.
Worth a potential shot anyway.
answered Mar 24 at 7:00
SkinSkin
2,0132515
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55321042%2fgenerate-id-based-on-string-in-excel%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
Although macro's/UDF's would be a handy way, here is a rather simple formula:
The formula I used in A2 translates to:
=IF(COUNTIF($B$2:B2,B2)>1,INDEX($A$2:A2,MATCH(B2,$B$2:B2,0)),MAX($A$1:A1)+1)
answered Mar 24 at 9:34
JvdVJvdV
4,5932517
add a comment |
Although macro's/UDF's would be a handy way, here is a rather simple formula:
The formula I used in A2 translates to:
=IF(COUNTIF($B$2:B2,B2)>1,INDEX($A$2:A2,MATCH(B2,$B$2:B2,0)),MAX($A$1:A1)+1)
answered Mar 24 at 9:34
JvdVJvdV
4,5932517
add a comment |
Although macro's/UDF's would be a handy way, here is a rather simple formula:
The formula I used in A2 translates to:
=IF(COUNTIF($B$2:B2,B2)>1,INDEX($A$2:A2,MATCH(B2,$B$2:B2,0)),MAX($A$1:A1)+1)
answered Mar 24 at 9:34
JvdVJvdV
4,5932517
Although macro's/UDF's would be a handy way, here is a rather simple formula:
The formula I used in A2 translates to:
=IF(COUNTIF($B$2:B2,B2)>1,INDEX($A$2:A2,MATCH(B2,$B$2:B2,0)),MAX($A$1:A1)+1)
answered Mar 24 at 9:34
JvdVJvdV
4,5932517
answered Mar 24 at 9:34
JvdVJvdV
4,5932517
answered Mar 24 at 9:34
JvdVJvdV
4,5932517
answered Mar 24 at 9:34
JvdVJvdV
4,5932517
4,5932517
add a comment |
add a comment |
This UDF is a bit shaky but will work depending on how many names you have and the spread of the names ...
Public Function GenerateId(ByVal strText As String) As Long
Dim i As Long
For i = 1 To Len(strText)
strChar = UCase(Mid(strText, i, 1))
GenerateId = GenerateId + Asc(strChar)
Next
End Function
... there is a chance it will double up but it's not easy to predict. You'd have to run all names through and check all outcomes.
Also, I know it's not a sequential ID approach starting from 1 but you didn't specify that so I used some creative licence. :-)
Also, this will ensure that the name retains it's ID if the data is sorted differently, not sure if that's a requirement or not but it's a consideration.
Worth a potential shot anyway.
answered Mar 24 at 7:00
SkinSkin
2,0132515
add a comment |
This UDF is a bit shaky but will work depending on how many names you have and the spread of the names ...
Public Function GenerateId(ByVal strText As String) As Long
Dim i As Long
For i = 1 To Len(strText)
strChar = UCase(Mid(strText, i, 1))
GenerateId = GenerateId + Asc(strChar)
Next
End Function
... there is a chance it will double up but it's not easy to predict. You'd have to run all names through and check all outcomes.
Also, I know it's not a sequential ID approach starting from 1 but you didn't specify that so I used some creative licence. :-)
Also, this will ensure that the name retains it's ID if the data is sorted differently, not sure if that's a requirement or not but it's a consideration.
Worth a potential shot anyway.
answered Mar 24 at 7:00
SkinSkin
2,0132515
add a comment |
This UDF is a bit shaky but will work depending on how many names you have and the spread of the names ...
Public Function GenerateId(ByVal strText As String) As Long
Dim i As Long
For i = 1 To Len(strText)
strChar = UCase(Mid(strText, i, 1))
GenerateId = GenerateId + Asc(strChar)
Next
End Function
... there is a chance it will double up but it's not easy to predict. You'd have to run all names through and check all outcomes.
Also, I know it's not a sequential ID approach starting from 1 but you didn't specify that so I used some creative licence. :-)
Also, this will ensure that the name retains it's ID if the data is sorted differently, not sure if that's a requirement or not but it's a consideration.
Worth a potential shot anyway.
answered Mar 24 at 7:00
SkinSkin
2,0132515
This UDF is a bit shaky but will work depending on how many names you have and the spread of the names ...
Public Function GenerateId(ByVal strText As String) As Long
Dim i As Long
For i = 1 To Len(strText)
strChar = UCase(Mid(strText, i, 1))
GenerateId = GenerateId + Asc(strChar)
Next
End Function
... there is a chance it will double up but it's not easy to predict. You'd have to run all names through and check all outcomes.
Also, I know it's not a sequential ID approach starting from 1 but you didn't specify that so I used some creative licence. :-)
Also, this will ensure that the name retains it's ID if the data is sorted differently, not sure if that's a requirement or not but it's a consideration.
Worth a potential shot anyway.
answered Mar 24 at 7:00
SkinSkin
2,0132515
edited Mar 25 at 11:58
answered Mar 24 at 7:00
SkinSkin
2,0132515
answered Mar 24 at 7:00
SkinSkin
2,0132515
answered Mar 24 at 7:00
SkinSkin
2,0132515
2,0132515
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55321042%2fgenerate-id-based-on-string-in-excel%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
How about
Johnas the ID?– Klaus D.
Mar 24 at 5:58
I want to have numeric unique ID
– SmitSherlock
Mar 24 at 6:34
It might be a good idea to include that in your question.
– Klaus D.
Mar 24 at 6:40