?
Solved

Excel 2010 pivot table text values

Posted on 2015-02-04
8
Medium Priority
?
764 Views
Last Modified: 2015-02-04
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.
0
Comment
Question by:tancat
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
8 Comments
 
LVL 50
ID: 40589686
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
 

Author Comment

by:tancat
ID: 40589759
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
 

Author Comment

by:tancat
ID: 40589777
Actually, attached is a better sample of data (with the null Shift values removed)
sample-data.xlsx
0
Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

 

Author Comment

by:tancat
ID: 40589782
Sorry, Excel always has to reformat the dates; this file is correct.
0
 

Author Comment

by:tancat
ID: 40589783
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 40589852
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
 

Author Comment

by:tancat
ID: 40589914
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
 
LVL 7

Expert Comment

by:tomfarrar
ID: 40589942
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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question