+ Post New Thread
Results 1 to 4 of 4
How do you do....it? Thread, Ethnicity - Match code to a word in Excel in Technical; ...
  1. #1

    Join Date
    Apr 2012
    Location
    London
    Posts
    107
    Thank Post
    4
    Thanked 5 Times in 5 Posts
    Rep Power
    6

    Question Ethnicity - Match code to a word in Excel

    Hi All,

    I know itís probably easy, but I have been asked to collect all the data we hold on staff and their ethnicity, age, etc. and dump it into a spreadsheet.
    I have managed to get all of the information out of our 15 year old database system, but it records ethnicity as a code, rather than an actual word. I know this is quite common in these systems.

    I know what code stands for what ethnicity, 1=white British for example, But does anyone know how to make Excel match up a number with a word and produce the result in a new cell?

    It would save me a lot of time and I would really appreciate if someone could help on this.

    Thanks.

  2. #2

    LosOjos's Avatar
    Join Date
    Dec 2009
    Location
    West Midlands
    Posts
    5,563
    Thank Post
    1,469
    Thanked 1,222 Times in 831 Posts
    Rep Power
    752
    You'll want to use a vlookup.

    Create a table with the code in one column and the description immediately to the right, then perform a vlookup against it.

  3. Thanks to LosOjos from:

    spacebar (7th November 2013)

  4. #3

    bladedanny's Avatar
    Join Date
    May 2009
    Location
    Sheffield
    Posts
    1,297
    Thank Post
    191
    Thanked 309 Times in 230 Posts
    Rep Power
    170
    A vlookup would probably be the best. On another sheet (or separate part of the current sheet) Set up a two column table. In the first column have the code and in the second have the text to display.

    Then in your main table create a new column next to the ethnicity code and put it.
    =VLOOKUP($CELLWITHCODE,$RANGEOFTABLE,2), eg =VLOOKUP(A6,$E$12:$F$21,2) The '2' is the column in which to get the text from.

    Hope this helps.

    --edit--

    Make sure that in the $RANGEOFTABLE part of the vlookup your range has the $ signs as above, otherwise it will change as you replicate it down.
    Last edited by bladedanny; 7th November 2013 at 12:46 PM.

  5. Thanks to bladedanny from:

    spacebar (7th November 2013)

  6. #4

    Join Date
    Apr 2012
    Location
    London
    Posts
    107
    Thank Post
    4
    Thanked 5 Times in 5 Posts
    Rep Power
    6
    Thanks bladedanny and losojos, I managed to do it and got there in the end.

    Thank-you.

SHARE:
+ Post New Thread

Similar Threads

  1. Is it possible to do this in excel?
    By Kineas in forum Office Software
    Replies: 5
    Last Post: 10th June 2012, 03:34 PM
  2. Unable to print from Word or Excel 2003
    By pennywest in forum Windows
    Replies: 8
    Last Post: 30th November 2009, 06:41 AM
  3. VB code to colour cells in a word table
    By park_bench in forum Coding
    Replies: 4
    Last Post: 30th June 2008, 08:52 AM
  4. need to extract text from a string in excel
    By projector1 in forum How do you do....it?
    Replies: 7
    Last Post: 14th February 2007, 01:41 PM
  5. Replies: 1
    Last Post: 21st January 2007, 02:51 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •