?
Solved

Excel 2010 pivot table text values

Posted on 2015-02-04
8
Medium Priority
?
682 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

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.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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 a scrolling table in Microsoft Excel using the INDEX function.

777 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