Solved

Excel 2010 pivot table text values

Posted on 2015-02-04
8
394 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
  • 5
  • 2
8 Comments
 
LVL 50

Expert Comment

by:teylyn
Comment Utility
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
Comment Utility
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
Comment Utility
Actually, attached is a better sample of data (with the null Shift values removed)
sample-data.xlsx
0
 

Author Comment

by:tancat
Comment Utility
Sorry, Excel always has to reformat the dates; this file is correct.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:tancat
Comment Utility
0
 
LVL 50

Accepted Solution

by:
teylyn earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

762 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now