Solved

Excel Pivot Table vs. Access Crosstab Query

Posted on 2000-04-11
5
448 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Problem: You created a new custom form in Outlook for your contacts (added fields, deleted fields, changed the layout of fields, whatever) and made it the default form for contacts. The good news is that all new contacts will utilize the new form. T…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 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

21 Experts available now in Live!

Get 1:1 Help Now