Hi everyone,

I hope someone can help me with this data transformation problem,
because I'm working with a big enough dataset that I really don't want
to do this by hand (I have neither the time nor the finances). I am
using Excel 2003. I have a dataset that looks like this (n=231):

subjid jobtitle site dur1 to dur243 freqwk1 to freqwk243
1 A A 30... 15 5...10
2 B A
3 C A
4 A B
5 B B
6 C B

In other words, this is a dataset holding the survey responses of 231
participants, in which they completed information about the duration
and frequency with which they performed 243 different tasks; each task
duration and each task frequency is listed as a separate variable.

For theoretically driven reasons too long to go into here, rather than
use the actual reported values, I want to use the modal values reported
by each job title at each site as the value for frequency and duration
for each task. In other words, if the mode for variable dur1 for
subjects in jobtitle A, site A is say 20, I want to replace their
existing values for variable dur 1 with 20. Ditto for all other
combinations (i.e., jobtitle B site A; job title A site B; etc.) I
have 7 job titles and 7 sites.
So I want to create 486 new variables that would hold these new modal
values as described above.

Is there a way to do this in Excel? Or if not, maybe in Access? How
would I go about doing this?


Thanks in advance,

Sylvia J. Hysong