Excel Question
salo
Posts: 161
Forum Member
✭
Hope I can explain this properly. I have an excel sheet with 20 sets of information. In one column I have names (up to 300 for each of the 20 sets) that should actually be a different identifier. I have another excel sheet with all possible names (approx 10,000) in one column and the correct identifier in another column.
Is there an easy/quick way to change the names in the 1st excel sheet rather than using find/replace?
Is there an easy/quick way to change the names in the 1st excel sheet rather than using find/replace?
0
Comments
Bill | William
Billy | William
Will | William
Bob | Robert
Robbie | Robert
In sheet one you have a column which contains Bill, Billy etc and you’d like to see William in each case?
If so then a VLOOKUP will do the job. So if you have Bill in A1, insert a column B and put this next to it
=VLOOKUP(A1,Sheet2!A1:B5,2,FALSE)
Which is a vertical lookup of the value in A1 in the first column of the table on sheet2 that’s in A1 to B5. The FALSE denotes it needs to be an exact match
You will now have on your first sheet: Bill | William and you can fill the formula down to translate them all
Seriously lacking in knowledge (in most things!): how does one automatically do numbering in sequence (1 to 100 for example) for rows without manually typing in the figures. Many thanks.
See http://spreadsheets.about.com/od/exceltips/tp/2010-09-03-Excel-2007-Fill-Handle-Tutorial-Hub.htm
Thanks - it works, even I managed to do it!