• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Pivot table showing unique first and last names

S

suell

Member
I am using excel 2010 working with a pivot table. I have first and last names and their respective dates of birth together with their sales. two sales people have the same first and last name and different dates of birth and I want to see them on separate lines but when I put the last name, first name and date of birth in the row area of the Pivot table the two sales people who have the same first and last name is grouped into one name and their dates of birth shown separately. I would like to see the duplicate names not grouped. I have used the "repeat all item labels" which is good but I would prefer not to. I know the first field in the pivot row is unique names and grouped but can you get around this? Sorry cannot upload this data and my pivot table.

[pre]
Code: 
FirstName  LastName  DateOfBirth  SalesPersonID	SalesCode   SalesDate
brian	    black	4/04/1933	444	1234	   5/02/2013
brian	    black	4/04/1933	444	5678	   3/02/2013
barry	    brown	1/04/2023	222	9012	   3/02/2013
barry	    brown	1/04/2023	222	1234	   1/02/2013
pen	    purple	12/08/1961	111	5678	   12/02/2013
pen	    purple	12/02/1963	555	9012	   12/02/2013
yentl	    yellow	4/05/1945	333	1234	   5/05/2013
yentl	    yellow	4/05/1945	333	5678	   2/02/2013
brian	    black	4/04/1933	444	5678	   4/03/2013
barry	    brown	1/04/2023	222	9012	   7/05/2013
[/pre]
 
Good day suell


post edited with back ticks


Why not turn your data into a table and filter there
 
One easy option is to create an extra column in original data like this,


=firstname & " " & last name & " ("&date_of_birth&")"


and use this column in pivot table.
 
Good day suell


If you put back ticks before and after any code it will keep the formatting.

If you need to use a PT then do as Chandoo has suggested.
 
Hi ,


From the data you have posted , the SalesPersonID seems to be defining a unique sales person ; can you not use this field instead of using the First Name , Last Name and the Date of Birth to identify a unique sales person ?


Narayan
 
You must log in or register to reply here.
Back
Top