# Can I do this pivot-table like thing?
**URL:** https://boards.straightdope.com/t/can-i-do-this-pivot-table-like-thing/655831
**Category:** Miscellaneous and Personal Stuff I Must Share
**Created:** [April 16, 2013, 1:11pm UTC](https://boards.straightdope.com/t/can-i-do-this-pivot-table-like-thing/655831 "2013-04-16T13:11:52Z")
**Posts on this page:** 14
**Page:** 1
### Author:  [@sandra\_nz](https://boards.straightdope.com/u/sandra_nz)
#### Post date: [April 16, 2013, 1:11pm UTC](https://boards.straightdope.com/t/can-i-do-this-pivot-table-like-thing/655831/1 "2013-04-16T13:11:52Z")
I’m not even sure if this is possible, calling all Microsoft experts! 🙂
I have a SharePoint list with these headings: Date, Name, Location
So example content in the list…
1 Jan - John - UK
2 Jan - John - UK
3 Jan - John - US
4 Jan - John - US
5 Jan - John - US
1 Jan - Mary - UK
2 Jan - Mary - UK
3 Jan - Mary - UK
4 Jan - Mary - UK
5 Jan - Mary - UK
I can easily export this SharePoint list to Excel.
I would like to ‘magically’ make a ‘pivot table’ that then goes:
Name - 1 Jan - 2 Jan - 3 Jan - 4 Jan - 5 Jan
John - UK - UK - US - US - US
Mary - UK - UK - UK - UK - UK
So instead of the standard pivot table format where the table content is a formula (count, sum, etc), I just want it to display the value.
Tools I have available: SharePoint 2010, Office 2007, Access 2007
I could do this manually each week, but if I had an automated way of doing it, that would save me a lot of time.
---
### Author:  [@Doctor\_Jackson](https://boards.straightdope.com/u/Doctor_Jackson)
#### Post date: [April 16, 2013, 1:36pm UTC](https://boards.straightdope.com/t/can-i-do-this-pivot-table-like-thing/655831/2 "2013-04-16T13:36:05Z")
The best I can come up with (unless you change the location to a number, like UK=1, US=2) is to set your pivot table up as follows:
DATE field as column label
NAME and PLACE fields as row labels
COUNT of NAME field as value
Right click on the table and remove “Subtotal NAME” , you get a table that looks like:
Count of Name Date
Name Place 1-Jan 2-Jan 3-Jan 4-Jan 5-Jan Grand Total
John UK 1 1 2
US 1 1 1 3
Mary UK 1 1 1 1 1 5
Grand Total 2 2 2 2 2 10
Close enough?
---
### Author:  [@Doctor\_Jackson](https://boards.straightdope.com/u/Doctor_Jackson)
#### Post date: [April 16, 2013, 1:38pm UTC](https://boards.straightdope.com/t/can-i-do-this-pivot-table-like-thing/655831/3 "2013-04-16T13:38:17Z")
```auto
Count of Name Date
Name Place 1-Jan 2-Jan 3-Jan 4-Jan 5-Jan Grand Total
John UK 1 1 2
US 1 1 1 3
Mary UK 1 1 1 1 1 5
Grand Total 2 2 2 2 2 10
```
Forgot the code tags
---
### Author:  [@tim-n-va](https://boards.straightdope.com/u/tim-n-va)
#### Post date: [April 16, 2013, 2:10pm UTC](https://boards.straightdope.com/t/can-i-do-this-pivot-table-like-thing/655831/4 "2013-04-16T14:10:12Z")
A little tedious to set up but If each person is only in once location per day, make up a location to number trnaslation, pivot that with a sum and then translate back.
---
### Author:  [@StGermain](https://boards.straightdope.com/u/StGermain)
#### Post date: [April 16, 2013, 2:46pm UTC](https://boards.straightdope.com/t/can-i-do-this-pivot-table-like-thing/655831/5 "2013-04-16T14:46:11Z")
If you copy, then paste/transpose, your result will look like this:
Date 1-Jan 2-Jan 3-Jan 4-Jan 5-Jan 1-Jan 2-Jan 3-Jan 4-Jan 5-Jan
Name John John John John John Mary Mary Mary Mary Mary
Country UK UK US US US UK UK UK UK UK
It all lines up in Excel, of course.
StG
---
### Author:  [@StGermain](https://boards.straightdope.com/u/StGermain)
#### Post date: [April 16, 2013, 2:57pm UTC](https://boards.straightdope.com/t/can-i-do-this-pivot-table-like-thing/655831/6 "2013-04-16T14:57:00Z")
Never mind - I see you have more than one value for a date.
---
### Author:  [@sandra\_nz](https://boards.straightdope.com/u/sandra_nz)
#### Post date: [April 16, 2013, 3:18pm UTC](https://boards.straightdope.com/t/can-i-do-this-pivot-table-like-thing/655831/7 "2013-04-16T15:18:47Z")
Thanks for your inputs, unfortunately I really am looking for a solution where it will look like my crappy ‘pivot table’ and it seems like I can’t do that. I think I will have to keep doing it manually. Just wanted to work smarter not harder. 😉
---
### Author:  [@Blunt](https://boards.straightdope.com/u/Blunt)
#### Post date: [April 16, 2013, 3:47pm UTC](https://boards.straightdope.com/t/can-i-do-this-pivot-table-like-thing/655831/8 "2013-04-16T15:47:00Z")
Could you do this with vlookups? Create a column in your data that is a concatenation of date and name, and then build a table looking that up? This assumes each person can only be in one place on any given date.
---
### Author:  [@Doctor\_Jackson](https://boards.straightdope.com/u/Doctor_Jackson)
#### Post date: [April 16, 2013, 6:53pm UTC](https://boards.straightdope.com/t/can-i-do-this-pivot-table-like-thing/655831/9 "2013-04-16T18:53:55Z")
Unfortunately, a pivot table will not return a text value, it must be numeric. You would probably need to use a query table and cross tab the result. Of course, that requires coding.
---
### Author:  [@Scougs](https://boards.straightdope.com/u/Scougs)
#### Post date: [April 16, 2013, 7:43pm UTC](https://boards.straightdope.com/t/can-i-do-this-pivot-table-like-thing/655831/10 "2013-04-16T19:43:41Z")
How many locations are there? If there are only two or three, it is possible using countifs, but it is a little clunky.
In your second sheet, the formula in B2 to reference the first sheet would be something like this:
=if(countifs(Sheet1!$A:$A,B$1,Sheet1!$B:$B,$A1,Sheet1!$C:$C,“UK”)=1,“UK”,“US”)
You could nest it for more countries, or maybe a wiser doper can take this concept and build on it.
---
### Author:  [@JerrySTL](https://boards.straightdope.com/u/JerrySTL)
#### Post date: [April 16, 2013, 8:32pm UTC](https://boards.straightdope.com/t/can-i-do-this-pivot-table-like-thing/655831/11 "2013-04-16T20:32:03Z")
You could do it with Access. Below is the SQL for a crosstab query. Change DateNameLocation to the actual table name.
TRANSFORM First(DateNameLocation.Location) AS FirstOfLocation
SELECT DateNameLocation.Name
FROM DateNameLocation
GROUP BY DateNameLocation.Name
PIVOT DateNameLocation.Date;
A few problems.
1. The Date field needs to be Date/Time data type or you will have sorting problems as 11 Jan will be right after 1 Jan if it’s a text field. 2 Jan won’t show up until after all the teens.
2. ‘Date’ and ‘Name’ are reserved words in Access SQL. Using them as field or table names can cause problems. TheDate would not. For more about Reserved Words see:
[List of reserved words in Access - Office | Microsoft Learn](http://support.microsoft.com/kb/286335/)
---
### Author:  [@sandra\_nz](https://boards.straightdope.com/u/sandra_nz)
#### Post date: [April 16, 2013, 8:47pm UTC](https://boards.straightdope.com/t/can-i-do-this-pivot-table-like-thing/655831/12 "2013-04-16T20:47:37Z")
> [@Doctor\_Jackson](#):
>
> Unfortunately, a pivot table will not return a text value, it must be numeric. You would probably need to use a query table and cross tab the result. Of course, that requires coding.
Yeah, I think that’s the root cause of the problem. So then it’s a case of finding workarounds, and my conclusion is that it’s quicker to do it manually.
Thanks all for your inputs!
---
### Author:  [@Munch](https://boards.straightdope.com/u/Munch)
#### Post date: [April 16, 2013, 9:12pm UTC](https://boards.straightdope.com/t/can-i-do-this-pivot-table-like-thing/655831/13 "2013-04-16T21:12:20Z")
You can really cheat around it by inserting some concatenated fields.
To the left of your first chart, insert a column, and =concatenate(b2&" "&c2). Drag that down.
Set up your second chart on a different sheet, and insert columns between all the dates:
```auto
NAME 1/1Dummy 1-Jan
John =concatenate($a2&" "&$c1) =vlookup(b2, Sheet1!$a$2:$d$11), 4, False)
Mary =concatenate($a3&" "&$c1) =vlookup(b3, Sheet1!$a$2:$d$11), 4, False) repeat for your other dates
```
That’s REAL ugly, even after hiding the dummy columns. It’d be easier if you could just use the concatenate formula in the vlookup formula, but you can’t.
---
### Author:  [@Scougs](https://boards.straightdope.com/u/Scougs)
#### Post date: [April 16, 2013, 9:38pm UTC](https://boards.straightdope.com/t/can-i-do-this-pivot-table-like-thing/655831/14 "2013-04-16T21:38:59Z")
> [@sandra\_nz](#):
>
> Yeah, I think that’s the root cause of the problem. So then it’s a case of finding workarounds, and my conclusion is that it’s quicker to do it manually.
>
> Thanks all for your inputs!
I think my workaround would be quicker, depending how many countries there are.