Filter A Table With An OR Logic Based On Columns Values

Feb 10, 2010

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 ?

View 2 Replies


ADVERTISEMENT

Macro Logic: Extract Higher/lower Values Based On Unique Cell

Jun 12, 2009

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.

View 2 Replies View Related

Filter Only Columns A-F When Apply Filter Values?

Mar 26, 2013

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..

View 1 Replies View Related

Filter Table Based On Choice In Another

Jun 22, 2008

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.

View 5 Replies View Related

Excel 2007 :: How To Filter Values In Pivot Table

May 31, 2013

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.

View 3 Replies View Related

Macro To Filter Table Based On Another List?

May 7, 2014

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

View 4 Replies View Related

Filter Pivot Table Based On Cell Value

Jan 9, 2014

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.

View 2 Replies View Related

Filter Pivot Table Based On Cell?

Mar 11, 2014

I try filter a pivot table based a cell.

[Code] .....

Attached File : FilterPT.xlsx

View 3 Replies View Related

Dropdown Based On A Filter In Initial Table

Mar 8, 2012

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.

View 1 Replies View Related

Excel 2011 :: Display Filter Values In Pivot Table

Jan 17, 2014

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.

View 1 Replies View Related

Excel 2011 :: How To Filter Pivot Table With Multiple Values On Mac

Apr 17, 2014

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.

View 9 Replies View Related

Dynamic Columns - Filter Based On Criteria?

Feb 19, 2013

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.

View 1 Replies View Related

Report Filter In Pivot Table Based On Another Field From The List?

Feb 20, 2012

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 Related

Filter Pivot Table Based On Data Validation In Another Sheet

Feb 14, 2013

I 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]....

View 3 Replies View Related

Logic For Comparing Columns In Excel

Jul 24, 2013

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.

View 1 Replies View Related

Filter OLAP Pivot Table Based On Named Range In Another Worksheet

Mar 14, 2012

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]....

View 4 Replies View Related

Trying To Build A Macro Filter Based On Data In Either Of Two Columns

Jul 31, 2008

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.

View 9 Replies View Related

IF Statement Logic In Columns - True / False

Mar 31, 2014

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

View 3 Replies View Related

Logic For Sorting Values?

Feb 17, 2014

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

View 6 Replies View Related

Excel 2007 :: One Pivot Table - Multiple Graphs Based On Report Filter?

Jan 10, 2014

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.

View 4 Replies View Related

VBA To Hide / Show Columns Based On Filter And Multiple Criteria

Mar 8, 2014

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".

View 1 Replies View Related

Excel 2010 :: Pivot Table Add Calculated Field Based On Certain Text In Report Filter

May 29, 2014

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.

View 2 Replies View Related

Filter Values Based On Master And Input List

Apr 11, 2013

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

View 8 Replies View Related

Filter Data Based On Values Located On Another Worksheet

Dec 28, 2011

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.

View 4 Replies View Related

Filter Choice Selection Based On Combobox Values?

Apr 24, 2014

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()...

View 4 Replies View Related

Complex Logic Formula To Delete Values

Nov 14, 2009

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 Related

Conditional Logic Based On Name Of Worksheet

Apr 24, 2009

I 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 Related

Coloring Entire Rows Based On Certain Logic

Aug 1, 2014

Here 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...

View 9 Replies View Related

Count Unique Values In Column Based On Values In Other Columns

Mar 14, 2014

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.

View 2 Replies View Related

Insert Row (Based On Values In Range) With Sum Of Values For Other Columns

Apr 21, 2014

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.

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved