I have a table where I maintain a log of every contact event I make with my
customers. In a Pivot table, I'm trying to return and calculate the first
contact event made by unique ID. When I pivot by ID and dump in "Log
Contact", it sums up all the contacts. If I use the Max function, then it
doesn't aggregate the first contact events. Can anyone offer any
suggestions?
Ideally, I would have another column that would say, "First Contact" and
return a 1...for example, ID 12345 would be 6/13/2011 9:53; ID 33332 would
be 6/2/2011 12:22. The other rows for that ID would be zero. My second
table is my preferred output.
ANY Help is much appreciated! Thanks!
ID Log Date Log Contact 12345 6/13/2011 9:39 0 12345 6/13/2011 9:53 1
12345 6/17/2011 13:48 0 12345 7/1/2011 16:20 1 12345 7/11/2011 15:00 0
12345 7/15/2011
9:20 0 33332 5/31/2011 11:08 0 33332 6/2/2011 12:22 1 33332 6/2/2011 18:40 1
33332 6/3/2011 9:26 1 33332 6/20/2011 18:00 0 33332 7/6/2011 10:15 0
33332 7/11/2011
12:15 0 33332 7/14/2011 10:51 1 33332 7/15/2011 9:20 0 43213 5/24/2011 13:46
0 43213 5/24/2011 18:16 1 43213 5/25/2011 16:48 1 43213 5/27/2011 15:24 0
43213 6/8/2011 16:41 1 43213 6/10/2011 14:00 0
Example Output.....
ID Log Date Log Contact Distinct Contacts 12345 6/13/2011 9:39 0 0
12345 6/13/2011 9:53 1 1 12345 6/17/2011 13:48 0 0 12345 7/1/2011 16:20 1 0
12345 7/11/2011 15:00 0 0 12345 7/15/2011 9:20 0 0 33332 5/31/2011 11:08 0 0
33332 6/2/2011 12:22 1 1 33332 6/2/2011 18:40 1 0 33332 6/3/2011 9:26 1 0
33332 6/20/2011 18:00 0 0 33332 7/6/2011 10:15 0 0 33332 7/11/2011 12:15 0 0
33332 7/14/2011 10:51 1 0 33332 7/15/2011 9:20 0 0 43213 5/24/2011 13:46 0 0
43213 5/24/2011 18:16 1 1 43213 5/25/2011 16:48 1 0 43213 5/27/2011 15:24 0
0 43213 6/8/2011 16:41 1 0 43213 6/10/2011 14:00 0 0
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
