pivot table with text values

I need to create a pivot table to summarize data, some of which is text.
The text values return as zeroes - is there a way to display them as text?
There is only one value for the row/column combination
szy8xjAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Jerry PaladinoConnect With a Mentor Commented:
If you Load or Link your Excel data as a table in Microsoft Access called "Tbl_Temp", the following SQL Query will transform your data into the format you are requesting...
TRANSFORM Max(Tbl_Temp.VALUE) AS MaxOfVALUE
SELECT Tbl_Temp.SSN
FROM Tbl_Temp
GROUP BY Tbl_Temp.SSN
PIVOT Tbl_Temp.[DATA ELEMENT];

Open in new window

Query Output
0
 
SimonCommented:
Only if you put the text fields as row or column items, not into the data area.
0
 
szy8xjAuthor Commented:
Boo! Thanks anyway!
0
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.

 
Rob HensonFinance AnalystCommented:
You can replicate the Pivot Table function with Dbase formulas if you need to extract one set of data for a particular set of criteria, ie one result at a time rather than a table of results like a pivot.

All of the Dbase formulae have the following syntax:  =Dbase(Datatable,Field,Criteria)

DataTable - The datasource, same as you would use for the pivot
Field - The column header of the column from which you want the result
Criteria - A subset datatable with some or all of the same headers as source data and criteria specified under each.

The criteria table takes a bit of getting used to but is quite useful once you understand it.

Examples of DBASE functions are:
DSUM - add up a certain column (Field) based on criteria (similar to SUMPRODUCT or SUMIFS)
DMIN - return the minimum value from the specified column (Field) based on criteria
DMAX - return the maximum value from the specified column (Field) based on criteria
DGET - retrieve a single value from specified column (Field) that matches criteria, this can include text values.

Hope that helps.

Thanks,
Rob H
0
 
szy8xjAuthor Commented:
So Bob, here is what I have in a nutshell....

Excel file -

Record looks like this: SSN, DATA ELEMENT NAME, VALUE
Number of SSN's is variable, number of DATA ELEMENTS per SSN is variable and not all SSN have the same DATA ELEMENTS, and VALUES depend on DATA ELEMENT NAME and are text and numeric.

I need to change this to one row per SSN with data element names as column headings and values under those headings in order to copy it into a template file - MS Access form.

I thought a pivot table with SSN as row identifier, Data Element name as Column heading and value. The numerics work great using MAX as the field setting but the text values come back as zero.

Will the DBASE technique get me that?

Thanks!
0
 
Rob HensonFinance AnalystCommented:
Can you upload a sample?

Thanks
Rob
0
 
szy8xjAuthor Commented:
Here is a sample file - the actual file has several thousand rows and up to 25 data elements - Thanks!
TESTFILE.xlsx
0
 
szy8xjAuthor Commented:
Many thanks! This will be a big timesaver!
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.