Is there a field setting in pivot tables to return the number of unique records?
Is there an excel formula to define a range of cells, and returns then number of unique records: (i.e =Unique(A1:A10)?
I have a pivot table with Sales and Budget, if i need to find the difference in pivot table i think i need to use field settings, however i am not getting the result
I have attached the same file for reference
I have a vast amount of data entries in a pivot table (over 100). I want to create a pivot table but when I click on it it says 'count' and not 'sum'. It will take me a long time to change them all, how do I do it quickly? And possibly, without the 'sum of ....' in the label of it?
View 3 Replies View RelatedI have my raw data in one Worksheet and have made multiple pivot tables based off this data. Since I needed to have the same calculated fields in all these Pivot Tables, I used to simply duplicate the pivot table sheet and edit the pivots to create a new table.
Now, I'm trying to create a new pivot table from scratch, by selecting the base data table and going to "Insert Pivot Table". However I see the same Calculated Fields and upon removing, the field is removed from all other Pivot Tables.
1. I have 4 pivot tables on one sheet, all with the same page field (store name) and all relating to same data table.
If I change the store from the page field on one table, I want it to simultaneously change the other 3 tables to the same store.
2. I have a similar sheet which has charts instead of tables for which I wish to achieve the same thing.
I am trying to write a list of pivot table page filter pivot items to the Immediate window - but only hidden items. The code below should do the job where the active sheet is a pivot table.
However when I change the page filter pivot items being hidden, the pivot items returned by the macro don't change. It seems to assume that all pivot items are hidden when in fact it may be only one or two. It will work though for pivot row items (pvt.RowFields) and pivot column items (pvt.ColumnFields) where the user changes them.
Does the pf.HiddenItems collection work for RowFields and ColumnFields but not PageFields? If so, is there a pivot field object that reliably holds hidden pivot items residing in the page filters?
Sub ListHiddenPageFilterPivotItems()
Dim wb As Workbook
Set wb = ThisWorkbook
Dim ws As Worksheet
Set ws = wb.ActiveSheet
Dim pvt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem
I have a list of purchase order line items. Each Purchase order could have multiple line items, so the list could have many occurances of the same purchase order number. I have designed a pivot table which summaries line items by ordering area (one of the fields in the list).
This works fine but what I would also like to check is the number of purchase orders by area. How can I acheive this? I would prefer not to use any helper columns but will adapt my application if this is needed.
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 RelatedHere is a sample of data that I have:
code name lastname
TS19SylvainBrook
TS19SylvainBrook
TV04ValérieMusoni
TV04ValérieMusoni
VB05BenValiquette
VB05BenValiquette
VB05BenValiquette
VI02IsabelleVanasse
VR07RichardVivo
VR07RichardVivo
VR07RichardVivo
WD01DanyWilliams
WD01DanyWilliams
WD01DanyWilliams
How can I quickly get the total number of employees from a list like this one? the first field is a unique key (1 code per employee)
In this case, the desired result would be 6. (the count function does NOT give the desired result..)
I searched and searched and I can't find an easy way to do this without using Access which I am rubbish at. Is there an easy way to do the following in Excel.
I have several thousand records by row each with a unique numerical identifier. The unique identifier is the "Household". Within the household there are sub "Accounts". The sub accounts are truely unique. All the "Accounts" are in the same column.
What I am trying to do is combine the records into one row per "Household" with the accounts listed in successive columns. The maximum number of accounts there may be is 7 but it can be as few as 1. Example:
BEFORE
HH Acct
1 1234
1 2345
1 3456
1 4567
2 9876
2 8765
3 1113
4 5556
4 4447
4 3335
AFTER
HH Acct1 Acct2 Acct3 Acct4 Acct5 Acct6 Acct7
1 1234 2345 3456 4567
2 9876 8765 8765
3 1113
4 5556 4447 3335
I am using a pivot table to explore the relationship between the two variables in a survey. Question 1 is a "choose all that apply" question, while Question 2 is more "if you said yes to any in question 1, choose all that apply".
So, I have about 10 filters for question 1, where each combination will give me a different number for the responses for question 2. My question is this: How do I get excel to automatically find the # of individuals with the particular combination of filters in question 1?
Ie. if said yes to 3/10 options in question 1, how do I get excel to find out how many actually said yes to the 3/10 questions.
The purpose of this data is for reference to question #2, so that i can utilize some percentages.
Im trying to lookup the first unique number in a column in a separate workbook and then return the value then the cell below would lookup the next unique number and return the value
91010111112121313700
700
700
7009929921442144218341834185018501892189219181918333433343400
3400
3400
3400347634763534
In building my pivot table my data that I want to show in the column area is showing up as rows stacked on top of each other. In the column section I'm trying to show Total Budgeted Amount next to Total Actual Amount but on the layout it's showing the two stacked on top of each other is there some kind of hidden key that I'm missing?
View 3 Replies View RelatedI have two tables, both contain a column of ID numbers and a column of addresses.
I would like a method to match the IDs from table1 to those of table2 - if no matches are found, search for matching addresses, if a match is found then output the ID in table1, the address, and the corresponding ID in table2.
If still no matches are found, output the ID and address from table1 and indicate that it didn't match.
Then I want to check table2 records - i.e. search for matching address between the records and output the table 1 ID, address and table2 ID were partial or no matches are found, as above.
I am trying to combine records in a database whose address is same. Because husband and wife have the same address so I want to send only one newsletter to that family.
My problem is to merge those records whose address field value is same. find attached an excel sheet that contains the exact data and the desired result.
Attached are two files. I want to make the "ORIGINAL" file look like the "DESIRED_OUTPUT" file using VBA.
View 3 Replies View RelatedI have a pivot table in the first sheet which includes the field "Date" as a column label.
In the remaining sheets, except for one, there are pivot tables based on the same underlying dataset which also include the field "Date" as a column label.
I would like to adjust the selection (i.e., exclude some dates) from the column label in the first sheet and see if it is possible to make the same adjustments automatically to the pivot tables in the remaining sheets as well.
note that the field "Date" is used as a Column label, i.e., it is not a Report filter.
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.
I currently have several pivot table that's linked to a single pivot table(let's call it X) in the same workbook. I'm doing this to limit the file size because the data in X comes from a text file that has millions of lines. However, it's such a pain every time I need to update the tables because simply clicking "refresh" does not update those tables that are linked to X with new data. I would have to instruct the wizard in every linked table to point to X every time. I'm trying to write a small program to re-point to X for each of those other pivot tables whenever i refresh data. However, after trying to record the steps to do this I'm still unable to run these
Sub Macro1()
ActiveSheet.PivotTableWizard SourceType:=xlPivotTable, SourceData:= _
"PivotTable1"
End Sub
Is it possible to create pivot table from another multiple pivot table.
Example: I have two diff pivot table "Income" and "Expense" as well
and I need to preapare new pivot table using with those two pivot table
1. I am trying to record a macro where I select a Pivot Table. But in the recorded macro "Pivot Table name" is not recorded neither the Pivot Field Property only the Range name is recorded. But on other systems (workstations) these details get recorded. Does this have something to do with excel settings?
2. I uploaded a macro enabled excel file on my company's "sharepoint" the drop down boxes present in the file get populated via a macro in "ThisWorkbook" page but sometimes these drop down boxes don't show any values. What can be the reason for this? Can't share the file because of data security policy of my organization.
I have 12 months worth of data in twelve separate workbooks each with a pivot table, I have copy/moved the pivot table tab from each into one workbook so now I have a workbook that consists of 12 tabs each with a pivot table. What I would like to be able to do is create a summary table with the full years data; where I am running into problems is that each months table has slightly different row and column counts and labels making any formula like =sum([sheet 1 cell a1]+[sheet 2 cell a1]) problematic.
View 3 Replies View Relatedif there is a way to display a table as column percentages but have the totals as raw numbers.
View 9 Replies View RelatedI've got 4 pivot tables (all derived from the same base data) on 4 separate worksheets. I've been able to (with this help of this site) to use VBA to hide pivot items on all of these sheets using a list on a user form. Hide/Show Pivot Table Field Items. Hide Pivot Table Fields Pivot Items by Criteria
I now need to be able to show all the pivot items on only 3 of the 4 pivot tables, with the 4th pivot table being left untouched. For ease assume that my sheets are sheet1, sheet2, sheet3, and sheet4. The tables I wish to update are on sheet2, sheet3 and sheet4. The pivot table on each sheet is called "PivotTable4" and the pivot item is called "Business". The pivot item contains 12 business names (Business1, Business2 etc etc)
Is there an easy way of doing this? I've spent the day looking through the internet and various "Dummies" books but with little success, I fear that I'm obviously below even Dummy level
We have our new employees fill out a form with name, address, tax info, etc. each week. (They all do it at the same time.) I then get 20 PDFs and export all the data to a CSV. We now want to import that CSV into our payroll software. However, the payroll software requires a unique ID for each employee.
So, we'll have this CSV each Monday, and each Monday, I need to "continue" the unique ID. So if this past Mondays started with 1000 through 1020, then next week, when I export, I want to start those records at 1021 and end at 1040. Then the following Monday, 1041-1060, etc.
Is there a way I can keep a record of what IDs are used and continue from that point each week?
I am working on a data integration and have transactional data sets with multiple columns of data. In reviewing the data, I can see that based on values in 2 different columns, I can identify unique transaction records. The data is already sorder by transaction # and date. Creating a pivot table gives me back summary info for header records (I can get unique header records based on a concatenation of the trx # and Date), but I am having a difficulty obtaining uniqueness for the line items. The id that I created for purposes of header record summation means nothing from a user standpoint and I need to come up with more meaningful info.
What I have and what I want is...:
Tranaction#DateWhat I want is…15801911/23/2005015801911/23/2005015801911/23/2005015801911/22/2005115801911/22/2005115801911/22/2005115801911/22/2005115801911/22/2005115801912/12/2005215801912/12/20052
If I can get the data defined like that, then I can concatenate the trx# and the increment to block out the transactions into individual transactions.
I have downloaded data to an excel spreadsheet by day and need to calculate the unique records by day. Then all the daily totals should equal the monthly total if I ran the same date range for the month by removing the duplicates to get the unique records.
View 9 Replies View RelatedI have a formula for counting unique text records. However, is it possible to count unioque records based on certain conditions. For example, I have a stats sheet for a sports league and I want to count all the unique teams for dsivision 'North'. Coloumn A has the division, coloumn B has the team they played on that day.
I want to count all the unique records in column B, but based on those teams being in the 'North Division' (column A)
I want to do this with Excel, and not in VBA.
My formula for finding unique records is:
I have a very basic table of customers. In one column there is duplicate data. I guess in most cases an Excel user would only want to filter the table to show the unique records only. In my case I want to do it the opposite way round, to delete the unique records so I have multiple occurrences of strings that appear in that one column. I've used the "Conditional Formatting" trick, which is great = COUNTIF($G$1:$G$44000,G1)>1 highlights all of the strings that appear more than once. What I'd like to do from there though is to either just have that data, and to remove the unique records.
Either that, or.. is there some way to have a field/column which shows "True" or "False" if such a string has appeared more than once in a column. Auto Merged Post;I forgot to mention.. the reason I'd want a column of "True" and False" would be because then I'd have the ability to sort/group the data into all the recurring records and all the unique ones. I'd then be able to do away with the unique ones by just copying the recurring ones.
I am trying to populate a tab with all the unique values from a data table. For example, list all the SKUs, SKU Names, Buying Groups, etc. for "Owl Filled Candles" on the "COLLECTION - SKU" tab. For reference, the "VENDOR - SKU" tab works perfectly. I want the COLLECTION tab to do the same thing as the VENDOR tab. The only difference is the VENDOR-SKU tab is pulling data based on a Vendor's name in a drop-down list in B3 and the COLLECTION tab is pulling data based on the Collection name in a drop-down list in B3.
I tried to just copy the tab and reset the reference cells but that isn't working.