adv bar
a11 b3
a2 b3
a3 b5
a4 b6
a5 b8
I have a pivot like the one above. I want to keep only the rows if "bar' falls within a range. So in a certain range (say d2:d7) I set the criteria for "bar". Is there a way to tell the table to keep only the rows, if the "bar" value can be found in d2:d7?
I have a worksheet in which I have sorted the data based in date and numbering (column I and E). I would like to create 2 macros for following actions:
1- all rows with the value "TOM" in column C will have to be deleted.
2- all rows with a value of 601 or 602 in column E, will have to be moved to the bottom of the sheet after the last row with data. The rows that have been moved will have to be sorted based in date (column I) and numbering (column E).
setup a vba code to find values which were separated by a certain value, thus there would be pairs of numbers sought after. What occured was that the first value is bolded, followed by the second not bolded, this pair would be separated by 75. Now I want to apply the same code, but instead of copying just the two cells which were separated to someone on the current sheet, I would like it to copy the entire row (as there is more data associated with it now) with the same bold/not bolded pairing to a new sheet, called "Numbers". I would imgine it be straight forward, yet I am very new to this and still tinkering.
Heres the ....
I have been using the code found here
Sub DeleteRowsFastest()
Dim rTable As Range
Dim lCol As Long
Dim vCriteria
On Error Resume Next
'Determine the table range
With Selection
If .Cells.Count > 1 Then
Set rTable = Selection
Else.............................
to delete rows that match the given criteria. I am now wanting to do the opposite, keep the rows matching my given criteria and delete all others.
I have a spreadsheet that contains two input tables: Parallel and Perpendicular. Next, the user can select a group which a certain entry belongs to. Now what I want is a "summary" of these entries in a table that has no blank rows and combines both Parallel and Perpendicular entries by the Group. Please note that there is no fixed number of inputs for any one group i-e the solution has to be dynamic.The groups are numbered from 1 to 6 and number of groups is fixed i-e 6.
A sample file illustrating the inputs and required output is attached.
I also posted this on: [URL] ....
Sample_file_summary.xlsx
I have a worksheet with 20,000 plus rows, header=yes, and I need to delete all rows NOT containing the numbers 6600, 6700 or 6800 in column C. I'm currently using the below code which works, but is DOG slow.
Sub TestDeleteRows()
Dim Firstrow As Long
Dim LastRow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
. ScreenUpdating = False
End With
I want to read information from the "Mapping" sheet, then find information in the "GLEX" sheet that correspond to the read information, then copy the information that was found in "GLEX" to the "Calc" sheet and add it all together and display the sum in a particular cell in the "Report" sheet.
The "Mapping" sheet is a mapping of information to show what makes up a particular total and where it must be entered in the "Report" sheet. The "GLEX" sheet is the output from a financial system and the source of the information that must be "reformatted" to the "Report" sheet. The information is only an extract as the whole totals to around 10MB. I have also deleted the "Report" sheet as it is fairly big on it's own, but the principal will be the same, I think. (If i should upload the "Report" sheet,)
Step 1:
"Column A" and "Column B" of "Mapping" are the starting points. The macro must read the values in "Mapping" "Column A" and "Column B" and remember them. Then it must find the row in "GLEX" "Column A" where the information from "Mapping" "Column A" matches and where the information from "Mapping" "Column B" matches with the information from "GLEX" "Column E".
Step 2:......................
I have a spreadsheet that contains two input tables: Parallel and Perpendicular. Next, the user can select a group (from a drop down) which a certain entry belongs to. Now what I want is a "summary" of these entries in a table that has no blank rows and combines both Parallel and Perpendicular entries by the Group. There is no fixed number of inputs for any one group i-e the solution has to be dynamic.
Attached is a file illustrating the inputs and the required output. I would prefer a formula based approach but not against VBA based suggestions either.
Sample_file_summary.xlsx
Also posted at: [URL] ....
I have two workbooks at the moment, one which holds a whole host of customer and accounts details and another which holds only the customer details. What I'm trying to do is cross reference the two to determine if a customers details appear on both and if they do highlight this, ultimately I'd like it to pull the customers account number back but even if it highlights a match that would be sufficient.
The only unique criteria I have to do this are customer surname and customer postcode.
In the Customer details spreadsheet I'm attempting to enter a formula which will look at the specific surname & postcode held on the row in which it is entered. It will then look at all of the rows on the other spreadsheet and determine whether any of them meet both criteria.
If they do I'd like it to display "match" if they don't I'd like it to display "no match"
Hi All, I have 2 pivot tables (PivotTable1 and PivotTable2) which both source from the same pool of date.
The difference between the two are that I have a different report filter criteria.
For one the heading is "day", for the other it is "night". Each colum of data has the same entry range (names of staff) and I want to be able to link the two tables so for instance -
If i select "Paul" from the (day) report filter on PivotTable1, I want the (night) report filter on PivotTable2 to also change to "Paul".
filter my pivot table using a cell value, rather than manually selecting from the dropdown.
The report filter title is Rnd H, and values start from 0 through to 2, at .1 intervals (so 0.1, 0.2, 0.3, 0.4 etc).
Rather than select "0.3" manually from the dropdown, can the pivot table do this if "0.3" was written in cell A1 for instance?
I have a report to fill that looks like this:
Table 1:
HEMANITOBAMANITOBA SK12
I want to fill it up with data from this pivot table:
HEIntervalManitobaManitoba SK11-10522-8423-8224-6125-5826-6427-6828-5929-53210-47211-53212-63221-7522-8023-7924-7825-7726-7627-7728-8229-86210-86211-86212-722
So basically what I want to do is for example from Table 1: If I want to find the what goes in cell in B2 (1, Manitoba) then I want to go to the pivot table find 1 which is in the first column(HE), there are 12 intervals to each HE(as seen in column 12), I want to take the average of all the HE with 1 corresponding to Manitoba (the values in Column 3) and to be put into B2 in Table 1.
I've got two columns with data. The first is text, and the second is numbers. So I want to be able to enter a certain criteria that would be contained in the text, and have excel return a list of the rows of text that contain that criteria and that have the highest 3 values in the corresponding column. I can use multiple cells to do this if need be (i.e. one cell for the text with the highest value, one for the second highest value, and a third for the next highest value).
View 4 Replies View RelatedI'm trying to make a pivot table that can compare sales based on the whatever month/year/salesman combination I give.
Ideally, I'll be comparing the sales data for 3 seperate months. Can I make a pivot table where I can make different filters apply to specific columns?
I am using pivot table for my customer aging which a sample is enclose. What I want to do is to Highlight the field " Customer Name " in colour if the the Field " Type of guarantee" is other than 0. Also a message of " Credit limit Exceeded" if the outstanding is more than the Credit limit.
View 2 Replies View RelatedI want to write a VBA code, so I can apply dates criterias to my pivot table. Say, I have 1-Dec-2007 in "C2" and 10-Dec-2007 in "C3". Now I want my pivot table to show me the dates between those two dates and the data that goes along with it. I have written this code, but it keep debugging:
Sub FilterDates()
Application. ScreenUpdating = False
Sheets("PnL").Select
Sheets("PnL").PivotTables("PivotTable3").PivotFields("Date") _
.PivotItems("01/01/1950").Visible = True 'to always have 1 populated
I have looked at the below Hide/Show Pivot Table Field Items help web pages:
Hide/Show Pivot Table Field Items
Hide Pivot Table Fields Pivot Items by Criteria
I am trying to use the above, but with dates in the following format in each cell:
YYYYMM
200612
200701
200702
200703
200704
200705
200706
etc, etc
The below code is working for >200702 and removes all years/months prior to this entered value. Unfortunately the code does not work when a user enters <200706, instead the code goes through to the “NonValidCriteria” prompt.
Sub HideByCriteriaYYYYMM()
'Declare variables
'SEE: [url]
'SEE: [url]
Dim pt As PivotTable, pi As PivotItem
Dim lMonth As Long
Dim strCri As String, strCri1 As String, strCri2 As String
Dim bHide As Boolean
Dim xlCalc As XlCalculation
STO-Productivity-Feb0314.xlsxI work in cancer research and I'm trying to find a way to show the amount of studies that staff have in total and their role on the study. I have a data table that contains many fields but the crux of what I'm having trouble with starts with the following:
When I begin with a pivot table I start by just selecting the all of the studies listed in the table and then the two roles that staff can have data or regulatory. Staff can do both roles or just one on a study. I put the roles of data and regulatory into the row labels and the studies that we work on in the values. The problem is that I now only see studies that the staff member works on that meet working on both roles and not the studies that they serve only one role.
UPDATE: see the attached spreadsheet to illustrate further. Ablorh is the employee that I have it focused on and it only shows the one study she has both the regulator and data roles. The 7 other studies that the employee works on in the data role do not show.
Sub Hidesingles()
For i = 5 To ActiveSheet.UsedRange.Rows.Count
For c = 3 To ActiveSheet.UsedRange.Column.Count
If Cells(i, c) .........
I have 63,893 rows and 3 columns (Parents, Component and Qty) in a table. This would be typical for a Bill of Materials showing the Qty of Components in a Parent. A lot of Components are common to a mumber of Parents.
In total I have 4,259 Parents and 10,904 Components making up this 63,893 rows in my table.
The problem is that neither the Pivot function in Excel nor Access will handle it, least not the way I'm doing it - the bog standard way ! I just get the "Excel cannot complet this task with available resourses......" message.
Anyone any ideas how to get a Pivot or something resembling one so that I end up with the Components as the Row Header and the Parents as Column Headers.
I know I could go in and manipulate the data but I want to be able to create this Pivot like report every month or so without having to reinvent the wheel each time.
I have a pivot table and i am trying to display the rows in order of relevance. I have a bit of code that will sort the rows into the order I want them which I have included. My problem is that sometimes I will have rows that will not be there, If position 3 doesn't exist I would expect it to display 1,2,4,5 but instead I get an error message.
ActiveSheet.PivotTables("PivotTable11").PivotFields("Baan Order").PivotItems("Nomination") _
.Position = 1
ActiveSheet.PivotTables("PivotTable11").PivotFields("Baan Order").PivotItems("Full") _
.Position = 2
ActiveSheet.PivotTables("PivotTable11").PivotFields("Baan Order").PivotItems("Equal") _
.Position = 3
ActiveSheet.PivotTables("PivotTable11").PivotFields("Baan Order").PivotItems("Target") _
.Position = 4
ActiveSheet.PivotTables("PivotTable11").PivotFields("Baan Order").PivotItems("None") _
.Position = 5
I wish to create a Pivot Table. It is based on source data with fixed columns (A to W) and dynamic rows. The following is my
Dim WS As Worksheet
Dim DataRange As Range
Set WS = ThisWorkbook.Worksheets("sheet1")
DataRange = Range("A1:W1", Selection.End(xlDown)).Select
ThisWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=WS.Range(DataRange)).CreatePivotTable _
tabledestination:=Sheets("sheet2").Range("A1"), tablename:="PivotTable1", defaultversion:=xlPivotTableVersion10
Dim PT As PivotTable
Set PT = ThisWorkbook.Worksheets("Sheet1").PivotTables("PivotTable1")
PT.PivotFields("Type of Work").Orientation = xlPageField
PT.PivotFields("Profit Center").Orientation = xlRowField
PT.PivotFields("B/(W) CTD Net Rev").Orientation = xlDataField
Because I'm going to use it in my work, it must be reusable. And every time I use it, the numbers of row are different. Therefore, I made the source data into a dynamic range. The range changes when rows change. However, problem always appears in the following line:....................
Pivot tables. I have a dataset from which I make a pivot table. In row labels I have 4 different columns of data below each other.
Level 1
Level 2
Level 3
Level 4
In some cases the data only has three levels, for example:
Earth
Europe
Germany
In other cases it has four levels of data:
Earth
Europe
Germany
Berlin
If I make a pivottable it will give 4 levels for all items. In the first case I mentioned, the data will state:
Earth
Europe
Germany
(blank)
Is it possible to remove the (blank) row as it does not provide any useful information..
I am writing regarding the Pivot table creation with data which contains empty rows.
Recently, I work on a project with a data which contains empty rows. I will attach the file for sample to see what sort of data contains my sheet.
As you can see the data is divided according to some criteria, and it is constantly updated by users. sometimes a new criteria is added at the end of the sheet, that is why I need to create a pivot table, to be able to just changing Source, to update the table and manipulate table in order to get desired results, but I can't remove these empty rows rom the data as far as some unexperienced users have to fill the information and it is more comfortable to have the data separated from each other for them.
However, when creating the Pivot table on this type of data, the pivot table will include the blanks Row Labels there.
In my Pivot table I have 3 fields in the "header - section" of each row
There is also the possibilty in the Page section to choose between subjects (eg physics, chemistry, biology etc) When all rows are displayed there are 68 in total
When I choose Physics there are about 30 customers that have a value in 1 of the rows. Excel shows 30 rows, but I would like all 68 row to be shown, because these are the values that are important to me. I have tried a lot of settings in the pivot table but can't find the correct 1.
What happens a lot is the the rows are "multiplied", meaning that the 1st row header has every combination of the 2nd and 3rd and so one. Which setting is needed to get what I want?
I have a pivot table which shows below... I need to set up a conditional format to highlight the row where all fields are "0"
Company
Deals
Opps
Days since Deal
Days since Opp
Days since Meet
Co 1
1
7
80
20
20
Co 2
0
0
0
0
0
Co 3
1
4
30
30
28
Co 4
3
3
30
30
37
I'm new to using VBA and have been recording macros and then trying to edit them where I need to and using Google to get the answers that I need, however I have hit a snag when I create a pivot table using my current macro.
The data sheet that I am creating my Pivot Table from will always have a different number of rows from week to week, but I am unsure of how to code this into my macro. When I recorded this, I used the range A1:S10000, which gives me (Blank) as the last row in my pivot table. change my code so that it only selects the data rows to stop these blanks appearing?
Code:
'Pivot Table DATA Tab to show Call Out Times for all Centres in file
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"DATA!R1C1:R10000C19").CreatePivotTable TableDestination:= _
"PIVOT!R3C1", TableName:="PivotTable1", DefaultVersion:= _
[Code]....
I have a workbook with a Pivot Table full of user data and security reports. The table is structured in a way that shows, numerically; each username and how man roles are assigned. I need to remove the username/row combo's that are listed as 1 or less. Obviously if I simply highlight the fields I want to delete it won't work. How would I go about removing such rows?
View 2 Replies View RelatedI have the code listed below where i keep getting a compile error. This is from a You Tube (Pivot Macro - Episode 1211 - YouTube) episode 1211. why this would not work? Basically trying to set up a macro that creates a Pivot Table that will always have the same number of columns, but each set of data will have different number of rows.
ActiveWorkbook.PivotCaches.Create(SourceType: xlDatabase, SourceData:
DataSheet & "!R1C1:R" & FinalRow & "C37", Version: xlPivotTableVersion14).CreatePivotTable
TableDestination: NewSheet & "!R3C1", TableName: "PivotTable2", DefaultVersion: xlPivotTableVersion14
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.