Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding unique names--then converting those names to unique number
Using either Excel 2003 or 2007, doesn't matter to me.
I have a set of data over many sheets, that I'm currently consolidating into one sheet. One of the columns is FamilyName, i.e. "Smith, Jones, Wilson, etc.". Some of the names in this column are repeated. What I need to do is take that column, find the unique names, and convert them into unique number values into another column. For example: FamilyName FamilyNum Jones 1111 Smith 2222 Wilson 3333 Rogers 4444 Smith 2222 Johnson 5555 Is something like this even possible? thanks for any advice. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding unique names--then converting those names to unique number
if amily name in A and num in b
then try in B2 Assuming Row one is headder enter the first unique numberyou want (In your example it would be 1111) in B3 =if(countif($A$2:A3,A3)1, vlookup(A3,A:B,2,0),Max($B2:B2)+unique number increment) assuming you want more than 1 as the unique number increment (in your example it would also be 1111) copy and paste down as far as you want. "Proton" wrote: Using either Excel 2003 or 2007, doesn't matter to me. I have a set of data over many sheets, that I'm currently consolidating into one sheet. One of the columns is FamilyName, i.e. "Smith, Jones, Wilson, etc.". Some of the names in this column are repeated. What I need to do is take that column, find the unique names, and convert them into unique number values into another column. For example: FamilyName FamilyNum Jones 1111 Smith 2222 Wilson 3333 Rogers 4444 Smith 2222 Johnson 5555 Is something like this even possible? thanks for any advice. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding unique names--then converting those names to unique number
Try this:
Assume the names are in the range A2:An Enter 1 in B2 Enter this formula in B3 and copy down as needed: =IF(COUNTIF(A$2:A3,A3)1,VLOOKUP(A3,A$2:B2,2,0),MA X(B$2:B2)+1) Biff "Proton" (nospam) wrote in message ... Using either Excel 2003 or 2007, doesn't matter to me. I have a set of data over many sheets, that I'm currently consolidating into one sheet. One of the columns is FamilyName, i.e. "Smith, Jones, Wilson, etc.". Some of the names in this column are repeated. What I need to do is take that column, find the unique names, and convert them into unique number values into another column. For example: FamilyName FamilyNum Jones 1111 Smith 2222 Wilson 3333 Rogers 4444 Smith 2222 Johnson 5555 Is something like this even possible? thanks for any advice. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding unique names--then converting those names to unique number
1. put all you names into a single column with a label to the top of the
column. 2. Data Filter Advanced FIlter copy to new column/unique records only For example: item item cat cat cat hat hat bat bat rat rat dog dog mouse dog hat hat mouse mouse The second column are the uniques In C2 enter: 1000 In C3 enter: =C2+1 and copy down: item item cat cat 1000 cat hat 1001 hat bat 1002 bat rat 1003 rat dog 1004 dog mouse 1005 dog hat hat mouse mouse Now each name has a unique number. From anywhere in the spreadsheet, if you want the number for a name, just VLOOKUP() the name in column B to get the value in column C. -- Gary''s Student - gsnu200729 "Proton" wrote: Using either Excel 2003 or 2007, doesn't matter to me. I have a set of data over many sheets, that I'm currently consolidating into one sheet. One of the columns is FamilyName, i.e. "Smith, Jones, Wilson, etc.". Some of the names in this column are repeated. What I need to do is take that column, find the unique names, and convert them into unique number values into another column. For example: FamilyName FamilyNum Jones 1111 Smith 2222 Wilson 3333 Rogers 4444 Smith 2222 Johnson 5555 Is something like this even possible? thanks for any advice. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding unique names--then converting those names to unique nu
Close, but not quite. I tried it out on a test sheet and here's what I got:
FamilyName FamilyNum Alice 1111 Bingo 1112 Charlie 1113 Daniel 1114 Bingo 1112 Frank 1113 Alice 1111 It makes some of them unique, but Charlie and Frank share 1113. (But it's certainly closer than what I had before!) "bj" wrote: if amily name in A and num in b then try in B2 Assuming Row one is headder enter the first unique numberyou want (In your example it would be 1111) in B3 =if(countif($A$2:A3,A3)1, vlookup(A3,A:B,2,0),Max($B2:B2)+unique number increment) assuming you want more than 1 as the unique number increment (in your example it would also be 1111) copy and paste down as far as you want. "Proton" wrote: Using either Excel 2003 or 2007, doesn't matter to me. I have a set of data over many sheets, that I'm currently consolidating into one sheet. One of the columns is FamilyName, i.e. "Smith, Jones, Wilson, etc.". Some of the names in this column are repeated. What I need to do is take that column, find the unique names, and convert them into unique number values into another column. For example: FamilyName FamilyNum Jones 1111 Smith 2222 Wilson 3333 Rogers 4444 Smith 2222 Johnson 5555 Is something like this even possible? thanks for any advice. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding unique names--then converting those names to unique nu
Hi,
enter 1111 in the cell B2 Try this formula in the cell B3: =IF(ISNA(VLOOKUP(A3,$A$2:B2,1,FALSE)),B2+1,VLOOKUP (A3,$A$2:B2,2,FALSE)) It should work. Thanks, -- Farhad Hodjat "T. Valko" wrote: Try this: Assume the names are in the range A2:An Enter 1 in B2 Enter this formula in B3 and copy down as needed: =IF(COUNTIF(A$2:A3,A3)1,VLOOKUP(A3,A$2:B2,2,0),MA X(B$2:B2)+1) Biff "Proton" (nospam) wrote in message ... Using either Excel 2003 or 2007, doesn't matter to me. I have a set of data over many sheets, that I'm currently consolidating into one sheet. One of the columns is FamilyName, i.e. "Smith, Jones, Wilson, etc.". Some of the names in this column are repeated. What I need to do is take that column, find the unique names, and convert them into unique number values into another column. For example: FamilyName FamilyNum Jones 1111 Smith 2222 Wilson 3333 Rogers 4444 Smith 2222 Johnson 5555 Is something like this even possible? thanks for any advice. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding unique names--then converting those names to unique nu
for frank to be 1113 it added 1 to the value for bingo above
check that the max function is B$2:B2 because it did not find the max of the values above. "Proton" wrote: Close, but not quite. I tried it out on a test sheet and here's what I got: FamilyName FamilyNum Alice 1111 Bingo 1112 Charlie 1113 Daniel 1114 Bingo 1112 Frank 1113 Alice 1111 It makes some of them unique, but Charlie and Frank share 1113. (But it's certainly closer than what I had before!) "bj" wrote: if amily name in A and num in b then try in B2 Assuming Row one is headder enter the first unique numberyou want (In your example it would be 1111) in B3 =if(countif($A$2:A3,A3)1, vlookup(A3,A:B,2,0),Max($B2:B2)+unique number increment) assuming you want more than 1 as the unique number increment (in your example it would also be 1111) copy and paste down as far as you want. "Proton" wrote: Using either Excel 2003 or 2007, doesn't matter to me. I have a set of data over many sheets, that I'm currently consolidating into one sheet. One of the columns is FamilyName, i.e. "Smith, Jones, Wilson, etc.". Some of the names in this column are repeated. What I need to do is take that column, find the unique names, and convert them into unique number values into another column. For example: FamilyName FamilyNum Jones 1111 Smith 2222 Wilson 3333 Rogers 4444 Smith 2222 Johnson 5555 Is something like this even possible? thanks for any advice. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Finding unique names--then converting those names to unique number
Or, you could just sort the names then:
Enter 1 in B2 Enter this much simpler formula in B3 and copy down as needed: =IF(A3=A2,B2,B2+1) Biff "T. Valko" wrote in message ... Try this: Assume the names are in the range A2:An Enter 1 in B2 Enter this formula in B3 and copy down as needed: =IF(COUNTIF(A$2:A3,A3)1,VLOOKUP(A3,A$2:B2,2,0),MA X(B$2:B2)+1) Biff "Proton" (nospam) wrote in message ... Using either Excel 2003 or 2007, doesn't matter to me. I have a set of data over many sheets, that I'm currently consolidating into one sheet. One of the columns is FamilyName, i.e. "Smith, Jones, Wilson, etc.". Some of the names in this column are repeated. What I need to do is take that column, find the unique names, and convert them into unique number values into another column. For example: FamilyName FamilyNum Jones 1111 Smith 2222 Wilson 3333 Rogers 4444 Smith 2222 Johnson 5555 Is something like this even possible? thanks for any advice. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding the MAX number while considering similar names | Excel Discussion (Misc queries) | |||
Finding unique numbers in a column | Excel Discussion (Misc queries) | |||
Finding unique records from a list. | Excel Discussion (Misc queries) | |||
Pull unique names for drop down list | Excel Discussion (Misc queries) | |||
Count Unique Names in list w/ Additional Criteria? | Excel Worksheet Functions |