Solved

Excel 2010 pivot table text values

Posted on 2015-02-04
8
529 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

Expert Comment

by:Ingeborg Hawighorst
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 earned 500 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
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…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

730 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