Excel 2010 pivot table text values

I am trying to put some time card data into a format that makes sense to my BA.  After playing around for a while, I came up with a table that sort of resembles a pivot table, except that there is only one value for each column/row intersection.  I tried to put the data into a pivot table but can't get the actual value to display in the 'values' field - Excel wants to aggregate the information (I understand why it wants to do this).

How can I get it to display the actual text in the values field instead of a count or sum?  

I have found a few examples online but I don't use excel very often and these solutions include steps with which I'm not familiar, and I don't know VB.  

The left-hand column contains dates in the format of 2/4/2015, and the top row contains specific date/timestamps, such as 2/4/2015 10:04:44 PM.  The intersection is a shift that someone worked, such as '15:00 - 20:58'.  So, 15:00 - 20:58 is the shift that the employee worked on 2/4/2015, and the shift info was uploaded to the system (from the time clock) or modified on 2/4/2015 at 10:05:44 PM.
tancatOracle DeveloperAsked:
Who is Participating?
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

See attached. I plugged the data from your last file into the data sheet and used your first file as a guide what to aim for.

I created a pivot table and dragged "date" into the rows and "Create_date" into the columns. The pivot table will only show actual dates, but your desired output was showing rows for dates with no data. Therefore, on the Out sheet, I manually entered the first two dates and then used the fill handle to drag down as far as required. The fill handle will increment the values.

From the pivot table I copied the column labels and used paste special > Values and then paste special > Formats to get the dates into the Output table. This is a step that you would have to do every time more data is added to the data sheet: Refresh the pivot and copy/paste the column labels to the Output sheet.

On the output sheet, the formula in cell B2 is

=IFERROR(INDEX(data!$B$1:$B$100,MATCH(Output!$A2&Output!B$1,INDEX(data!$A$1:$A$100&data!$C$1:$C$100,0),)),"")

Copy across and down. Adust the range to reflect the number of rows in your data source, but don't use whole column references, since that can slow the workbook down.

cheers, teylyn
sample-output.xlsx
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

as you have found, pivot table values can only be numeric.

You could use the pivot table to create the top row and left hand column, then copy that and paste special as values (and again as formats) to another spreadsheet. Then use a lookup formula like Index/Match to retrieve the value for the intersections.

Details for that can be provided if you post a sample file that represents your data structure. A few dozen rows of dummy data will be sufficient.

cheers, teylyn
0
 
tancatOracle DeveloperAuthor Commented:
From the spreadsheet that I attached, Sheet 1 is the output that I'm looking for.  Sheet 2 is a sample of data that I have for one time card.  Date is the date that the work/shift occurred, and create_date is the date that the information was inserted/updated in the system.
sample-output.xlsx
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
tancatOracle DeveloperAuthor Commented:
Actually, attached is a better sample of data (with the null Shift values removed)
sample-data.xlsx
0
 
tancatOracle DeveloperAuthor Commented:
Sorry, Excel always has to reformat the dates; this file is correct.
0
 
tancatOracle DeveloperAuthor Commented:
0
 
tancatOracle DeveloperAuthor Commented:
Beautiful!  This is exactly what I was looking for.  (Sorry about the missing/extra dates (1/6/2015 & 1/7/2015) - the original raw data includes a row for each of those dates that has no shift times.)  

I can't believe how quickly you were able to do this; I have been struggling for several hours, plus I'm going on vacation tomorrow and have to hand this off to my boss for anything that I don't finish tonight - no pressure or anything!

Thank you!
0
 
tomfarrarCommented:
Consider importing the file into Access and using a cross-tab query to create your results.  See attached.
C--Users-E221037-Documents-Database14.ac
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.