Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to DISPLAY all data values in Pivot Table...not count, sum, etc
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to DISPLAY all data values in Pivot Table...not count, sum, et
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to DISPLAY all data values in Pivot Table...not count, sum, etc
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to DISPLAY all data values in Pivot Table...not count, sum, etc
i'll give it a try thanks -- spot1234 ------------------------------------------------------------------------ spot1234's Profile: http://www.excelforum.com/member.php...o&userid=36632 View this thread: http://www.excelforum.com/showthread...hreadid=563817 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
OLAP Pivot table - How to show items with no data ? | Excel Worksheet Functions | |||
Data Restructuring: Using Pivot Table w\o the SUM, COUNT etc. Function? | Excel Discussion (Misc queries) | |||
pivot table | Excel Discussion (Misc queries) | |||
Count unique values - Pivot Table | Charts and Charting in Excel | |||
Pivot table Data always showing up as "Count" | Excel Discussion (Misc queries) |