Take the Log Contact in Column and data Field in Pivot Table
From: excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On Behalf Of Vincent Torralbes Sent: Saturday, July 16, 2011 9:11 PM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Calculating distinct events by ID 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 -- ---------------------------------------------------------------------------------- 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
