Solved

Excel Pivot Table vs. Access Crosstab Query

Posted on 2000-04-11
5
454 Views
Last Modified: 2012-08-13
Is there a way to generate a Pivot table in Excel that does not summarize the data but rather lists the values (using the first value if there is more than one)?  I am working with text data and am simply trying to transpose it as with a crosstab query in Access.

Example data (before query):

KEYID           ID   Class
a1dog          a1   dog
a1cat           a1   cat
a2dog          a2   dog
a2cat           a2   cat
a3cat           a3   cat
a4mouse     a4   mouse

Example data (after query):

id              cat          dog           mouse
a1           a1cat      a1dog      
a2           a2cat      a2dog      
a3           a3cat            
a4                                          a4mouse


SQL:

TRANSFORM First(Table1.keyid) AS [The Value]
SELECT Table1.id, First(Table1.keyid) AS [Total Of keyid]
FROM Table1
GROUP BY Table1.id
PIVOT Table1.class;
0
Comment
Question by:amham1
  • 2
  • 2
5 Comments
 
LVL 4

Expert Comment

by:Noggy
ID: 2705168
Errm, not as far as I know. Excel is really geared for using numerical data in Pivot Tables. I think the best you can do is to use the Summarize By|Min or Max for the data field. But I have a feeling even that will return 0 for text data.

Maybe one of the others will know.

BTW Why are you doing this in Excel if you already have it in Access? Are you exporting the data from Access as a type of Report?
0
 

Author Comment

by:amham1
ID: 2705563
Noggy, thanks for responding.  It appears that min/max returns 0 as you mention.  Unfortunately, the data is not in Access, I only mentioned the crosstab query because I thought it might help explain what I'm aiming for.
0
 
LVL 22

Accepted Solution

by:
ture earned 50 total points
ID: 2706835
amham1,

Sorry - I haven't been able to accomplish this with a pivot table. I have found another aolution, but it is not at all as elegant as a pivot table would be.

Here is a step-by-step:

I assume that:
Your data is on Sheet1, in the range A1:C7, with headings on first row. The rest of Sheet1 is empty.

1. Let's create a list with the unique IDs:
1.1 Data - Filters - Advanced Filter
1.2 Select 'Copy to another location'
1.3 Click in 'List Range', delete whatever is in that text box and select column B, resulting in 'Sheet1!$B:$B'
1.4 Make sure that 'Criteria range' is blank
1.5 Click in 'Copy to', delete whatever is in that text box and select cell F1, resulting in Sheet1!$F$1'
1.6 Check 'Unique records only'
1.7 Click OK

2. Let's create a list with the unique Classes:
2.1 Data - Filters - Advanced Filter
2.2 Select 'Copy to another location'
2.3 Click in 'List Range', delete whatever is in that text box and select column C, resulting in 'Sheet1!$C:$C'
2.4 Make sure that 'Criteria range' is blank
2.5 Click in 'Copy to', delete whatever is in that text box and select cell E, resulting in Sheet1!$E1'
2.6 Check 'Unique records only'
2.7 Click 'OK'

3. Let's copy the classes and transform the Classes to a row
3.1 Select the range with classes (E1:E4)
3.2 Copy the range by pressing Ctrl+C
3.3 Select cell F1
3.4 Edit - Paste Special
3.5 Check 'Transpose'
3.6 Click 'OK'

4. Some cleaning up before we continue...
4.1 Select column E
4.2 Delete the entire column by pressing Ctrl + Minus (or Edit - Delete)
4.3 Select cell E1
4.4 Edit - Clear - All

5. Now we'll create a Criteria Range:
5.1 Enter 'ID' in cell J1
5.2 Enter 'Class' in cell K1

6. Time to make a table:
6.1 Enter this formula in cell E1:
    =DGET(A:C,"KEYID",J1:K2)
    (It will result in a #NUM! error)
6.2 Select the range E1:H5
6.3 Data - Table
6.4 Row input cell: $K$2
6.5 Column input cell: $J$2
6.6 Click 'OK'

We are done! The table looks kind of ugly because #VALUE! shows up wherever there is no match. You can change the formula in cell E1 to avoid this:

=IF(ISERROR(DGET(A:C,"KEYID",J1:K2)),"",DGET(A:C,"KEYID",J1:K2))

Ture Magnusson
Karlstad, Sweden
0
 
LVL 22

Expert Comment

by:ture
ID: 2708052
amham1,

Thanks for the points. I'm glad that I could help you and that you found my solution acceptable.

/Ture
0
 

Author Comment

by:amham1
ID: 2708109
Thanks ture!  Your instructions will give me exactly what I am looking for.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This video walks the viewer through the process of creating envelopes and labels, with multiple names and addresses. Navigate to the “Start Mail Merge” button in the Mailings tab: Follow the step-by-step process until asked to find the address doc…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

860 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