How to Autogenerate Text Assigned to a Number (Not spell it out)

bluevelvet113

New Member
Joined
Apr 14, 2021
Messages
4
Office Version
  1. 365
Platform
  1. MacOS
Hello everyone,

I have an Excel document which lists anonymous participants for a case study. Each participant has been assigned an ID number (e.g. ID: 1). The ID numbers are listed in Column A. The corresponding columns each provide demographic information (e.g. gender, race) and the participants' education background (e.g. college graduate) but instead of these terms being written out, they have been assigned a code. For example, 1=female, 0=male, 8=High School graduate, 9=College graduate.

I would like to have the codes retroactively populate with the corresponding demographic information. For example, change all '1' to read 'female', '8' to read 'High School graduate'. The challenge with this, however, is that the numerical codes can have a different text equivalents depending on the 'demographic information' in each column. For example, in the Gender column (Column B) '1'=female, yet in the Native Language ('Natlang') column (Column C), '1'=English.

I've attached a screenshot for reference. Please let me know if there is a way to do this. By the way, I'm also a basic Excel user so an easy tutorial would be helpful if possible! Thank you!
 

Attachments

  • Screen Shot 2021-04-14 at 4.53.24 PM.png
    Screen Shot 2021-04-14 at 4.53.24 PM.png
    15.9 KB · Views: 7

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Habtest

Board Regular
Joined
Jul 30, 2020
Messages
180
Office Version
  1. 2016
Platform
  1. Windows
Formula option could be INDEX(MATCH()), or simply VLOOKUP() if column sequence in the same among input, result and code tables.

Book1
ABCDEFGHIJ
1INPUTCODE TABLE
2#GenderMajorNatlangCodeGenderMajorNatlang
310220MaleEconEnglish
421031FemaleAcctSpanish
530102PhilFrench
641113German
74
8RESULT5
9#GenderMajorNatlang
101MalePhilFrench
112FemaleEconGerman
123MaleAcctEnglish
134FemaleAcctSpanish
Sheet1
Cell Formulas
RangeFormula
B10:D11B10=INDEX($H$3:$J$8,MATCH(B3,$G$3:$G$8,0),MATCH(B$9,$H$2:$J$2,0))
B12:D13B12=VLOOKUP(B5,$G:$J,COLUMN(),0)
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,148
Office Version
  1. 365
Platform
  1. Windows
I've read the question as being that the codes should be replaced in their original locations rather than trying to reproduce the whole table in a new location, although that would be easier, and less error prone with the INDEX and MATCH version.

To actually replace the codes in situ would either need custom formats (not recommended for anything on this scale), or find and replace (ctrl H), one column at a time, matching 'Entire cell contents' in order to prevent incorrect changes.
 

bluevelvet113

New Member
Joined
Apr 14, 2021
Messages
4
Office Version
  1. 365
Platform
  1. MacOS
Formula option could be INDEX(MATCH()), or simply VLOOKUP() if column sequence in the same among input, result and code tables.

Book1
ABCDEFGHIJ
1INPUTCODE TABLE
2#GenderMajorNatlangCodeGenderMajorNatlang
310220MaleEconEnglish
421031FemaleAcctSpanish
530102PhilFrench
641113German
74
8RESULT5
9#GenderMajorNatlang
101MalePhilFrench
112FemaleEconGerman
123MaleAcctEnglish
134FemaleAcctSpanish
Sheet1
Cell Formulas
RangeFormula
B10:D11B10=INDEX($H$3:$J$8,MATCH(B3,$G$3:$G$8,0),MATCH(B$9,$H$2:$J$2,0))
B12:D13B12=VLOOKUP(B5,$G:$J,COLUMN(),0)
Thank you! This is essentially what I'm trying to do. Do you have any tutorials that you can share on how to do a VLOOKUP for this sort of function?
 

bluevelvet113

New Member
Joined
Apr 14, 2021
Messages
4
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

I've read the question as being that the codes should be replaced in their original locations rather than trying to reproduce the whole table in a new location, although that would be easier, and less error prone with the INDEX and MATCH version.

To actually replace the codes in situ would either need custom formats (not recommended for anything on this scale), or find and replace (ctrl H), one column at a time, matching 'Entire cell contents' in order to prevent incorrect changes.
Thanks for your help. I'm essentially trying to do what the user 'Habtest' suggested in the thread. Do you think VLOOKUP would work for this sort of task or is
INDEX MATCH better? Thanks!
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,148
Office Version
  1. 365
Platform
  1. Windows
Do you think VLOOKUP would work for this sort of task
It will work under extremely strict conditions, a single change to the layout could easily cause everything to be wrong. The INDEX MATCH method should not fail.
 

Habtest

Board Regular
Joined
Jul 30, 2020
Messages
180
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Thank you! This is essentially what I'm trying to do. Do you have any tutorials that you can share on how to do a VLOOKUP for this sort of function?
No problem. Regarding the VLOOKUP, the suggested formula above has a nested COLUMN function inside to determine which catagory in the code table to return, you may find plenty tutorials on these 2 online.

Please note, as Jason and I mentioned, you need to maintain a stricly identical layout, any additional/hidden column will skew the outcome. INDEX(MATCH()) combo is a little more complicated but less hassle especially if the table is to be maintained in the long run.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
13,148
Office Version
  1. 365
Platform
  1. Windows
Two more methods using Office 365 functions. The XLOOKUP method works the same as the VLOOKUP method suggested by @Habtest, but with a slightly simpler formula that makes some use of dynamic arrays allowing the formula to spill down, but not right.
Another way that would make the formula spill right as well would be to make a slight change to @Habtest's INDEX formula,
Excel Formula:
=INDEX(H3:J8,MATCH(B3:D6,G3:G8,0),MATCH(B9:D9,H2:J2,0))
Which I believe should also function correctly if the workbook is opened in an older version of excel, although setting up this method in an older version would not be so easy.
Book2
ABCDEFGHIJ
1INPUTCODE TABLE
2#GenderMajorNatlangCodeGenderMajorNatlang
310220MaleEconEnglish
421031FemaleAcctSpanish
530102PhilFrench
641113German
74
8RESULT5
9#GenderMajorNatlang
101MalePhilFrench
112FemaleEconGerman
123MaleAcctEnglish
134FemaleAcctSpanish
14
15RESULT
16#GenderMajorNatlang
171MalePhilFrench
182FemaleEconGerman
193MaleAcctEnglish
204FemaleAcctSpanish
Sheet2
Cell Formulas
RangeFormula
B10:D13B10=FILTER(FILTER($H$3:$J$8,$G$3:$G$8=B3),$H$2:$J$2=B$9,"")
B17:D20B17=XLOOKUP(B3:B6,$G3:$G8,H3:H8)
Dynamic array formulas.
 

bluevelvet113

New Member
Joined
Apr 14, 2021
Messages
4
Office Version
  1. 365
Platform
  1. MacOS
No problem. Regarding the VLOOKUP, the suggested formula above has a nested COLUMN function inside to determine which catagory in the code table to return, you may find plenty tutorials on these 2 online.

Please note, as Jason and I mentioned, you need to maintain a stricly identical layout, any additional/hidden column will skew the outcome. INDEX(MATCH()) combo is a little more complicated but less hassle especially if the table is to be maintained in the long run.
Thanks! That's good to keep in mind.
 

Forum statistics

Threads
1,136,322
Messages
5,675,075
Members
419,549
Latest member
EliteBeat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top