Can I do this pivot-table like thing?

Miscellaneous and Personal Stuff I Must Share
1

I’m not even sure if this is possible, calling all Microsoft experts! :slight_smile:

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.

2

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?

3 

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

4

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.

5

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

6

Never mind - I see you have more than one value for a date.

7

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. :wink:

8

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.

9

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.

10

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.

11

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

12

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!

13

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:



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.

14

I think my workaround would be quicker, depending how many countries there are.