Suppose you had a column in the Data worksheet called "Gender", with entries of F for female, and M for male. Then, suppose that, for some reason, you'd like to instead have a code of 1 for female, and a code of 2 for male.

 

The recode option would be for you.

 

Suppose you had a Data worksheet, with a column for "Country", with entries such as NZ, AU, CA, US, DO, and VZ. Then, suppose for some reason you'd like to create a new column, to be called "Language", with NZ AU CA and US to all be coded EN, with DO and VZ to be coded SP. (Apologies to CA residents who speak SP.)

 

The recode option would do the job for you.

 

Or, suppose you'd like to get group breakouts, with only CA and US selected. You could use Recode, entering a new code of "Exclude" for all records without CA or US, after which you'd go for those breakouts.

 

Finally, although you know how to use the *tst card to select only certain Data records, and the NumericFilter2 special macro, you'd like to just delete Data records with, say, SP in the Language column. The Recode option could do it.

 

How it works

 

You go to the Data worksheet, and click on the Recode a Data column option under the Move+ menu.

 

You indicate the column which has the source data, that is, the column with the codes you want to work with.

 

The Recode macro copies the column to the far right side of the worksheet. Note the use of the word "copies" -- the macro does not delete or alter the original column in any way.

 

Then, the macro looks at the first entry in the copied column, that is, in row 3 of the newly-copied column. Let's say it finds a value of NZ. This little snap indicates what next happens:

 

 

The macro adds another new column to the worksheet, immediately to the right of the copied column.

 

It then asks you what NZ should become. You enter EN in the little 'What's new?' box, and click OK. All rows whose entry in the copied column is NZ will then have EN in the adjacent column, that is, in the recoded column.

 

After this the macro returns to look at the next entry in the copied column. It'll find CA (according to the little snapshot above). You'll be asked what CA is to equate to in the new column. You enter EN.

 

And so forth. We might end up with rows looking like this:

 

 

Got the idea? It's pretty simple (which will reflect poorly on you if you didn't get the idea).

 

At the end of this process, the Recode macro leaves you with two new columns: the copy of the original column, and a column with the recoded values. It's easy to see if the macro has done what you intended -- just scan down the columns. If the result is not what you had in mind, just delete these two columns, and start again.

 

On the other hand, if the result is in fact what you wanted, you might then want to delete the column which has the copy of the original column. You don't have to do this, but if you do you'll save a bit of space. There are, after all, only a certain number of columns which a worksheet may have (256 was the limit in earlier versions of Excel, and this limit could be an issue at times -- however, with Excel 2007, you can have over ten thousand columns!).

 

Astute readers might have their hands up at this point: You said you wanted to have a new column called "Language", but instead you have "Country recoded" at the top of the new column. You haven't finished, have you?

 

Correct (ho-hum). We're left with the back-breaking task of typing 'Language' into the cell which presently has 'Country recoded'.

 

If your ultimate objective is to get group breakouts, and you'd like to exclude all records with DO or VZ in the original column, you'd respond thusly:

 

 

You don't have to type the whole word; just 'ex' will do. When the breakouts routine runs, it will ignore all rows which have been excluded in this manner.

 

Similarly, if there are records you want to delete, enter the word 'delete' in the little box, or just 'del', without the apostrophes. Lertap will set about deleting rows from the Data worksheet once the Recode macro has worked completely down the original column.

 

Please note that Lertap will say No-No! if you're asking for records to be deleted from Data when your workbook also has a Scores worksheet. There's a very critical correspondence between the Data and Scores worksheets, and Lertap tries its best to see that this correspondence is not disturbed.

 

Herewith all the usual warnings about deleting records from Data: you cannot recover them. Best to make a copy of the workbook before deleting records, something you can do by using the New menu.

 

If you click on OK without entering anything in the little box, the Recode macro will use whatever value you last entered. This makes it a bit easier to apply the same new code multiple times.

 

If the original value is blank, or empty, then, to maintain the blank, press your keyboard's space bar once, and then click on OK. Otherwise, if you don't want blanks in the new column, simply enter something in the little box, and, in the blank of an eye ....

 

Finally: as you may know, Excel has its own recode facilities, and they're quite respectable. If you page forward to the next topic you'll see.