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.
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.
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")
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?
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.
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?
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.
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.
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
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.)
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.
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.
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:
B B B-A B-A B-V B-V
Column B contains: THR THR-MATT HF-MATT HS-HS-THR HS-MATT
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.
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).
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:-
ABC http://192.168.44.29:2967 DENIED
BCD https://d31qbv1cthcecs. atrk.js DENIED
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 http://192.168.44.29, http://172.25.5.107/is Private IP so I need to ignore it but http://188.8.131.52 is public IP and we need to copy to other sheet.
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.
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)
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.