Results 1 to 9 of 9

Thread: Complicated search and return command

  1. #1

    Complicated search and return command



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    158
    Articles
    0
    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.
    Circumference of a circle = 2πrē



    ēthe circle's radius

  3. #3

    Attachment

    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!!
    Attached Files Attached Files

  4. #4
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    158
    Articles
    0
    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)
    Circumference of a circle = 2πrē



    ēthe circle's radius

  5. #5
    The IA Labels are never greater than 2. How do I create a pivot table?

  6. #6
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    158
    Articles
    0
    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.
    Circumference of a circle = 2πrē



    ēthe circle's radius

  7. #7
    Thanks so much!!!

  8. #8
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    158
    Articles
    0
    Is that resolved or do you need the file? (I could have sworn I had posted it)
    Last edited by JoePublic; 2012-03-22 at 08:39 AM.
    Circumference of a circle = 2πrē



    ēthe circle's radius

  9. #9


    Register for a FREE account, and/
    or Log in to avoid these ads!

    Quote Originally Posted by JoePublic View Post
    Is that resolved or do you need the file? (I could have sworn I had posted it)
    I got it sorted. Thanks again!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •