How to DISPLAY all data values in Pivot Table...not count, sum, etc

Don't know a way to do that directly, but here's a workaround (should be
fairly easy to put in a macro):

1. Assign a number for each different application. Make a cross-reference
table with this information somewhere.

2. Replace each application name with its number in your source data. Format
the cell as a number, not text.

3. Create the pivot table, summing the Application field. Nothing actually
gets added together, because each combination of
Department/Region/Application is unique.

4. Copy the pivot table & paste special in place as values.

5. Replace the numbers in the data area with the corresponding application
name.

Hope this helps,

Hutch

"spot1234" wrote:

>
> I have a list APPLICATIONS used by our different DEPARTMENTS by REGION
>
> eg
> DEPARTMENT REGION APPLICATION
> Accounting Europe MS Word
> Accounting Asia Word Perfect
> Marketing NorthAm Word Perfect
>
>
> What I'd like to do is create a table that shows DEPARTMENTS in Rows
> and REGIONS as columns. Then in the data area i just want to display
> all the APPLICATIONS (ie no sum, count, avg....just list all the
> systems that match the Department/Region combination
>
> Any Ideas? Are there any addins that could help?
>
> eg
> NORTH AMERICA EUROPE ASIA
> <etc...>
> Accounting | MS word MS word word perfect
> | outlook ccmail
> outlook
> ----------------------------------------------------------------------
> Marketing | Word Perfect Word Perfect Word
> Perfect
>
> ---------------------------------------------------------------------
> Development | Excel Lotus Lotus
> | Outlook Outlook
> ccmail
> |
> -----------------------------------------------------------------------
>
> <etc..> |
>
>
> --
> spot1234
> ------------------------------------------------------------------------
> spot1234's Profile: http://www.excelforum.com/member.php...o&userid=36632
> View this thread: http://www.excelforum.com/showthread...hreadid=563817
>
>

This macro seems to work ok for me:

Option Explicit
Sub testme01()

Dim curWks As Worksheet
Dim newWks As Worksheet
Dim myRng As Range
Dim LastRow As Long
Dim LastCol As Long
Dim iCol As Long
Dim myInputRng As Range
Dim myCell As Range

Application.ScreenUpdating = False

Set curWks = Worksheets("sheet1")
Set newWks = Worksheets.Add

With curWks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
Set myInputRng = .Range("a1", .Cells(LastRow, LastCol))
Application.StatusBar = "determining Region headers"
With myInputRng.Columns(2)
.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=newWks.Range("A1"), Unique:=True
End With
End With

With newWks
With .Range("a:a")
.Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
Header:=xlYes
End With
Set myRng = .Range("a2", .Cells(.Rows.Count, "a").End(xlUp))
If myRng.Rows.Count > 250 Then
MsgBox "too many Training classes to fit on the worksheet!"
GoTo ExitNow:
End If
myRng.Copy
.Range("b1").PasteSpecial Transpose:=True
.Range("a:a").ClearContents
End With

With curWks
Application.StatusBar = "Copying departments"
With .Range("a:a")
.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=newWks.Range("a1"), Unique:=True
.Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
Header:=xlYes
End With
End With

With newWks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

Set myRng = .Range("B2", .Cells(2, LastCol))

Application.StatusBar = "Populating lots of formulas"
For Each myCell In myRng.Cells
With myCell
.FormulaArray _
= "=INDEX(" & myInputRng.Columns(3).Address _
(external:=True, ReferenceStyle:=xlR1C1) & "," _
& "match(1,(" & myInputRng.Columns(1).Address _
(external:=True, ReferenceStyle:=xlR1C1) _
& "=rc1)*(" _
& myInputRng.Columns(2).Address _
(external:=True, ReferenceStyle:=xlR1C1) _
& "=r1c),0))"
End With
Next myCell

Application.StatusBar = "Filling the formulas down"
myRng.AutoFill _
Destination:=myRng.Resize(LastRow - 1)

Application.StatusBar = "Cleaning up"
With myRng.Resize(LastRow - 1)
.Value = .Value
.Replace what:="#n/a", replacement:="", lookat:=xlWhole, _
MatchCase:=False
End With

Application.Goto .Range("a1"), scroll:=True
.Range("b2").Select
ActiveWindow.FreezePanes = True

With .UsedRange
.Columns.AutoFit
End With
End With

ExitNow:
With Application
.ScreenUpdating = True
.StatusBar = False
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


spot1234 wrote:
>
> I have a list APPLICATIONS used by our different DEPARTMENTS by REGION
>
> eg
> DEPARTMENT REGION APPLICATION
> Accounting Europe MS Word
> Accounting Asia Word Perfect
> Marketing NorthAm Word Perfect
>
> What I'd like to do is create a table that shows DEPARTMENTS in Rows
> and REGIONS as columns. Then in the data area i just want to display
> all the APPLICATIONS (ie no sum, count, avg....just list all the
> systems that match the Department/Region combination
>
> Any Ideas? Are there any addins that could help?
>
> eg
> NORTH AMERICA EUROPE ASIA
> <etc...>
> Accounting | MS word MS word word perfect
> | outlook ccmail
> outlook
> ----------------------------------------------------------------------
> Marketing | Word Perfect Word Perfect Word
> Perfect
>
> ---------------------------------------------------------------------
> Development | Excel Lotus Lotus
> | Outlook Outlook
> ccmail
> |
> -----------------------------------------------------------------------
>
> <etc..> |
>
> --
> spot1234
> ------------------------------------------------------------------------
> spot1234's Profile: http://www.excelforum.com/member.php...o&userid=36632
> View this thread: http://www.excelforum.com/showthread...hreadid=563817

--

Dave Peterson

i'll give it a try thanks