Digital Spy

Search Digital Spy
 

DS Forums

 
 

Excel Question


Reply
Thread Tools Search this Thread
Old 22-10-2012, 11:38
salo
Forum Member
 
Join Date: Feb 2009
Posts: 72

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?
salo is offline   Reply With Quote
Please sign in or register to remove this advertisement.
Old 22-10-2012, 12:07
platelet
Forum Member
 
Join Date: May 2002
Location: GL51 0EX
Posts: 7,379
Okay if Iím reading your post right you have a table in sheet 2 which provides the translation you want something like

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
platelet is offline   Reply With Quote
Old 22-10-2012, 12:19
salo
Forum Member
 
Join Date: Feb 2009
Posts: 72
Thanks - that's exactly what I need. I'll be finished it in no time!
salo is offline   Reply With Quote
Old 29-12-2012, 00:57
Sideburns57
Forum Member
 
Join Date: Jun 2010
Posts: 1,959
Can I please ask a random Excel question (cheekily using someone else's thread!).

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.
Sideburns57 is offline   Reply With Quote
Old 29-12-2012, 02:08
psionic
Forum Member
 
Join Date: May 2002
Location: Crystal Palace TX
Posts: 18,916
Can I please ask a random Excel question (cheekily using someone else's thread!).

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.
One way of doing it is:[LIST][*]Enter 1 in the 1st row[*]Enter 2 in the 2nd row[*]Select both the above cells [*]Notice at the bottom right of the selection there's a little blob called a fill handle. Drag it downwards and subsequent cells will fill in the correct sequence.[/LIST]
psionic is offline Follow this poster on Twitter   Reply With Quote
Old 29-12-2012, 02:28
Sideburns57
Forum Member
 
Join Date: Jun 2010
Posts: 1,959
Nope, sorry - no blob seen!
Sideburns57 is offline   Reply With Quote
Old 29-12-2012, 03:33
psionic
Forum Member
 
Join Date: May 2002
Location: Crystal Palace TX
Posts: 18,916
psionic is offline Follow this poster on Twitter   Reply With Quote
Old 29-12-2012, 18:29
Sideburns57
Forum Member
 
Join Date: Jun 2010
Posts: 1,959
Thanks. I will have a go!
Sideburns57 is offline   Reply With Quote
Old 29-12-2012, 18:50
Sideburns57
Forum Member
 
Join Date: Jun 2010
Posts: 1,959
"One small step for man.........."

Thanks - it works, even I managed to do it!
Sideburns57 is offline   Reply With Quote
 
Reply



Thread Tools Search this Thread
Search this Thread:

Advanced Search

 
Forum Jump


All times are GMT +1. The time now is 22:38.