PDA

View Full Version : Complicated search and return command



emmapote
2012-03-19, 04:35 AM
I am working with the following types of datasets:
TRIAL_LABEL IA_LABEL IA_FIRST_FIXATION_TIME
Trial: 1 bouche 932
Trial: 1 nez 376
Trial: 1 nez2 568
Trial: 1 oeil 2476
Trial: 1 oeil2 1312
Trial: 1 sourcils .
Trial: 1 sourcils2 .
Trial: 1 centresourcils 244
Trial: 1 joues 732
Trial: 1 joues2 .
Trial: 1 menton .
Trial: 2 bouche 856
Trial: 2 nez 684
Trial: 2 nez2 .
Trial: 2 oeil 448
Trial: 2 oeil2 1504
Trial: 2 sourcils .
Trial: 2 sourcils2 6116
Trial: 2 centresourcils 264
Trial: 2 joues .
Trial: 2 joues2 5404
Trial: 2 menton .
Trial: 3 bouche .
Trial: 3 nez 404
Trial: 3 nez2 248
Trial: 3 oeil 4532
Trial: 3 oeil2 3644
Trial: 3 sourcils .
Trial: 3 sourcils2 .
Trial: 3 centresourcils .
Trial: 3 joues .
Trial: 3 joues2 1084
Trial: 3 menton .
Trial: 4 bouche 1272
Trial: 4 nez 4484
Trial: 4 nez2 2920
Trial: 4 oeil 396
Trial: 4 oeil2 728
Trial: 4 sourcils 3948
Trial: 4 sourcils2 4220
Trial: 4 centresourcils 240
Trial: 4 joues 4952
Trial: 4 joues2 .
Trial: 4 menton .
Trial: 5 bouche 964
Trial: 5 nez 544
Trial: 5 nez2 268
Trial: 5 oeil 4180
Trial: 5 oeil2 1940
Trial: 5 sourcils 6444
Trial: 5 sourcils2 5476
Trial: 5 centresourcils .
Trial: 5 joues .
Trial: 5 joues2 .
Trial: 5 menton .
Trial: 6 bouche 260
Trial: 6 nez 584
Trial: 6 nez2 .
Trial: 6 oeil .
Trial: 6 oeil2 .
Trial: 6 sourcils .
Trial: 6 sourcils2 .
Trial: 6 centresourcils .
Trial: 6 joues 1308
Trial: 6 joues2 1024
Trial: 6 menton .
Trial: 7 bouche 1212
Trial: 7 nez 5532
Trial: 7 nez2 .
Trial: 7 oeil 2068
Trial: 7 oeil2 1588
Trial: 7 sourcils .
Trial: 7 sourcils2 8188
Trial: 7 centresourcils 7004
Trial: 7 joues .

There are 96 trials in total and each are labelled (IA Label) as shown above. I need to create a calculation sheet to input these data files into so that excel will organize the data by selecting the smallest fixation time for each part of the face (bouche, nez, oeil, sourcils, joues and menton) for each trial number (1 through 96). The final dataset needs to have the trials 1 to 96 in a column with the lowest value for each face part in the coinciding rows. I am just learning to program in Excel and this is far to complicated for me to figure out at this point, so any input would be greatly appreciated!!!
Thanks in advance!
Emma

JoePublic
2012-03-20, 02:30 PM
It would be helpful to see a sample workbook with some data in it - it appears as though a pivot table might be a simple solution to this.

emmapote
2012-03-20, 02:53 PM
Here is a full version of the datasets I will be working with. I have included in the workbook an example of how I need the data to come out at the end of it all! I needs to select the lowest value for each of the zones per trial in the dataset. Thanks so much!!

JoePublic
2012-03-20, 03:01 PM
Do the numbers in the IA_LABELs ever go past 2 - i.e. could there be nez, nez2, nez3, nez4 and so on, or is it just the labels you have included in that sheet? (if the latter, then a simple grouping in the pivot table will work nicely; if the former, then an additional formula column will be required)

emmapote
2012-03-20, 04:07 PM
The IA Labels are never greater than 2. How do I create a pivot table?

JoePublic
2012-03-20, 04:53 PM
Select the data. Then Insert tab, and select pivot table. Choose where you want the pivot table, then press Finish. You will then need to drag the Trial to the Row Fields area, the IA_LABEL to the Column fields area, and the time to the values/data area. Then right-click a data cell in the actual pivot table, choose field settings and change the function to Min rather than Sum or Count.
For the column groupings, you need to rearrange the fields in the column header area (you can just drag them around) so that any you want in a group (e.g. nez and nez2) are next to each other, then select them, right-click and choose Group. This will create a new field with new items starting at Group 1 but you can simply rename them.
Alternatively, if you use a couple of formula columns (easier if you want to sort the Trials by number), you won't need to do manual groupings. I'll post a demo file in a minute.

emmapote
2012-03-22, 03:31 AM
Thanks so much!!!

JoePublic
2012-03-22, 09:37 AM
Is that resolved or do you need the file? (I could have sworn I had posted it)

emmapote
2012-03-23, 04:59 AM
Is that resolved or do you need the file? (I could have sworn I had posted it)

I got it sorted. Thanks again!!