assign a letter to a range of letter values e

Microsoft Office excel 2007 home & stude...
June 30, 2010 at 12:05:32
Specs: Windows Vista

I want to assign a value (#/letter) to a set of names, then apply it to a range of different names (reapeated) and check which names I dont have down on my set.
ej: set of names: tom (code T), susan (S) and fred (F)
angust etc
the cells next that have angust will not have a code.

See More: assign a letter to a range of letter values e

Report •

June 30, 2010 at 12:18:21
Is there a reason that you have told us what you want to do?

Report •

June 30, 2010 at 12:26:20
well Im looking for a formula in excel to do this. do u have any suggestions.

Report •

June 30, 2010 at 12:33:59

The point was that it would be appropriate to be asked for help.

Everyone who provides answers is volunteering their time and knowledge.


Report •

Related Solutions

June 30, 2010 at 12:42:37
It adds an air of politeness to a post if you actually ask a question, maybe even throwing in a "Please" and/or a "Thank you" instead of just telling us us what you want.

Would you walk into a co-worker's office or a friend's house and just blurt out that you want something or would you say something like "Hey, could you help me with this..."?

There's no reason that it should be any different in a forum such as this one. We're all just volunteers, giving of our free time, trying to help out. Politeness goes a long way.

As to your question, I'm not sure why you need the code. If you want to see if a name is repeated, why not just use COUNTIF?

For example, with your list in A1:A100, and tom in A1, try:

=IF(COUNTIF($A$1:$A$100,A1)>1, A1 & " is repeated")

Since your example has "tom" listed twice, the formula above will return:

tom is repeated

If there is a reason that the code is required, let us know and we'll see what else we can offer.

Report •

June 30, 2010 at 12:59:27

If your set of names is in column D, cells D2 to D5
your long list of names is in column A starting at cell A2, enter this formula in cell B2:
Now drag this formula down column A to extend it to all the rows of your long list of names.

Note the $ signs, they are essential for maintaining the correct cell references when the formula is extended.

Any name in the long list in column A that is not present in your set of names will be labeled "New".

VLOOKUP with the False argument will return the #NA error value when it does not find a match (see the VLOOKUP() help file)
Then an IF() statement tests for the #NA error or no error and returns "New" when VLOOKUP() returns the #NA error.


Report •

June 30, 2010 at 13:07:05
Listen, Im terribly sorry that I did not ask for help at first. I know it could have come out as impolite, but Im really not used to asking questions on posts.
well anyways I did not know that ur all volunteers, listen I dont even know how this works. Its my first time and u dont have to be too harsh, I spent time on formulating good my question, which obviously was not good enough, but still some things you overlook when its the first time. My first language is not even english, so Im a bit conscious about it.
But thanx for the recomendations, I will keep it in mind next time.
as for my question:
I really would like to see how I could identify the names that are not repeated, the names that I dont have down, u see I had to search through the whole list to check all the posible names. but I have skipped some, dont know which ones they are, it really long, and some I cannot appply any formula because are mispelled so I keep on loosing records.

Anyways if u can be fo any help it would be nice or let me know if u dont understand my question. I know there is a simple way but cannot work it out.

Report •

June 30, 2010 at 13:09:56
Ok, thanx I will try it out. I think I have it.


Report •

June 30, 2010 at 13:25:32

About adding names and not misspelling them - look at data validation. It makes adding names easy - you maintain one 'approved' list of 'correct' names and then every cell that you want to put a name in, can only select from the approved list.

When a new person is added, include them in the approved list.

Please ask if you want to use Data Validation and need some more help.


Report •

June 30, 2010 at 13:44:58

well I did not do the list, it was given to me, so it already had the errors on them. Can I use data validation, in this case?
the formula u gave me worked, but for some reason some names that are already in the list are marked as new. any idea of what can be happening.

Report •

June 30, 2010 at 15:34:59

No - data validation is for new data entry only.

If some names that look OK are giving "New" they may have extra spaces before or after the name.

Do this on a copy of your data first:

Try adding a new column next to the long list of names.
If the long list is in column A with the first name in cell A2
In the new column, say cell B2 enter =trim(A2)
This will remove extra spaces.

Drag the formula down alongside all the existing names.
Now copy the cells in column B
Select the cell with the first name in column A (cell A2) and Paste Special... and select Paste Values

Column A should now have 'cleaned' names.
Try the lookup formula again. This may have removed some of the extra "New" results.


Report •

June 30, 2010 at 18:40:40
For other ideas on how to "clean" your data, see here:

Top Ten Ways To Clean Your Data

Report •

Ask Question