Is it possible for me to compare two pivot tables and get a report. in the attached example, is it possible to compare sheet1 and sheet 2, if the values haven't changed, then it should be green, or it should be red.
View 3 Repliesit's possible to set up conditional formatting to compare values between two identically-formatted (i.e. same row and column) pivot tables?
I have two pivots from two different sources and I want to be able to visually identify discrepancies between the two. Both have dates as the row labels (same date range) and categories as column labels (some categories may not necessarily be present on both pivots but the format of the labels - i.e., the names of the categories - is consistent where there is overlap)
I've tried doing this with 'traditional' CF but whenever I manipulate the pivots (i.e. by selecting / deselecting an item in a report filter), the movement of the columns trashes the formatting and I have to set it all up again.
Figured, given the structure of the tables is essentially the same, there might be a smarter way of doing it (by referencing the pivot fields in some way)
I 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
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
Macro that compare between 2 tables in 2 different sheets - BOM (Bill Of Materials) compare
And Create a Table with all differences.
Table 1 (sheet1):
PartNumQTYLocation
000 2 A1
111 3 B1
222 1 C1
333 4 D1
Table 2 (sheet2):
PartNumQTYLocation
333 3 D1
000 2 A1
111 3 B2
222 1 C1
444 5 E6
Table with all differences (sheet 3):
PartNum (Ver1)PartNum (ver2)QTY (Ver1)QTY (Ver2)Location (Ver1)Location (Ver2)
111 111 3 3 B1 B2
333 333 3 4 D1 D1
Blank 444 Blank 5Blank E6
I need to check data from two tables(A and B). They are supposed to match each other and my job is to check if there's error in it. Each row contains several criteria, and all criteria need to be matched. I need to give a report on the info that
1) contains in table A but not in B
2) contains in table B but not in A
3) contains in A and B but some of these criteria do not match..
I have basic knowledge of VBA. Forget to say, they are in different order. And even after sorted, the name may still unmatch with each other, so can not use formular like "=Sheet1!A1=Sheet2!A1"
Example:..........
i have a database. column a is the last name. column b is the first name. column c is a person's title. i regularly have to merge this database with another from another department and compare to see that all staff are processed and that titles are correct. i have been trying to write a formula that can pull from the contents in any given row, search the rest of the sheet to find if there is an identical person listed and finally let me know if the title is the same. i just need a "true" or a "false" if both conditions are met.
View 6 Replies View RelatedHow do i get my excel to compare between tables and come up with a result. For example i have 2 tables,
Table 1
[Code]....
Table 2
[Code] ....
How do i make them compare and then come up with a result like
[Code] ....
I have two employee rosters, "yesterday" and "today", with same columns heading(First, Last, Location, Status, etc, etc, etc . I want to compare both rosters and highlight the differences on "today's" roster if an employee's information (location, status, etc) changes from yesterday.
Another change could be, an employee may not be on today's roster as he was yesterday and I could have a new employee on today's that wasn't on yesterday's. Is there a way to copy the row/record from yesterdays roster and add him to today's but highlight it so I know that he is gone?
Compare Two Sheets and Highlight Differences (Sheet attached)
I have two lists (List A and List B) of hundreds of items that reside in a single worksheet.
Some items may or may not occur in both lists.
Every item in both lists has a value on the range of 1-50.
I am only interested in those items that occur in both lists.
I need to find the difference of the values for each of these items (List A - List B), including negative values.
I have tried many variations of formulas using VLOOKUP with no luck.
I cannot figure out how to accomplish this with a Pivot Table.
I want to make a macro for this.
Here's an example:
List A
W 29
X 11
Y 13
Z 4
List B
V 8
W 10
Y 30
Z 6
Where the results would be:
W = 19
Y = -17
Z = -2
I have 2 tables
One is paycheck issued
The other one is paycheck cashed
My goal is to find out which paycheck is not cashed
Table 1
Table 2
PAY CHECK ISSUED
PAY CHECK CASHED Name Net Pay
Name Net Pay Last, FIRST 76.28
FIRST LAST 76.28 Last, FIRST 163.14
Last, FIRST 193.48
FIRST LAST 193.48 Last, FIRST 156.97
I have two tables with "X" and <Blank> data. The tables share the same column headers. I want to compare the rows of each table and if the rows contain an "X" in the same column then it puts an "X" in the third table. I have attached a sample file with my expected results on the third worksheet. I have created named ranges if that helps the formula writing.
Name1 =Sheet1!$A$2:$A$5
Name2 =Sheet2!$A$2:$A$5
Produce =Sheet1!$B$1:$E$1
Group1 =Sheet1!$B$2:$E$5
Group2 =Sheet2!$B$2:$E$5
Sumproduct seems to do the trick if I turn the "X" into "1" and <blank> into "0". If there is a match the value is greater than "1".
=SUMPRODUCT(Sheet1!$B$2:$E$2,Sheet2!$B$2:$E$2)
I prefer to use "X" though if I can. I would like to automate the formula instead of manually adjusting the ranges for every cell.
I need to look colB and colC should be equal to colH and colI, IF both cells matched, then copy the EmpNo(ColB or colH) and Counter(colC or colI) get the difference of values in another colomn
If there is any row for Employee or Counter issing from either of tables populate with RED in either of 2 colomns,
for example:at row 17, table 1 has a missing colC, and row 17(table1) = row18(table2), it should populate the row17 for table2, as a reference.
Desired output is to compare a value in one table to a value in another table based on the same values in associated columns in each table and produce a "True" or "False".
In each worksheet, the information should be similar but I want to compare them.
Worksheet#1 = "ABCD"; Column A = Lot#; Column F = "Defect T"
Worksheet#2 = "EFGH"; Column B = Lot#; Column R = "Defect T"; Column I = "Inspection level"
I want to find the lot # from cell A2 in worksheet "ABCD" in column B of worksheet "EFGH" AND column I in "EFGH" = "1st Inspection". At the same time, I want to compare the cell in F2 in worksheet "ABCD" to the value in column R that correlates to the same row where the lot#s match.
Is it possible to use a nested IF statement to match the cells?
I'm making a basic excel sheet as a hotel reservation tables. Sometimes we accommodate who do not follow the hotel regulation, so we add them to the black list table, made from (First name, Last name, Phone number). See image below:
Then if we reserve for a new customer, we use this sheet:
Here is my question:
what should i do, to make a raw color turn to red if i have entered a black listed visitor ?
e.g. if i enter "Martin" as a first name, AND "Issa" as Last name, AND "76344909" as a Phone. then the raw will turn to red to indicate that this customer is black listed.
I have 2 Data tables in 2 sheets of the same workbook. I want to compare my column A of table 2 with column A of table 1 and delete any rows of table 2 where (column A of table 2 has a value which is not in the column A of table 1)
In Excel I used the Vlookup function and deleted any rows which had Error in result of the formula. May I know how to execute this in VB
I have a 8 pivottables with a graph for each. is there a way that i can have a dropdown menu to select which graph to display instead of having all these graphs everywhere....
View 4 Replies View RelatedI have an issue with printing a worksheet with a pivot table. When refreshing the table, depending on the filter choices, the table length with expand and contract. When the pivot table contracts, it leaves a light blue shaded area. If you try to print the worksheet it includes the blue section. Is there a print macro that can be written that will only print the sections with values or perhaps a print setting that would exclude the shaded area?
View 1 Replies View RelatedWhen in a worksheet and clicking in the pivot table results, the pivot tables no longer display.
I did have a reinstall of Office last week but can't see how that would have impacted this. Otherwise, I only use the pivot table command to 'refresh all'.
I know very little about them and didn't create this workbook.
I do add entries to the source data and have tried to change source data but I get Reference is not valid.
When I run pivot tables, the row label descriptions are only displayed at the highest level for each category. As displayed below the highest level on the row label is MOB and is only detailed in the first row. I need for each of the different row label levels to display the data so there are no blank cells.
MOBMan. Boxset 2Large1328Manual StickeringN/A500Shrwp 13+Large16680Small596Shrwrp 7-12Large35704Small2411SW 1-6Large87912Small14635SW w/ PremLarge26966OUTDisplay Lvl 1Small100Klutz Standard Display 6-packN/A2499Man. Boxset 1Large40000Man.
[Code] ........
All I want to do is write some vba script that inserts a pivot table based on all of the data in my current workbook.
Therefore I want something incredibly simple if it exists like:
ActiveWorkbook.PivotCaches.Create(SourceData:= ActiveWorksheet.Select.AllData)
ie the vba code that just takes the data you've got in your current worksheet and creates a pivot table.
I have four sheets with a lot of pivot tables on each and my problem is that whenever I hit Refresh the formatting of the pivot tables is always changing. It seems that this is something of an issue in Excel 2010 and that they only way to truly get around it is to re-format the pivot tables upon refresh with VBA code.
Any resource or thread I can use to learn the commands to format the pivot tables?
I am getting better with VBA code but seems to always get stuck on trying new things because I do not know the commands.
I'm trying to sync a common field in two different pivot tables.
I have 2 pivot tables on a single worksheet.
Each table has its own data source - 2 different databases (had to set it up that way to present all the data requested).
The 2 data sources have one column of data in common. This column is called Projects and all project names are the same in both databases.
BUT...
Pivot Table 1: Projects is in the ROW area (multi-select dropdown)
Pivot Table 2: Projects is in the PAGE area (single select drop-down)
I would like to link the Projects data items so that when I select a project name in the Combo box, the same project name would seamlessly be selected in both tables.
Each table would populate with its own data based on the project selected.
Basically, I'd like to use the method illustrated in this Flash file:
[url]
This method would have worked beautifully if it weren't for this reason:
Table 1: Project data is in the PAGE field
(single selection)
Table 2: Project data is in the ROW field (multi-selection)
If the Projects data was in the PAGE field in both tables, my code would look like this:
ActiveSheet.PivotTables("Table 1").PivotFields("Project").CurrentPage = _ActiveSheet.Range("X1").Value
ActiveSheet.PivotTables("Table 2").PivotFields("Project").CurrentPage = _ActiveSheet.Range("X1").Value
But no, because the ROW field is a multi-select one, I get this kind of
I have several pivot tables that need to be re-linked to an Access database (really change the link to a different month's data). Currently, I do this manually, but I was hoping to write a macro to do this. Data is saved by month so I can't just create a "current" file as to not change the links each time. I can only get code to work for the refresh portion.
View 9 Replies View RelatedI have used the macro recorder to create a macro that creates a pivot table from data located on another workbook.
with this said i have two questions.
How can i make it so that the workbook containing the data for the pivot tabel does not need to be open?
the second question is why am i gettign stuck with a macro that sticks right here: