Add Variable Codes to the Colorado Dataset Using Excel

  1. Grab a copy of the question/variable codebook you created at https://docs.lrs.org/public-library-annual-report/code-the-annual-survey/
  2. Create a new worksheet in the Colorado spreadsheet.
    1. Name it “Codes” with a capital C.
  3. In column A of the worksheet list all of the question numbers.
    1. 1.1
    2. 1.2
    3. 1.3
    4. 2.1
    5. 2.2
    6. etc.
  4. In column B of the worksheet list each questions corresponding code
    1. library
    2. library_local
    3. address
    4. pacweb
    5. eusers
    6. etc.
  5. In the main data worksheet of your file, create a new row near the top, directly under the row that contains question numbers.
  6. Use VLOOKUP to match each question number with its code. Copy the VLOOKUP function below into every cell in row you just created.
=VLOOKUP(INDIRECT(ADDRESS(ROW()-1,COLUMN())),Codes!$A1:$B210, 2, FALSE)

This is fairly tricky, so let’s unpack it.

1. VLOOKUP(
2.    INDIRECT(
3.        ADDRESS(
4.            ROW()-1,COLUMN()
5.        )
6.    ), Codes!$A1:$B210, 2, FALSE
7. )

Lines:

  1. Executes the VLOOKUP command
  2. Uses the INDIRECT function to create a reference from text.
  3. Uses the ADDRESS function to create a cell address from a row and column.
  4. If you’re in Column B, Row 2, this tells ADDRESS (and then INDIRECT) to go up one cell, to Column B, Row 1.  In practical usage, we are putting the VLOOKUP function into every cell in Row 2, and the function (in each cell) look at the value in Row 1 which contains the question numbers.
  5. Closes the ADDRESS function
  6. This is where the VLOOKUP magic happens.  It takes the value from the cell above and compares it to the 1st column (A) in the “Codes” worksheet.  If it finds a match, it returns the value from the 2nd column (B) in the “Codes” worksheet.

Whew…tricky, huh?  Well you’re not done yet.

  1. Create a new row below your dynamically populated codes.
  2. Copy the row that contains the codes.
  3. Paste Special as values into the new row.
  4. Save your file.

You should now have a worksheet with 3 headers:

  1. Question numbers
  2. Dynamic codes that are the result of VLOOKUP
  3. Text values of the codes in #2.

This is extremely important because if you try and save the worksheet to .CSV without creating the 3rd header, then the codes probably won’t be save correctly in the resulting file.

Scroll to Top