Pivot: Same Data In Two Columns
Feb 11, 2009
I would like to show the same set of data grouped by two criteria but subgrouping them. When I put my data in the data area, and the criteria (crit1, crit2) in the column fields area I get the values grouped first by crit1 and than subgrouped by crit2. What I would like to see is grouping by both criteria independently, first by one and than by the other, without subgroupinig.
Hope it's not too confusing :-)
Up to now, I've been solving that by multiple pivot tables. Is there any way to do the same?
View 4 Replies
ADVERTISEMENT
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
Oct 17, 2007
When i tried to drop another field into data field, by default, the new data field will appear below the field which was already in there. However, i would like the new field showing in an new column - horizontally - rather than showing in a new row.
For example:
Rather than the Pivot table appear like this:
Q108A 72
____B84
____C68
____D5
Q109A 64
____ B 73
____C58
____D 5
I would like the privot table present like the following format:
____ A_ B_ C_ D_
Q108 72 84 68 5
Q109 64 73 58 5
Is there setting I can change, so that the new field is added to a new column.
View 7 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
Sep 9, 2008
I have attached a spreadsheet of an example of a database with a pivot table and a desired report
Can anyone give me a pivot tabe from my database that looks like my desired report.
I am willing to seperate the database into two seperate databases with different transaction types if necessary.
View 6 Replies
View Related
Jul 24, 2013
I have 2 columns with a list of competitors (competitor 1 & competitor 2) involved in a negotiation + the price/value of the negotiation. Each line represents a negotiation with a value in numbers & the names of the 2 main competitors involved.
I have around 150 lines in the original file and would like to show in a graphic what are the competitors that we regularly find in the negotiations and what is the value of the negotiations they are involved.
The problem is that there is no main competitor so i can find the same name in any of the 2 columns and i cannot make separate graphs for each column because if i do so i duplicate the value.
Is there any way that aggregate this info into a single graphic/pivot graphic? Im using excel 2013
View 2 Replies
View Related
Aug 9, 2012
I have 29 excel files with some number of worksheets from 1 to 4. The name of the worksheets are the same in all the spreadsheets. Then I've a got a pivot table. I have to compare some data (3 columns) from the pivot table to the numbers from all these sheets from 29 excel files.
How to do it in a most efficient way?
View 4 Replies
View Related
May 13, 2013
why is pivot table changing numbers to dates. It was fine all along, numbers retained the fromat of source data and now, all numbers in field columns are turning into dates. is there a setting I can change to prevent excel from reformatting numbers to dates?
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
Sep 5, 2013
I've attached some dummy data. Basically, everyday I'm going to dump a report containing data into a 'Data Dump' tab. 'Pivot 1 - Filtered' I have set up so that it will show specific product IDs only (In this example I only want to see 1X, 2X and 9D).
However, I now have a need to see the total value of that order number in 'Pivot 2 - Autofilter'. (For Example, order number 1111 has both 1X and 8D contained within it). Now obviously the simple thing to do would be to filter every order number shown in pivot 1, and filter pivot 2 with them. However when I'm doing this for real, this can be tens/hundreds of order numbers.
The only workaround I have found is to put a column at the side using VLOOKUP and if ISERROR is false then include in pivot, then used an autofilter on that, but I'd like to keep the normal pivot if possible.
View 1 Replies
View Related
Mar 18, 2013
I manage a team that quote for business. They are targetted on number of quotes per month but then also wins per month, however a win may come a number of months after the original quote was generated.
They work off a spreadsheet where they log:
Quote date (And month)
Quote Value
... customer info etc
Win month (against orginal quote information)
Win value (against orginal quote information)
I started writing my pivot using the Quote month as the main reference point, IE Quote Month in the Row, and then put all the data in the central drop data section... However, there are two immediate problems with this:
They are targetted on quotes generated per month, some business won has come from last year, which they are lifting from the old spreadsheet and puttin in my new one... meaning that quotes they generated in Oct 2012 are now showing up as quotes for October this year.
If the wins span a number of months they are showing in January (if quoted in January) and not in March (when actually won)
Is there a better way of writing my pivot table??? There must be... ideally what I would like is half the table referencing the Quote date and half referencing the order date... but I don't know how to do this? I could probably do this with pages but I would like to show all on one sheet...
View 2 Replies
View Related
Feb 15, 2009
I have a question of the best method to add STATUS comment column to the right of a pivot table say during a status meeting and when the pivot table is refreshed that the STATUS comment is locked with a specific row of data in the pivot table...
View 2 Replies
View Related
Jul 14, 2014
I have a pivot table that I create weekly to include the new week's data, as well as the rest of the weeks in the year. I'd like for the macro to pull all of the columns like "WE" into the pivot table so that it includes the current week. Below is what the code looks like now. Is there a way to tell the macro to pull columns based on "field name like" language? I've been able to get this far, but it doesn't loop the headers to find all of the columns that have WE headers.
Sub AddPTFields()
Dim PT As PivotTable
Dim PF As PivotField
Dim FldName As String
Dim LastCol As Integer
Dim TxtStr As String
Dim wsName As String
[code]....
View 2 Replies
View Related
Mar 15, 2013
I have a pivot table that shows the following:
ID_LOCATION1_LOCATION2_COUNT
1__XXAT______213_______1
_____________215________1
_____________226________1
2 XXAL______213_________1
_____________228________1
3 XXAA______213________1
_____________123________1
_____________258________1
_____________065________1
...and the list goes on for 1300 more ID items
What I need is to reformat it to show the location2 as columns one next to each other as follows:
1 XXAT 213 215 226
2 XXAL 213 228
3 XXA 213 123 258 065...
View 1 Replies
View Related
Mar 19, 2013
On a worksheet, I created:
- a list of data
- a pivottable based on these data
When moving this worksheet this worksheet to another workbook, the pivot table can't refresh anymore. This throws an error message "Reference is not valid". To work around this problem I need to adapt the datasource. The same occurs if the list and the pivot table are on separate sheet, with the added strange behaviour that, when data an PT are split, it is not possible to move both sheet together.
This would not be a big issue if my problem had to be solved manually. The real problem is that I need to move the sheets from a C# program.
View 3 Replies
View Related
Nov 2, 2012
I have two queries regarding pivot table.
I would like to include additional columns with formula in to a pivot table
I would like to get ride of the Items in a pivot table with Zero values without editing the source data.
I have attached a work file in to the below link, [URL]....
View 1 Replies
View Related
Nov 22, 2013
What I am trying to get is the distinct number of cost codes based on the budget codes.
Basically, when I do a regular pivot, I can see it gives me the 'COUNT OF CODE CODE' = 4 in the summary section, but, that seems to give me the number of records per Cost Code which is not what is needed but rather I would see a distinct # of codes codes based on budget code, and to boot, the subtotal should be directly under the Cost Code column.
Is that even possibly just using the pivot tools available in excel?
View 2 Replies
View Related
May 9, 2014
1) I have created a pivot table where one of the value is minimum of a "Rate". So far, so good.
2) Is there a way I can access other column values from the same row "MIN" value was picked up from ? Or I have do a VLOOKUP & INDEX ?
Attached is my excel that has sample data and what I need.
View 3 Replies
View Related
Jul 1, 2014
I have a pivot table showing actual and forecasted sales per month for diffrent regions/sales channels/segment.
I would like to calculate the average monthly sale, for forecasted and actual data, within the pivot instead of doing it outside in columns to the right of the pivot table.
View 1 Replies
View Related
Nov 22, 2013
I want to get a subtotal of columns B C and D in a pivot table. I have tried to add a calculated item to a pivot table to add columns B C and D. When I try adding a calcuted item I am getting an additional column inserted after columns B C and D. Each additional column has the previous column duplicated. I want a subtotal of column B C and D. I don't want to use the grand total function because I also have columns E through H that I don't want in the subtotal.
How can I get the columns B C and D subtotaled within the pivot table?
View 5 Replies
View Related
Oct 24, 2011
How can you visually format a column with multiple values in a pivot table? Say you have month in the column section, and three values in the value section: budget, actuals, variance; I want to visually seperate each month's set of data.
View 1 Replies
View Related
Mar 26, 2013
is there anyway to arrange pivot table columns without manipulating the field list items?
in other words, in older versions of excel, you could just right click the column and select move left, or move right
View 2 Replies
View Related
Jul 18, 2013
I have a large data set that I want to pivot on to produce various results. I can easily pivot on two columns to produce those Unique IDs where East AND West are "Yes" or "No." I want an easy to filter on results where either East OR West is "yes." I could do this with a joined (Concatenated column) such as East/West but was hoping there might be an easier solution built into Excel pivot feature.
Unique ID
East
West
1
Yes
No
2
Yes
Yes
3
No
Yes
4
Yes
No
5
No
No
View 2 Replies
View Related
Jan 7, 2007
Pivot Tables. The structure and subject-content of cells B3:D14 (Block A) is identical to those of cells F3:H14 (Block B). I want a pivot table to treat the two Blocks as if they occupied the same three columns (e.g. B3:D26).
View 3 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
May 14, 2014
why it works (but it obviously does). I crabbed off the sheet, modified it with my data and the percentages calculate reliably.
What I can't figure out is that in the Pct calculation in the table is the formula:
=GETPIVOTDATA("S_FCR",PT_1,"Center",[Center],"Date",MONTH([Date]))/GETPIVOTDATA("C_ID",PT_1,"Center",[Center],"Date",MONTH([Date]))^2
The formula itself makes perfect sense in terms of the numbers and filtering involved....but why does it actually work and what does the "^2" do?
View 2 Replies
View Related
Jun 16, 2014
Not sure if this is possible but I have a sheet of data which is effectively a project plan. It has a list of resources, role and phase etc. I then have lots of rows which are w/c dates and under each "week" I have the amount of hours or days they work. So for each person I can record their hours etc.
I then need a Pivot which basically collates the data and displays each phase's total hours but month. There maybe multiple phases or and each person may appear in each phase
I've attached an example - Excel Problem.xlsx
View 1 Replies
View Related
Aug 14, 2014
So I am running in to a large brick wall! I have a single pivot tablet with a column for items received and then a column for items shipped. I would like to create a calculated field in the pivot table that will subtract the items received from the items shipped columns. I have attempted to add the calculated field using the tool bar but it keeps giving me "0" for every item on the pivot table when there are obvious differences.
View 1 Replies
View Related
Dec 10, 2012
I have a spreadsheet similar to this:
Name........Cat....Dog....Fish....Mouse
NY............Cat.....Dog....Fish..........
NY............Cat...............Fish....Mouse
DC.....................Dog..............Mouse
DC...............................Fish............
CA ...........Cat................................
I want to create a pivot table to do this:
Name.........Cat....Dog....Fish....Mouse
NY...............2.......1.........2........1..
DC...............1......1..........1............
CA ...............1.............................
I have been unable to get it formatted correctly. The closest I get are "sub-colums" with counts.
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
