Filtering Two Columns And Setting Specific Value In Third

Mar 4, 2009

I have requirement wherein, I have to put filter conditions on two separate columns in the same sheet & putting hard coded value eg. "XYZ" in third column for this selected range. I am using macro autofilter but its giving me errors for multiple filters.

View 9 Replies


Setting Filtering Across Multiple Sheets?

Jul 31, 2013

I work with project data.... I have attached a small sample. I will have data across 8 - 10 tabs with respect to costs associated with different projects.

What I do now is go through each tab and set filters based on what project and date range I am looking for ... if I am not careful, there may be other filters previously set that I do not notice and my data isn't accurate and I may spend quite a bit of time reviewing all the filters and resetting as needed.

But, the filtering will be the same across all tabs (i.e. I may want to see all project data for a single month, or I may want to see all project data for a single project across all date ranges.)

is there a way to create another tab which I can identify the desired filtering than can populate across all the tabs? not all the tabs contain the same number of columns of data, but they do all contain some similar columns that represent the typical filtering range.

View 1 Replies View Related

Setting A Button To Go To A Specific Sheet?

Sep 26, 2009

I have a sheet called "Richard" and i wish to assign a button so that when i click it, it goes straight to it.

I know how to make a button and assign the macro but i just need the code. So would be very grateful if somebody could help me with this or point me in the right direction.

View 5 Replies View Related

Filtering / Separating Specific Datasets?

Jul 10, 2014

All of the strings are in one column and are in General format. They contain a few different types of strings: some only numbers, some numbers separated by commas (which makes them NOT numbers according to ISNUMBER), some text only, some text and numbers (separated by comma), some are 3 numbers, each separated by comma, etc.


What I'm looking to do:I want each of these distinct types filtered out someway. I've already done ISNUMBER to filter out the values which are numbers only, no commas. Perfect.

For the next step, I'd like to filter out cells which have only numbers and commas. The comma itself makes them considered not numbers, so how do I add the condition that I'm looking for cells with digits and a comma? A wildcard?

Edit: I've found the following formula which can check if it contains a comma, but doesn't specify between digits or text. Just commas. =IF(ISNUMBER(FIND(",",A1)),"TRUE","FALSE")

View 1 Replies View Related

Specific Criteria Filtering To New Worksheet

Apr 1, 2013

I am creating a workbook with multiple worksheets. When row H on the first worksheet matches certain criteria, I want the whole row that this specific cell is in to copy to a separate worksheet of the same workbook in excel so that a list of these rows automatically compiles. How do I do this?

View 1 Replies View Related

Setting Sell Price To Specific Profit Margin?

Nov 29, 2012

I am looking for a formula which will calculate a sell price with 20% profit after taxes and costs. I need it to factor in a fixed shipping cost (eg £10), a 7.5% Amazon fee of the sell price, then 20% tax of the sell price. I currently have a formula which essentially does this but as I'm not good with Excel it requires me to input all the values every time which is not practical considering our large inventory.

View 3 Replies View Related

Setting The Findfile Dialog To Open Within A Specific Folder

Feb 27, 2007

The following code works fine


I would like to know if I can set it so when the dialogbox appears it starts at a folder located on my C drive

The default setting opens the dialog in MyDocuments

View 3 Replies View Related

Filtering Cells To Exclude Specific Data

Jan 31, 2014

I have a column that contains combined data of numbers and letters. I want to filter that column to exclude cells that contain the letter "d" or "f". I am trying to create a pivot table from the results of the filter to only display the ID #s that do not contain the letters "f" or "d".

The data inside the fields is not the same length and is not listed in a particular order. I have over 14,000 records I need to filter.

Data set example: The items in red are an example of the cells I would like to exclude from my worksheet. Is there a way to filter without deleting the data?


View 1 Replies View Related

Filtering On Specific Values Contained In Column

Oct 13, 2011

Column A
Row1 Orange-Apple-Pear
Row2 Orange-Grapes-Melon
Row3 Berry-Apple-Melon
Row4 Banana-Grapes-Melon
Row5 Orange-Grapes-Banana

Given the information above:

I want to find a macro that will auto filter on for example
"Banana" would filter to rows 4,5
"Orange" would filter to rows 1,2,5
"Apple" would filter to rows 1,3

View 4 Replies View Related

Pivot Table - Filtering Against Specific Range

Jun 20, 2012

I am trying to create a Pivot Table that filters on a range of data.

I have a sheet with lots of data on it, and separate sheet where I have created my Pivot Table and my list I wish to use as the filter values.

I need to extract 630 specific Departments out of thousands and report the movements against those depts. The 630 will change as time goes on and I want to just have a range where I can update the values as and when changes occur. I initially tried using Filter on Department but realized this was going to be a very long winded process.

View 4 Replies View Related

Filtering Columns When Adjacent Columns Are Blank

Apr 15, 2013

I import a text file that displays outstanding parts orders. The order Number column only displays one number even if there are multiple parts ordered I.E. so if three parts are on order then the order number is displayed once with the other two underneath blank. I would like to filter the column that displays the amount of stock that is available so that even if the row has no stock but the other two have all three rows are displayed that reference the order number.

Example Screen


View 5 Replies View Related

Sorting / Filtering Specific Letters In Random Code

Dec 7, 2013

I have an excel database that contains a code to identify specific people.

Jones 3 Quay St, PN 063586954 JU79N4
White 24 Dyk St, PN 063547786 9GVJ64
Smith 9 Random St, PN 063512698 4LN867
Butt 89 Yeah Pl, PN 063569986 D920HK
Handle 69 James Ct, PN 06 3549687 ZK26S84

If I wanted to filter the list so I only had codes that had Z, N, H in it. How do I do that??

View 5 Replies View Related

Macro For Filtering Spread Sheet By Date And Specific Person

Dec 15, 2006

I am fairly new to macros and I am currently working on a project where I would like to create a 2 buttons which will filter by date and by owner. The spread sheet I have is fairly large and is added to weekly.

I have two objectives that I am trying to reach:

First I need to filter for all past due task items from the Thursday of the week I am working in back and for a specific person (owner) by the finish date

Second I need to filter for upcoming tasks one week out for a specific person (owner). The objective of this is so that on any given day a user can find any upcoming tasks that are due one week out.

If this is not an option. My thought was to create an input box where the user can input the date and the information on the spread sheet will pull all past due items or upcoming tasks. These two items need to pull seperatly not together.

I know I can create a macro by autofiltering by columns however I am trying to avoid having to go in and change the date each week.

Start date is column E
Finish date is column F
Owner is column L

View 10 Replies View Related

Excel 2011 :: Pivot Table Filtering On Values In Specific Subcolumn

Jul 19, 2013

Is there is some way to filter based on the value in a specific subcolumn.

Using the example of a list of salespeople and their transactions over the year, who sell multiple products, the PT is Sales Person name for the Row Labels, and Type of Product for the columns. The resulting PT has 3 columns, for each of the products - e.g. table, chair couch, and the Values are the total number of that item sold. Is there any way I can filter, so that I will see only those sales people who have sold 3 tables or more lets say. (Thus enabling me to quickly see what other products those sales people have sold.)

(The actual situation is a lot more data heavy than that - it is actually a list of donations for a non-profit, coming in from hundreds of people, across a dozen different categories; I am trying to analyse the extent to which people who gave for a particular category (Direct Mail solicitation) also gave across other categories.

So far I've manipulated things by inserting a '% of row total' value into the PT and then using countif/sumif functions outside of the PT to figure out how many people donated solely to this category (= 100% of row total), and how many donated to other categories too (= more than 0% of row total, less than 100%), which gives me a decent summary of sole donations to this cause vs other categories too, but doesn't visualise what the actual other categories donated to were. I've also investigated making a PT of the existing PT, but I'm not sure if that's even possible...)

(I'm using Excel 2011 for Mac, but if there's some other version that would make this possible, I may be able to use another computer.)

View 1 Replies View Related

Filtering Columns

May 13, 2009

I have an excel with the following information

A1 - Is a dropdown which will have names of the systems (EAI,Mediation,CDF

Row2 will be header which will have following data

B2 - Project Id
C2 - System ID
D2 - Project name
E2 - Project Phase
F2 - Status
from g2 to GN2 there are about 60 resources names entered

From C3 to F3 the data wil be entered and appropriately a chk mark will be
made to the corresponding resource wroking on that project

Say i have about 10 systems provided as dropdown for entering data in A1
(Ex: EAI, MEdiation, .....)
There are resources working for each system

when i apply filtering on a system name i shoud be able
to show only those resources working for that system and all others should be

Also under the "system" dropdown i will have an option "All", when "All"
option is selected i should be able to see all the data for all the systems.

EX: if i take system "EAI" and if the resources working for eai are there in
AO2 to AX2 then i want the resources from AX2 onwards and before AO2
not to be shown.

If i take a system "Mediation" and if its resources are from Z2 to AI2
then i want resources before Z2 and after AI2 notto be shown.

If "All" option is chosen then i should be able to see the whole sheet
with all the data for all the systems and all resources.

View 12 Replies View Related

Filtering Data On Columns?

Mar 28, 2014

how to filtering data from the columns please (not the rows)..?

Usually we could filtering the rows with auto filter, right..?

Now, is it possible to do that with columns..?

View 8 Replies View Related

Filtering Multiple Columns

Jan 28, 2008

I am trying to filter three columns of data to a given range without having to using auto filter on each column. I would like to have a Macro or Control Button or something easier than picking each set of data each time I want to filter.

Attached is a sample file.

I am not well versed in Excel and this is my first post so please bear with me in this process.

View 14 Replies View Related

Filtering Multiple Columns At Once

Jul 28, 2008

a formula in excel. here's what i need to do:

column A has the names of deffirent drivers (ex; john, peter, james)

column B has start time (9:00, 10:04, 11:08)

column C end time (9:14, 10:16, 11:23)

i need a formula that will take all the "john" from column A, do column c - column b for the time it took and then do an average of all of john's attemps.

View 10 Replies View Related

Setting Up (Define Names) For 70 Columns Of Data

Sep 19, 2013

I need to create "Define Name" references for 70 columns of data so that I can establish Parent/Child relationships in a data validation list. Is there a quick way to do this or do I need to go through the same steps 70 times (i.e., once for each column)? My concern is that the current order of my columns might change too, and I don't want to have to recreate the "Define Names" every time it does.

View 1 Replies View Related

Filtering Data Using Two Separate Columns

Dec 10, 2009

I have text in column A and text in column B. I would like to create column C and place the number 1 in each row whenever columns A and B contain the text I am looking for. I could then use C to filter all the 1's with ease (by way of macro or the autofilter). In my problem, column A contains the following text per cell:


Column B contains:

I need to filter based on column A having the letter "A", or column B having the word "MATT". Columns A and B do not have to contain both "A" and "MATT", respectively, in order for column C to have a 1.

View 4 Replies View Related

Filtering Data In 2 Seperate Columns

Feb 25, 2007

I'm trying to put together a sheet to track football results. For simplicity sake, the sheet looks like this...

Home ; Away ; For ; Against ;
a ; b ; 2 ; 0
c ; d ; 1 ; 1
b ; c ; 1 ; 3
d ; a ; 2 ; 4
a ; c ; 1 ; 2

By using Autofilter on the home or away columns, i can obviously filter it in order that I can see all A's home results or all of A's away results, but by doing this you can't just filter the sheet in order to see the results of every game A plays in. I have a feeling that I need to do something with Advanced filter but have been playing around with this and not getting any joy. Can anyone advise on what I need to do?

Ultimately, my end goal would be to be able to create a drop down list where i would select the team from a list of all teams and this would automatically filter the sheet to their results only (and then I could use an autofilter to look at just homes or aways).

View 9 Replies View Related

Moving VBA Generated List Between Columns And Setting Arrays

Jun 21, 2013

I have been working on a few different spreadsheets, and I have gotten almost everything to work. There are just two things that I just can't get right. Here is the first problem:

It seems to me that I should be able to change where the list will display where it says: sh.Range("a10").PasteSpecial xlPasteValues

I change the range to anything in column A and it works, but If I change the column, the entire script seems to run in one row, and the days of the week don't move.

Sub Workbook_Open()
Range("a2:d300").FormulaR1C1 = Clear
Dim sh As Worksheet, dyArr As Variant, rng As Range, c As Range
Set sh = Sheets("Summary")
[Code] ....

Now the second issue, I need to know how to set the array to look at every single worksheet without listing every sheet. Here is an example:

dyArr = Array("apple", "explorer", "firefox", "google", "java", "safari", "templates")

Now, in the future I might need to add a worksheet in between google and Java, and I don't want to have to change the script every time.

View 3 Replies View Related

How To Total Only Visible Columns Or Rows After Filtering

Dec 1, 2009

I have filtered a worksheet and want to only total the data in the columns that are visable, is this possible?

View 4 Replies View Related

Excel 2010 :: Filtering Multiple Columns (OR)

Jul 18, 2012

I have a worksheet with

Col A being Name,
B being primary skill,
C secondary skill and
D tertiary skill.
(Sanitised example below)

There are about 15 diferent types of skills ("Skill x, Skill y etc") listed in each of column B,C and D.

I would like to have a filter (or similar) where all names would show if a certain skill is present in either column B, C or D.

For example, a filter that on the below spreadsheet would allow me to view the names of all people who have "Skill x" either as a primary, secondary or tertiary skill.

I am using Excel 2010.


Person 1

[Code] .........

View 3 Replies View Related

Locking And Protecting Certain Cells - Filtering Columns

Jan 22, 2013

I have a spreadsheet where I need to lock certain cells, only problem is this then takes away the ability to filter the columns which I also still need in that spreadsheet.

Is there a way to lock cells so that they can not be edited but also keep the filtering function?

View 2 Replies View Related

Filtering Columns In Excel Based On Public IP Address

Apr 18, 2014

I want to filter data code which contains public IP Address and copy the filtered data to other sheet. Column contains both string and number. Please see example:-


https://d31qbv1cthcecs. atrk.js

[Code] ........

In above table (second column) the URL address after http:// can start with string or can start with number. If it is starting with numbers the I need to check whether the address is public is Private or public. In second column, Private IP so I need to ignore it but is public IP and we need to copy to other sheet.

View 5 Replies View Related

Merging Code For Hiding Columns And Setting Chart Axes Values

Feb 23, 2010

I'm trying to create a code that when run hides a selection of columns and defines the minimum and interval value for a chart on the active worksheet. My attempt is assigned to the 'update skills' button at the top of the "GRAPH" worksheet of the attached workbook.

View 2 Replies View Related

Locating Important Columns In A Table Of Raw Data - Setting Variables Using A Loop

Feb 22, 2012

I need to be able to locate some important columns in a table of raw data (the column locations are not fixed).

I would like to identify the locations (based on the heading values in Row 1) and store them as Public variables. The Match function works fine for this, however I'd like to make a simple loop to set these variables (opposed to repeating the function for each).

In the example below, I can't figure out how to reference "List1(Count)" as the name of the variable I'm trying to set.


Public Field1 As Long, Field2 As Long, Field3 As Long 'The column numbers will be stored here
Sub FindFields()
Dim List1(3), List2(3)
Dim Count As Long
'Public variables (declared above)

[Code] ......

So after running FindFields(), the Test1() macro should give "1 - 2 - 3" (for example) as the locations of the fields in Sheet1. But currently this doesn't work.

View 2 Replies View Related

Setting Cell To 0 If Nothing Is In Specific Cell

Feb 4, 2014

Basically it works out peoples hours

standard hours
overtime 1
overtime 2

because there is quite a lot of coding going on i was just wondering is there a simple bit just to add into each code that will keep the cell 0 until something is added into a different cell.


A1 = 00:00
A2 = 00:00

because of the formula A3 = 16.5

i need A3 to say 0 whilst A1 and A2 are at 00:00.

the Formula for A1+A2+A3 is perfect when something is entered into A1 and A2

View 7 Replies View Related

Search Only In Specific Columns (or Ignore Other Columns)?

Mar 25, 2014

Find button in Spreadsheet. My problem is I wish to only search 2 of the columns on my sheet.

I have been trying to locate info on my own, see here: [URL]

But I cannot seem to find anything showing how to specify where to search.

Here is the code I am using

[Code] ......

Want to search columns G and J -OR- Search entire sheet but ignore Columns A and B ...

View 2 Replies View Related

Copyrights 2005-15, All rights reserved