i have a table and have some columns.
i want to filter the table so that i just see the records which have "text1" in ther column "F" OR they have "text2" in ther column "H", for example.
How can i implement an OR filter ?
This is a logic based macro that needs to compare values in 1 column above a specific cell and take action until the column has no more data. Here's what I'm trying to do (and I've also attached a sample of desired results):
1) I want user to input starting cell. So I need to create a button for that.
2) This is where the logic begins and I'm having difficulty.
From cell defined in button, I move up one cell in that column and compare that value to the original cell.
If the value is greater than original cell then logic needs 2 outcomes:
a) move up 1 cell in column and if that value is less than original cell, then extract that value and copy that value one cell to the right or 1c.
b) move up 1 cell in column and if that value is greater than original cell, then move up again, until find lesser value than original cell. Once we find value less than original cell, extract value and copy value one cell to the right or 1c.
I have some columns on which I have a filter, with some columns next to those that have information in them.
What I need to do is filter only columns A-F when apply filter values, but keep columns I-K fixed as A-F change when they are filtered..
1. I am starting with two independent tables, one with ‘ItemA’ and the other with ‘ItemB’
2. The user must be able to select an itemA which will point to itemB list, showing only a specific pre-defined group or combination of the itemBs.
3. This pre-defining of the itemB combination will be done uniquely for each itemA selection
Example: Selection of an itemA, row 2 will show, say, an itemB rows 2, 4, & 7 only. Selection of itemA, row 3 will show an itemB rows 4, 5, & 9 only. Selection of an itemA, row 4will show an itemB rows 2, 7, 11 & 13 only, etc.
4. In reverse, the user also needs to be able to select an itemB and display all itemA’s common to that itemB. Using the example above, the entry or selection of an itemB, row 2 will show rows 2 & 4, itemA.
This is represented visually in the attached GIF.
In Excel 2007 is it possible to filter the "Values" in a Pivot Table?
I am tracking the Gross weight of shipping containers. My table sums the weight of all items in a container by container number. So my rows are 9 digit container numbers and my data values is a Sum of part weights. I want to be able to filter out containers above a certain weight.
for example:
Row Labels Sum of Gross Wt. - Lbs
10003150588929
10003153258700
10003155984958
1000315651530
10003156549761
I know I can simply copy and paste into a new tab and sort it there, but I'd like to be able to do it internal to the pivot table if that is possible.
basically i have a macro to filter a lot of data based on peoples names but the people keep changing so I dont want to go into VBA each time to update the name list int he macro.I've created a list of names in a sheet in excel but not sure how to point the macro to filtering by that instead, heres what I have:
VB:
ActiveSheet.Range("$A$3:$H$61").AutoFilter Field:=7, Criteria1:=Array( _
"Name1", "Name2", "Name3", "Name4", Operator:=xlFilterValues
ActiveSheet.PageSetup.PrintArea = ""
[Code]....
I have named the database list for each name and its on a sheet called Team_List
From what I understand there's no way to do this without using macros. I would perfer not to use macros, but I need my PivotTables to auto-refresh anyways and apparently that will require a macro, so oh well. I'm very new to macro coding so I can't seem to successful apply any of the previous threads about this to my sheet.
I just need my pivot table on sheet "Customer" to filter the customer field based on the value in cell C1. Also I need to make sure the sheet doesn't "freak out" if the value in C1 is either blank, or is a customer value which doesn't exist in the table. Preferably in this scenario it would display nothing on the pivot table, but I don't know if that's possible. I want the sheet/macro to allow an invalid filter value in C1 just so the sheet doesn't lock up until it is corrected.
I also need my workbook to auto-refresh all the pivot tables is that's easy to code in as well.
I try filter a pivot table based a cell.
[Code] .....
Attached File : FilterPT.xlsx
I need to create a drop down menu where the contents are based on a separate table that has been filtered dependent on a choice made in the initial table.
In Sheet "Bookings" I have a list of lesson times for a riding school.
One of the columns is labelled "Customer experience" and contains either "Advanced","Intermediate" or "Novice"
A second column is designed to assign the name of the horse used by that rider during the lesson.
Horse details can be found a second sheet called Horses.
The horses are rated "Advanced" can only be ridden by Advanced Customers, whilst Intermediate ones can be ridden by both "Intermediate" and "Advanced" customer. Novice horses can be ridden by anyone.
Ideally I would like to be able to include a drop down in the Bookings table that contains the name of available horses.
I have a pivot table which has a report filter. The report filter can have anywhere from 20 to 350 values. The user will be able to select multiple values from the listing to produce the table as needed. I would like to be able to display what values were selected in the report filter so that the user can see this information once the pivot table is rendered. Is there any way to display this information either above below or along side of the table itself?
I am using Excel 2011 for Mac, but I also have access to Excel 2011 for Windows. Either way will work. I have been able to get quite a bit of what works on Windows to work with the Mac.
I'm on Mac using Excel 2011. This means I don't have "slicers", which is all my Googling kept turning up.
I have a pivot table with 4 different value columns, and I want to be able to filter it the same way you would a normal table - i.e. remove everything below a specific number in one column, and filter for only specific strings in another column, etc.
I have a 6000+ row database with 5 columns across. The 5 columns have the following headings: Hair Color, Eye Color, Age, Location, and Salary.
I have created macros that will filter each column based on criteria - for example:
[Code].....
As you can see, at cell J10 and K10, I list the filter that is used for a particular search. Some searches, will only have 2 search criteria, some may have all 5, etc.
On a separate worksheet (in the workbook), I have a "Report Tab", my question (after a long build up) is how do I dynamically change the columns and values on the "Report" tab depending on the search that I perform? For example, if I did a two variable filter (Hair and Salary), those would be the only two columns on the report - if I did all five variables for the search, all columns would be on the report, and so on.
Is it possible to set up the second level of a report filter based on the value of the first report filter. I have a pivot table that needs two report filters: first is the location and the second is the department. What I want to do is have the user be able to select the location and then only show valid departments for that location. I know that this is possible if I set up a form and have the second field be a query against the first. I can create a calculated field but I cannot put that in the report filter area.
View 2 Replies View RelatedI found this code and am trying to use it to update the filter in my pivot table (sheet 6), based on the data validation selection in sheet 1, but when I make my selection on sheet 1, nothing happens.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim pt As PivotTable
Dim pi As PivotItem
Dim strField As String
strField = "Region"
[code]....
I have data in two cells.If both are different then it should display that column data with different color.
EX:
COL A COLB
1 1
2 1
If col b is not match with col A then it should display that col B value cell with some color.
I have a named range of values on Sheet2 (GPI). Sheet1 is an OLAP pivot table containing row label (GPI 14) and values (Net Rx Count) only.
Unfiltered this list is over 7,000 rows. I need VBA code to display only those rows where the GPI 14 value matches any value in the named GPI range on Sheet2.
In other words how can I display the select rows without manually selecting the items of interest AND without manually hard coding the values in the code as they will change.
Below is 1 of the many codes I tried. This appears to be the most intuitive but I get an 'invalid procedure" error at Set my PivotTable...
'Sub PivotAnalysis()
'
Dim myPivotTable As Excel.PivotTable
Dim myPivotField As Excel.PivotField
Dim myPivotItem As Excel.PivotItem
[Code]....
I created a contact list that incorporates buttons at the top of the page to sort the data based on two customer type columns (thanks to Richard for help getting this far!).
I am attaching the file so that it will be easier to take a look. Four of the five macros are working fine but the second one ("All Clients") filters on the premise that "yes" is answered in either of the two customer type columns. In other words I am trying to show the records that have "yes" entered in either of these two columns, not necessarily both columns.
Column A: T/F
Column B: True/False
Here's what I want in a sentence: If one of the cells in grouped row is "T", column B = True, if not then column B = False.
(row groups are separated by a blank row)
Example)
Col A,Col B
TTRUE
FTRUE
[Code] ....
I came up with following formula:
=IF(MATCH(TRUE,INDEX(ISBLANK(A1:A17),0),0)>MATCH("T",A1:A17,0),TRUE,"")
It checks to see if blank cell is found before "T" but it doesn't look rows above.
inincubus.grouprows-1.xlsx
I have a dump which looks like below sample:
3
3
2
2
The actual dump will have around 200 - 500 rows of data.From the above I need to manually group them which are similar. E.g. the data of (3, 3, 2 , 2) in row 1 & 2 are matching, so they will be group 1 & row 3 will be group 2, row 4 will be group 3 respectively.
The sum & sort did not work as sometimes the data with the same range are not in same order. simpler logic/VBA code if any, which will automatically put group numbers if the data in two rows are exact match.
E.g of the output below for the above data:
Group No.
1
3
3
2
2
I have a perfectly working pivot table and I would like to make some graphs based on the report filter. My report filter has 4 categories, with each more than 10 sublevels.
When I make one pivot graph/chart, this goes fine, the data is ok, and I am happy. But one I make a second, and thus adjust the report filter, the first graphs changes according to the filter. I dont want that to happen
Ultimately I would like a powerpoint presentation with multiple charts, based on one table, with different report filter filters. Updated ONLY on the values, not the filter.
I have a very large table and i need to be able to Hide/show specific ranges based on:
Filter +and+ specific cell values in columns
brief example of the table : tablee.png
So...
1. Filter Column "B" (in this case we select "HELPING")
2. Auto hide/show collumns. - IF "C1" = "Required" THAN Show "C:E", IF "C1" ="N/A" , HIDE "C:E" and so on for every column like above.
There are over 80 columns like the "C:E" range. and I only need to show those that are "Required".
Any way to create a calculated field in an Excel 2010 pivot table that will find all the Transaction Types (Report Filter) with "transportation" in them and make the field Quantity 0 and leave all other quantities the same? I do not want the quantity of transportation added in twice and may not have the flexibility of adding a column to the raw data.
I used the formula below in a calculated field and it does not match the values using the added column to the data file.
=IF(ISERROR(SEARCH("*transportation*",'Transaction Type')),Quantity, 0)
I am trying to get the sum of Quantity field to equal the AdjQuantity field using a calculated Pivot field and not add a new column to the data.
I am trying to copy a spreadsheet i made in google docs in excel keeping the same functionality. Since the formulas are a bit different between the two, i can not simply copy the codes so i must start from scratch. The basics of my spreadsheet is that i have (3) columns: A master list, A filtered list, and an input list. I want the filtered list to change based on the values in the inputted list.
ColA = Input list
ColB = Filtered list
ColC = Master list
Items:
Orange
Red
Blue
Yellow
Green
ColA contains the colors i manually enter
ColB contains all the items in ColC that are NOT already listed in ColA (except for Red, i dont what that showing up)
ColC contains ALL the items above.
Two Examples of a functional worksheet:
ColA............ColB
Orange........Blue
Green..........Yellow
ColA........ColB
Orange....Blue
.............Yellow
.............Green
ColB is where the magic happens, it grows and shrinks based on the values in both the master list ColC and the input list ColA
On a first sheet, I have a list of cities where physicians travel along with an autofilter. On that same worksheet, I have a cell where users can input a zipcode.
On a second (hidden) sheet, a function calculates and displays in column F the distance in miles between the user-input zip code and all the cities in 4 states. The cities are listed in column D.
I need to filter all the cities on the first sheet (the ones where the docs travel) based on their distance from the user-input zipcode - namely all the cities located less than 150 miles from that zipcode.
I am trying to create a macro that will allow the user to select choices from combo boxes on a userform. The choice of one combo box determines what will be shown in the next combo box and so on. This will occur a set number of times (depending on what they are looking for), at which point the list of possible choices will be presented.
I populated one combo box in UserForm_Initialize() but then I might have to change the others with Combobox1_Change()...
upon further consideration I thought that it would be best to probably do verything in two passes or maybe not see below macro to get an idea of how the end process should function
View 4 Replies View RelatedI need to put a macro in the toolbar which copies data from another worksheet. The trick is I have several worksheets and want the one macro to pull data from different columns based on which worksheet is active. So I need to test the name of the active worksheet in my logic.
View 5 Replies View RelatedHere is my attachment with data in first sheet and expected output in second sheet. Have given comments in second sheet for better understanding.
Data is of a debt collection, in sheet column F "form #" denotes loan form numbers and column L "paid" denotes the amount received or not received.
In this Form # are unique and form number will repeat with different paid amount.
Entire row to be colored based on the sum value of paid column amount of respective form number.
1. non repeated single form number with negative value of paid - Orange color
2. repeated form numbers with sum of values in paid column as positive value - only entire row of form number with negative value in blue color
3. repeated form numbers with sum of values in paid column as negative value - first form number row to be colored with yellow and rest of repeated form numbers with purple color and its sum value (negative value should be made available in yellow line of the Form # group to the right of right most column data)
4. form numbers and paid column of value "0" - pink color
I tried recording and edited macro only for the logic of paid value "0" and confused with other logics and declaring variables...
I'm running into an issue trying to calculate unique values in a Data column based on a few variables in other columns.
My current formula in Summary tab D4:D19 is
{=SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$H$1,
IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))
+
SUM(IF(FREQUENCY(IF(Data!$I$3:$I$66<$E$1,IF(Data!$A$3:$A$66=$I$1,
IF(Data!$C$3:$C$66=A4,ROW(Data!$I$3:$I$66)))),ROW(Data!$I$3:$I$66)),1))}
This is currently counting the number of times a date value (data column I) appears for that name (A4:A19) in the data when meeting all of the conditions. I need it to instead count the number of times a unique date appears for that name with the additional conditions met (which all appear to work fine).
The results in the pink highlighted cells (Summary column D) should be:
Names starting with A - 3
All others - 2
I've left some other columns in the data with X's so that I can easily convert this back to my working spreadsheet.
I have different reports, some have fifty transactions, others have thousands. My goal is to: Insert a new row every time the values in the "Account" column meet a certain criteria, AND THEN add the totals for the Debit and Credit Columns.
Let's say I start with a table that looks like this:
A
B
C
D
Dept
E
Account
T
F
F2
G
Debit
Credit
Total
33010
[Code] ....
I want to group the first four rows because Accounts 33010 and 33015 are in the same department. Same with 50050 and 500060. I want to then insert a row below the last row with "33015" as its Account #. And add the values for Debit and Credit. It'd look like this:
A
B
C
D
Dept
E
Account
[Code] .........
Honestly, I have tried everything. Running a Macros with Relative reference does not cut it.
