Results 1 to 3 of 3
2013-05-07, 12:36 #1
- Join Date
- Aug 2001
- Anaheim, California, USA
- Thanked 2 Times in 2 Posts
Add User ID's to existing spreadsheet
I am using Excel 2010 on Windows 7.
I have a spreadsheet that I have been using for many years to keep track of participants in an annual event. It should be on a database, but I don't have time and I have a lot of other information that I track in other worksheets of the same workbook.
I have 1706 entries, for 517 different people. Some have participated only once, others multiple times. The event started in 1989.
2 problems that I have:
1) sometimes the same people are entered with different names, Bill & William, Bob & Rob, etc.
2) some of the girls have grown up into women and have gotten married, so a new last name but I don't want to change their maiden name from an old event.
When I sort the list by name, to see when each one participated, it gets confusing.
So I had a good idea, I will give each one a User ID, then I can sort by ID and get more accurate results.
Here's my question, is there an easy way to assign an ID? I can sort by name and then just give an ID to each group of same names, but that seems tedious. There must be a better way. Anyone have any suggestions?
2013-05-07, 14:23 #2
- Join Date
- Jul 2002
- Pittsburgh, Pennsylvania, USA
- Thanked 342 Times in 335 Posts
I think you could use a formula for a first (automatic) pass. Sort by name, then Create a column counting the number of the items above a name. Then create a new column which if the count is 0 add 1 to the value above it, if more than 0, add the value above.
Then make a second pass and take all alternate names/duplicates and manually change those names to match the lowest ID for that person. Then you can either delete those IDs that are no longer needed or reuse them if desired.
2013-05-08, 21:12 #3
- Join Date
- Aug 2010
- Pa, USA
- Thanked 644 Times in 588 Posts
Here is a list of sample runners in a race listing their name and the year they participated. There is a blank column for IDs yet to be assigned. There is mix of names, some being exactly the same while some have the same last name but the first name is a nick name. On the sheet is a button to assign the ID. By clicking on the button, the names will be sorted alphabetically and then by year if not already, then a list box will open populated with all the names. Select the names you wish to group. Please see the image
After the names are grouped, click the assign button and an ID# starting at 1000 will be assigned to them and placed in the ID column next to the names. The listbox will immediately refresh with those names removed ready for the next grouping. Repeat the process as many times as desired. The ID#s will increment by 5 and will always be unique. See second Image.
When finished, click the close button to retire the userform and return to the worksheet. It took only seconds to assign ID#s to the sample list.