?
Solved

Excel Pivot Table vs. Access Crosstab Query

Posted on 2000-04-11
5
Medium Priority
?
466 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
[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
  • 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 200 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: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
New style of hardware planning for Microsoft Exchange server.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

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