Excel Pivot Table vs. Access Crosstab Query

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;
amham1Asked:
Who is Participating?
 
tureCommented:
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
 
NoggyCommented:
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
 
amham1Author Commented:
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
 
tureCommented:
amham1,

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

/Ture
0
 
amham1Author Commented:
Thanks ture!  Your instructions will give me exactly what I am looking for.
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.