1. ## Advanced Logic Problem

Hello gurus, I have a large event dataset for individuals. Below I'm attaching a sample of 10 individuals to give you an idea. Individuals belong to a group and a group consists of one or more individuals. Each individual has one or more event. Each event has one or more episodes within it. Events and episodes have start and end dates. Episodes also have location information. I have to figure out the following: for all individuals that belong to a group that consists of 2 or more individuals, proportion of individual's time spent with any other group member at the same location to the total event duration. In other words, I need to know whether individuals that belong to a group experienced their episodes together (i.e. at the same location on same or overlapping dates) and if yes, the portion of their total event time they spent together. For groups consisting of three or more individuals, I need to figure out the proportion for different combinations of individuals belonging to the same group.
Can this be done using Excel formulas like nested IFs, AND, OR, COUNTIF, etc? Or would I need to learn VBA to be able to do something like this?

2.  group_id indiv_id event_id #indiv_per_group dob sex event_type event_starts event_ends(red_if_still_ongoing_when_data_pulled) event_end_reason event_length_in_days event_score episode_location1 episode_location2 episode_location3 episode_location4 episode_location5 episode1_starts episode1_ends episode2_starts episode2_ends episode3_starts episode3_ends episode4_starts episode4_ends episode5_starts episode5_ends 1 1 1 3 1/1/1980 Female 2 4/26/2012 5/24/2013 X 393 5 Garden X Garden A Garden M Garden H 4/26/2012 5/23/2012 5/23/2012 5/30/2012 6/3/2012 7/11/2012 7/11/2012 5/24/2013 1 1 2 3 1/1/1980 Female 3 10/21/2013 11/18/2013 Z 28 8 Garden Y 10/21/2013 11/18/2013 1 2 3 3 1/3/1985 Male 3 3/13/2012 3/11/2013 Y 363 Garden Z Garden B Garden N Garden J Garden P 3/13/2012 3/14/2012 5/2/2012 5/11/2012 8/24/2012 9/1/2012 10/18/2012 10/19/2012 10/29/2012 11/5/2012 1 3 4 3 1/4/1989 Female 2 3/29/2012 5/13/2013 Z 410 10 Garden Z Garden C Garden N Garden N 3/29/2012 3/30/2012 5/4/2012 3/11/2013 3/11/2013 3/28/2013 3/28/2013 5/13/2013 2 4 5 2 2/24/1988 Female 1 6/8/2012 1/30/2013 A 236 7 Garden R 6/8/2012 1/30/2013 2 5 6 2 7/20/1985 Male 1 6/8/2012 2/4/2013 B 241 3 Garden R 6/8/2012 2/4/2013 3 6 7 1 9/1/1974 Female 3 1/12/2012 1/13/2012 X 1 3 Garden Y 1/12/2012 1/13/2012 4 7 8 2 4/27/1976 Male 4 8/11/2012 3/4/2013 X 205 4 Garden X Garden J Garden S 8/14/2012 8/24/2012 9/24/2012 10/25/2012 10/25/2012 3/4/2013 4 8 9 2 4/5/1997 Male 2 10/5/2012 10/19/2012 B 14 2 Garden Y 10/5/2012 10/19/2012 4 8 18 2 4/5/1997 Male 3 12/6/2012 12/7/2012 B 1 6 Garden Y 12/6/2012 12/7/2012 4 8 51 2 4/5/1997 Male 4 10/9/2013 10/11/2013 B 2 7 Garden G 10/9/2013 10/11/2013 18 33 92 2 8/6/1978 Male 1 7/29/2012 3/7/2014 586 8 Garden G Garden C 7/30/2012 8/7/2012 8/7/2012 3/18/2014 18 34 93 2 11/22/1989 Male 1 7/29/2012 3/7/2014 586 Garden G Garden C 7/30/2012 8/7/2012 8/7/2012 3/18/2014

3. Sounds like you need a pivot table.
Can you attach a sample file, cut and paste is not formatting

4. for some reason the attachment function is not working. I put the file on my box.com account. here is the link. Thanks! https://pitt.box.com/advancedlogicsample

5. Originally Posted by ladygaga
for some reason the attachment function is not working. I put the file on my box.com account. here is the link. Thanks! https://pitt.box.com/advancedlogicsample
To attach a file, click 'Reply' and a box opens for you to type your post. One of the exit options shouul be 'Go Advanced'. Click this, and look for the paperclip attach option. This will activate 'Manage Attachments', allowing you to locate and upload the file you want to attach. When this is done, click 'Submit Reply'.

HTH

6. Originally Posted by WizzardOfOz
Sounds like you need a pivot table.
Can you attach a sample file, cut and paste is not formatting
here you go

7. Explain your expected results on your file, still confused

Sorry

8. your sample set does not contain any matches, may be best to upload everything for group 1

Also your location if Event1, Episode 1 is in garden A and episode 5 is also in garden A this does not mean the same episode.

Rizky, on further analysis its a database query
group by group_id, event_id, episode_id
sum on episode time
identify indiv_id

#### Posting Permissions

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