Hello!
Currently, I have a set of raw data that has a time-stamp for each question, each session and for each individual. For example:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Subject[/TD]
[TD]Session[/TD]
[TD]Question[/TD]
[TD]Time[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1a[/TD]
[TD]1[/TD]
[TD]08:59:22[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1a[/TD]
[TD]1[/TD]
[TD]09:00:13[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1a[/TD]
[TD]2[/TD]
[TD]09:01:58[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1a[/TD]
[TD]2[/TD]
[TD]09:01:33[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1b[/TD]
[TD]1[/TD]
[TD]08:33:24[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1b[/TD]
[TD]1[/TD]
[TD]08:31:22[/TD]
[/TR]
</tbody>[/TABLE]
I would need to transpose and combine the data to the following format, in a separate worksheet; so with 1 glance, I can see the time stamp for each subject, with session number and question number grouped together (i.e. session 1a, question 1).
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Subject[/TD]
[TD]Session1a_Question1[/TD]
[TD]Session1a_Question2[/TD]
[TD]Session1b_Question1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]08:59:22[/TD]
[TD]09:01:58[/TD]
[TD]08:33:24[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]09:00:13[/TD]
[TD]09:01:33[/TD]
[TD]08:31:22[/TD]
[/TR]
</tbody>[/TABLE]
I've tried various formulas, including using nested "index", but I just can't seem to get Excel to understand what I'm looking for. Pivot table can't seem to work as well, since I only wanted Excel to retrieve the timestamp, instead of summing them/averaging them. I am sure that all my formulas is wrong and since the amount of data that I have to deal with is extensive, and there is no way that I am going to do this manually. Any help will be very helpful!
Currently, I have a set of raw data that has a time-stamp for each question, each session and for each individual. For example:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Subject[/TD]
[TD]Session[/TD]
[TD]Question[/TD]
[TD]Time[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1a[/TD]
[TD]1[/TD]
[TD]08:59:22[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1a[/TD]
[TD]1[/TD]
[TD]09:00:13[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1a[/TD]
[TD]2[/TD]
[TD]09:01:58[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1a[/TD]
[TD]2[/TD]
[TD]09:01:33[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1b[/TD]
[TD]1[/TD]
[TD]08:33:24[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1b[/TD]
[TD]1[/TD]
[TD]08:31:22[/TD]
[/TR]
</tbody>[/TABLE]
I would need to transpose and combine the data to the following format, in a separate worksheet; so with 1 glance, I can see the time stamp for each subject, with session number and question number grouped together (i.e. session 1a, question 1).
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Subject[/TD]
[TD]Session1a_Question1[/TD]
[TD]Session1a_Question2[/TD]
[TD]Session1b_Question1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]08:59:22[/TD]
[TD]09:01:58[/TD]
[TD]08:33:24[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]09:00:13[/TD]
[TD]09:01:33[/TD]
[TD]08:31:22[/TD]
[/TR]
</tbody>[/TABLE]
I've tried various formulas, including using nested "index", but I just can't seem to get Excel to understand what I'm looking for. Pivot table can't seem to work as well, since I only wanted Excel to retrieve the timestamp, instead of summing them/averaging them. I am sure that all my formulas is wrong and since the amount of data that I have to deal with is extensive, and there is no way that I am going to do this manually. Any help will be very helpful!
Last edited: