Pivot Table To List Multiple Source Columns In Rows?
Jul 4, 2014
I have a huge data set with survey data (sample attached: sample.xlsx). Column headings: survey questions Row headings: respondent identifiers (names) Row data: answers to questions (numeric values, only five answer options 1,2,3,4 or 5, no blanks)
I need to build that kind of pivot table:
Rows - list of particular questions (i.e. questions no. 3, 7 and 12) Columns - list of all diffrent answer entries (eventually, it will be 1,2,3,4 and 5) - it could also be questions in columns and answers in row (no difference) Values - count answer entries (i.e. how many answers "5" are on quesiton 3)
------
UPDATE: explanation added.
The reason I need pivot tables: i'll have to cross analyze multiple sets of questions. I'll have to do such cross-analysis 100+ times, so, writing a formula for each time does not quite work...
View 6 Replies
ADVERTISEMENT
May 12, 2007
I am trying to round a number to the next half penny. The mround function would seem to work but does not work for fractions.
View 2 Replies
View Related
Jun 19, 2008
I'm trying to create a Pivot table with various columns comparing information of executives from various Private Equity Firms. Some of the additional columns are Industry Specialty, Harvard Grad Year, My companies contact, etc . . .
The goal is to allow me to then query this information so if I wanted to see all Bain Capital employees who graduated with an MBA in 1990 and their specialty.
My question comes in trying to set up my source list for the data under Industry Specialty and my companies Contact. Is there a way I can set a value for these cells and mark them as an "AND" in the source if there are multiple contacts or industries that one of the Private equity executives is linked too, that would then allow them to be displayed as so when I create the pivot table?
Attached is .zip for further clarification.
View 9 Replies
View Related
Jul 17, 2009
I wish to hide some columns that contain data used to update a pivot table in another worksheet. What I want to know is though, will this affect the pivot table? I think that graphs in excel will normally ignore data in hidden cells, so I was wondering if it was the same with pivot tables.
View 2 Replies
View Related
Apr 29, 2008
My worksheet has a report name in cell A1, a brand name in B2, price name in C2, and color in D2. Row 3 has a table header in cell range A3:E3 followed by rows of table contents at the end of which is a row of empty cells. This sequence repeats itself for a new product: a row with the report name in column A, followed by a row with a new brand name, price and color in the same columns as before followed by the same table header and table contents. The number of products (with the previously mentioned details and table) vary so I would like to write a macro that is not dependent on a set number of products. Each table varies in the number of rows but never contains empty cells and always ends with a row of empty cells. I want to delete all rows with the report name and have all products in one table with the brand, price and color added to the table header and contents.
View 2 Replies
View Related
Mar 9, 2014
I am trying to generate several pivot tables from one data source table. I have successfully created my first pivot table (A date field, and a water storage facility level reading) and subsequently a graph from this. I have worked out that I need to group my dates as I am supplied a daily reading, but only need monthly average. All worked great.
Now i need to create more pivot tables and graphs. The next one I want is to create one grouped by years. But when I create this new pivot table and change the grouping of the date field to yearly, it also changes the grouping on my first pivot table, which is undoing my work.How to tell excel that these pivots are independent, and I don't want them changing in unison? See screen grab of my source data and where I am up to...
Microsoft Excel - 401027_0100.00_0221.00.csv_2014-03-10_11-41-35.jpg
View 1 Replies
View Related
Feb 26, 2014
I have 5 pivot tables on 5sheets, all looking at the same source data in sheet 6
On the source data there is a filter on the headers, if you change the filter, is it possible for all 5 pivot tables to update according to the filter?
My starting point is the below, but there probably is a better way but i would want the sourcedata to equal visible rows on the source data sheet headers run A:K and up to row 10000 .
View 2 Replies
View Related
Jan 4, 2010
I'm trying to write a macro that will create a pivot table, and am getting an Error code 1004: Cannot Open Pivot Table Source File "Sheetname". My code is below. I've tried to note what each section does, and it all seems to work well except for the Pivot Table creation.
View 14 Replies
View Related
Oct 13, 2009
attached Pivot Table. There is a section, which I've marked, that shows how I want my Pivot Table report to look. I've had Excel 2007 kick me out a number of times when I've tried to filter and shift rows and columns to accomplish this and areas are frequently grayed out. Can someone please assist?
I'm attaching a file rather than going into a lot of detail here because I think a visual is easier here. The raw data, my attempt at the Pivot Table and my desired outcome all show on the same tab.
View 5 Replies
View Related
Jun 28, 2007
How do I create a pivot table with multiple data columns? My fixed asset software will not let me create a report to list multiple months/ quarters. I've created a spreadsheet that I can dump each month into, but I'd like to be able to sort by G/L acct or Department. When I try to create a pivot table, I can't get it to accept each month as a data field. Ive attached a copy of the spreadsheet that I'm using & the report that I'd like it to look like.
View 4 Replies
View Related
Oct 5, 2011
There is pivot table for some reason the last column (YEAccts), which sums all the other columns values is exluding the 1st column for some reason. This "YEAccts" shows up fine in the pivot table "field list". And I followed the source for this and it's a table in a different tab that pulls data from an access query connection.
The name of the column that is summing up the rest of the columns is "YEAccts", but for the life of me I can't seem to find where this column is in the source table tab or even in the access query where the data is being pulled from.
How to find this "YEAccts" column? Also, why would it exclude not summing up the data in the first column?
View 2 Replies
View Related
May 14, 2012
I need to put a worksheet together that has multiple columns that I can then sort in order (on any one columns that affects each row together) in "vehicle type".
I want to set it up so the full listing can be sorted top to bottom in colour or then click sort to sort in MAX SIZE and or again sort in SPEED. (So sort in Alpabetical order or by value).
I havent used pivot tables before and assume its the best way. I dont want to use the filter system to sort as its too clumsy for the end user.
sort>sort>sort>sort>VEHICLECOLOURSMAX SIZESPEEDA9brown50100PRONTObrown100110VELICITOgreen200120
SPECIALpurple50155ZEROpink2585GREENVgreen5155MIDEOblack2000135DELVOpurple300155ASPIROblack6155
View 2 Replies
View Related
Jun 1, 2007
My pivot table wants to total 2 sets of data and put it in rows. I would like it to be in columns.
The small attached sample shows what I would like.
View 4 Replies
View Related
Jul 14, 2014
I was wondering if there is any way possible to link your pivot table filters to filter the data the same way in the table that it comes from? So if i had date as one of my headers and i filtered the date to a specfifc date, is there a way to also filter the date in the data source sheet?
View 2 Replies
View Related
Sep 7, 2006
I have a report that was created for 2005 that contains two worksheets: a "source data" worksheet and a " pivot table" worksheet. I cleared out the 2005 data in the "source data" worksheet and replaced it with 2006 data...after this I refreshed the Pivot Table and everything seemed fine. When looking at the file size I noticed that it was almost twice its original size....upon further investigation I found that the Pivot Table was internally holding onto the old source data (the "Show" functionality of the rows/columns in the table lists the 2005 row/column headers as well as the 2006 headers....even though no data from 2005 is shown in the Pivot Table).
Does anyone know how to purge the old data from the internal Pivot Table memory?
I hope this is enough information....let me know if you need more.
Thanks in advance for any help,
Jon
View 9 Replies
View Related
Jun 21, 2014
I'm trying to produce a fixed format pivot table in Excel 2010. Normally I'd just construct a manual table using COUNTIFS, SUMIFS etc, howver, for this exercise the requirement is to be able to click on any field and have a tab pop up with the relavent data a la Pivot Table. But....I need the Pivot table to have a fixed format (which I can do with 'Preserve cell formatting on update') and to have all of the rows and columns in irrespective of whether there is data or not (i.e. if I haven't sold any apples in June, I still want the 'Apples' field to appear, just with a value of zero). I had assumed that the option I needed was 'Show items with no data on rows/columns' but these options are greyed out. I've tried right-clicking on the whole table, on individual fields and on labels but still get the same greyed out options.
Is there any way I can retain all of the rows and columns?
View 1 Replies
View Related
Jan 21, 2013
I have a single workbook with multiple worksheets. Each worksheet has a different pivot table displaying a different view of the data. Each pivot table uses the same source data at worksheet1.
Each week i add new data to the end of the source data, which means that I need change the source data reference separately in each pivot table to update each pivot table view to include the new data. This is laborious as there are quite a few pivot tables.
Was wondering if there is some way of changing the pivot table source data reference on all pivot tables at the same time.
View 4 Replies
View Related
Jun 19, 2013
I'm trying to use a List Box to select multiple items on my Pivot Table but I can't seem to get it to work. Its the Report Filter part of the pivot table.
When the list Box is set to fmMultiSelectSingle it work and selects the indivdual items, but I need it set as fmMultiSelectMulti for if i'm also needed to select multiple items. (which doesn't seem to work!)
View 2 Replies
View Related
Nov 9, 2011
In the "Pivot Table Field List" I have the option to "Choose fields to add to report". I have a large number of fields that I want to choose and "Add to Values" - so it is tedious to select each item individually.My question: Is there any possible way select multiple fields at the same time? e.g.
a "select all" option, orhighlighting a range of the available fields (either by dragging or holding the shift key)I've looked / tried above with no luck.
Any Excel Add-Ins out there that might address this? I'm fearing not since my searches have come up empty.
View 4 Replies
View Related
Aug 24, 2009
I was wondering if anyone knows how to change the souce data for a Pivot Table?
Eg if I have the Pivot table looking at Columns A & B lines 1 - 20 and I want the Pivot to also include Column C and lines 21 - 50, how would I do this? for a chart I can just right click and select the "Source Data" option but it seems that this is not available for Pivot Tables.
View 3 Replies
View Related
Apr 10, 2013
A co-worker of mine set up a pivot table using data saved on an external data source on a shared drive. I would like to create a similar pivot table myself, linked to his pivot table so when he gets updated data, my pivot table also will update accordingly. My question has to do with the pivot table data source. How do I copy the connection?
View 3 Replies
View Related
Jul 18, 2007
How can I possibly find the source path of a file used to build a pivot table. So basically the pivot table is using an external source and I'm unable to find it.
View 2 Replies
View Related
Apr 4, 2014
I am trying to create a Chart from a Pivot Table. I want the Chart to show my projects and present each months amounts side-by-side comparing (Plan, Actual, Forecast) data each month for the calendar year.
I am trying to figure out how to generate the pivot table showing multiple projects so that the data is grouped by month comparing (Plan, Actual, Forecast) data. Projects are listed down the Rows while months Jan-Dec are along the columns. Each month has 3 columns because the second row contains header for the data sets of Plan, Actual & Forecast. (see sample below) When I try to create a Pivot Table, It shows multiple month fields (Jan, Jan2, Jan3, Feb, Feb2, Feb3) instead of a single month.
{Using Excel 2003}
NameOwnerJan JanJanFebFebFeb
PlanActualsForecastPlanActualsForecast
Project 1Region 1 126.1 119.6 119.6
Project 2Region 2 18.0 0.9 0.9 8.2 8.2
Project 3Region 2 80.0 2.6 2.6
Project 4Region 3
Project 5Region 3 60.0
Project 6Region 4 55.8 55.8 55.8
Project 7Region 4 19.4
View 2 Replies
View Related
Mar 27, 2009
I have a pivot table created. How do I determine which sheet it is pulling the data from, hence the source of the pivot table? Also, if can you go into source and modify data points, then in pivot table, will the results would be automatically modified with updated data points?
View 2 Replies
View Related
May 10, 2009
I've got a bit of a problem revealing pivot table source data, so just wondering if anyone know how to reveal it? My colleague sent me the spreadsheet, I can see the pivot table, but I can't find the source data that the pivot table links to.
View 3 Replies
View Related
Sep 26, 2011
I am using pivot tables/charts for drilldown reporting. After running a complex macro to consolidate multiple reports into one range, I need to generate pivot tables and charts.
These reports will change month to month in the number of entries they have (template will be identical), and therefore my data source range for the pivot table is bound to change. Therefore my question is, how to develop a macro that will dynamically change the source data range to the amount of rows?
View 5 Replies
View Related
Mar 27, 2009
I am using pivotable with access queery as the source.
When I set it I cuse that external source browse tool, but it seems to only recognize the source if the dive is the same.
When I go downstairs to try on a users computer there dirive letter(K is different than my (H and it does not work.
View 9 Replies
View Related
Feb 22, 2010
why the data reference for a pivot table is changing after I email the workbook to a client. The source data and the pivot tables are all part of the same workbook and I can't understand why its saying can't find source data. Excel 2007 is being used on both computers.
View 9 Replies
View Related
Apr 24, 2006
I am using pivot tables which were created by another (no longer contactable) and have found two fields in the field list which are not part of the source data i.e. neither are column headings but are used in the pivot table.
My questions are:
1. How are these field created?
2. How can I examine these fields for formulae (as I have found one of them returns an incorrect value)?
Field1 return a numerical value which is the difference between two columns
Field2 shows this as a % (but incorrectly)
View 4 Replies
View Related
May 31, 2007
My fixed asset software will not allow me to show multiple individual months of acquisitions. I can export each month. This will create a separate spreadsheet for each month, but the problem is that each spreadsheet may contain the same asset, but additions to that asset.
Spreadsheet 1
....Description............................Dept...................January acquisition
.....Building____________________Plant______________1,000,000
Spreadsheet 2
.....Description..........................Dept...................February acquisition
......Building___________________Plant______________1,000,000
.....Welder____________________Metal_________________5,000
What I want is this
Spreadsheet 3
.....Description.........................Dept..................January Acq..........February Acq
......Building__________________Plant____________1,000,000__________1,000,000
......Welder___________________Metal_________________________________5,000
View 4 Replies
View Related
