Randomized choice tasks data setup

[julian0309]

Hello,

I have a choice experiment data where the order of choice tasks is randomized.
'choice' is recorded responses ignoring the randomization, and 'random' shows to which choice task (chtk) the recorded choices correspond. 'new' is the new choice variable I'm trying to construct. For example, 'random' for 'caseid'=1 & 'chtk'=1 is 2 which indicates that the recorded choices for chtk=1 are in fact for choice task #2. Therefore, 'new' takes the value of 3 for chtk=2.

If someone could teach me how to create the 'new' variable effectively, that would be greatly appreciated!

id caseid chtk alt choice random
1 1 1 1 3 2
1 1 1 2 3 2
1 1 1 3 3 2
1 2 2 1 2 5
1 2 2 2 2 5
1 2 2 3 2 5
1 3 3 1 3 7
1 3 3 2 3 7
1 3 3 3 3 7
1 4 4 1 2 10
1 4 4 2 2 10
1 4 4 3 2 10
1 5 5 1 3 9
1 5 5 2 3 9
1 5 5 3 3 9
1 6 6 1 3 1
1 6 6 2 3 1
1 6 6 3 3 1
1 7 7 1 1 6
1 7 7 2 1 6
1 7 7 3 1 6
1 8 8 1 1 3
1 8 8 2 1 3
1 8 8 3 1 3
1 9 9 1 2 8
1 9 9 2 2 8
1 9 9 3 2 8
1 10 10 1 2 4
1 10 10 2 2 4
1 10 10 3 2 4

[Michiel Bliemer]

Perhaps you could use some kind of vlookup(...) in Excel to do this.
Alternatively, you could just replace the third column (chtk) with the sixth column (random), which would also link choice task 2 with a choice for 3.

Maybe others have some clever ideas

Michiel