filter by date and action

  • Thread starter Thread starter Harry
  • Start date Start date
H

Harry

I have a table with three columns:

Name Date Action
Fred Today this
Fred Today this
Fred Today that
Wilma Today this


I want to sort by name and by date, but only if 'this' was done more
than once on the same day, and not if 'that' was done on the same
day,They have to perform this more than once, and they cannot have
performed that on the same day.
If not in excel, mssql maybe?

Thanks,
Harry
 
I have a table with three columns:

Name         Date           Action
Fred           Today         this
Fred           Today         this
Fred            Today        that
Wilma         Today         this

I want to sort by name and by date, but only if 'this' was done more
than once on the same day, and not if 'that' was done on the same
day,They have to perform this more than once, and they cannot have
performed that on the same day.
If not in excel, mssql maybe?

Thanks,
Harry
Click to expand...

You could add two columns for counters of This and That next to your
data.

=IF($C2="This",1,0)
=IF($C2="That",1,0)

Name Date Action This That
Fred 8/18/2011 This 1 0
Fred 8/18/2011 This 1 0
Fred 8/18/2011 That 0 1
Fred 8/18/2011 This 1 0
Wilma 8/18/2011 This 1 0

You could even tie the action check to the counter column header
incase you needed to add a 3rd action for compare

=IF($C2=D$1,1,0)
=IF($C2=E$1,1,0)

Then put it in a pivot table with the name and date and apply filter
to the Name of "Sum of This > 1" and apply a filter to the date of
"Sum of That = 0" This will give you the list of people that did This
more than once in a day but didn't do That on the same day.

Name Date Sum of This Sum of That
Fred 8/17/2011 3 0
Wilma 8/18/2011 2 0
 
Back
Top