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
ADVERTISEMENT
Feb 24, 2013
I write a excel file (with macros included) to tackle the datas regarding stock market.
The excel file encompasses some information about financial reports, and some functions (macro) like sorting and filtering.
I'm wondering if I can build it as an windows 8 App and put it on the App market.
Or, in other words, can I make a windows 8 App by the functions in Excel ?
Can I do that ?
View 1 Replies
View Related
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
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
View Related
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
Jun 11, 2014
I am running a auto filter macro to I need to change it to only copy column A:B and past in column C:D in the sheet called "Diary". How to change my current macro to only select column A:B and paste it. It is currently pasting the entire row.
Here is the part of my code:
[Code] .....
View 1 Replies
View Related
Nov 29, 2006
I have a large worksheet in drive C: called 'hits.csv' that has manufacturer part numbers in column G and column L.
I need macro code to find all rows in the worksheet where the part number in each cell in column G is exactly the same as the part number on the same row in the corresponding cell in column L. The macro should then delete/remove all rows where there is no exact match.
Note: The part number in every cell in column G is the only thing that is in each cell in that column. However just to warn you that the part number in every cell in column L is at the beginning of each cell, followed by a space and then a description of the part.
Once it has done the above, I would also like this macro code to open a worksheet called 'partnumbers.csv' in drive C:. It should then compare the part numbers in column A of this file with the part numbers in the above worksheet ('hits.csv'). The cells in Column D of the file 'partnumbers.csv' all contains a price. Where the macro finds an exact match on the part numbers in these 2 files, it should copy the price from the cell in column D in the file 'partnumbers.csv', into Column K of the other file, 'hits.csv' where the match exists.
View 9 Replies
View Related
Oct 1, 2008
i have created a spreadsheet to simplify our work flow, I am stuck on what is probably the easiest of the commands.
basically have rows dedicated to specific codes and the colums represent values relating to each code, all codes have a different set of values, the attached example only has a few variables but the actual worksheet will have several hundred.
the idea is the user will input the code they wish to get details on in A2 and then press the command button and it will then show (as per the after sheet in the attachment) just the relevant information for that code, so filter the code in column A and hide the columns which hold no value.
where i am getting stuck is I am not sure the best way to proceed, is it best to create the macro button to do the filter and hide or is there a better way using vlookup and a pop up window asking for the relevantcode to be inputted to to retrive the information, again understand there will be hundreds of colums and hundreds of rows and the values may be 20 or 30 colums apart for some of the Codes so this simplification is really saving the user a lot of time.
View 7 Replies
View Related
Sep 20, 2009
I am trying to find the sum value of a range, specified by two variables.
ie. what is the total spend in 2007 for Hong Kong?
I think I have (with the help of Andy Pope) resolved how to distinguish the correct figures which I wish to summate using with the following
View 7 Replies
View Related
Oct 16, 2011
Objective: to create an average variance among exact match RevCode
Example: all RevCodes with "666" should be used to make an average variance statistic
Situation: All RevCode's are not in similar group counts and this creates incorrect averages with a standard formula.
Set of Data
RevCode Observed Expected Variance RevCodeMean
666 200 220 -9.091%
666 205 220 -6.818%
666 207 220 -5.909%
777 500 355 40.845%
767 505 505 0.000%
668 100 105 -4.762%
668 105 105 0.000%
Formula that generates average variance between exact match revenue codes:
=(SUM(B2:B4)-SUM(C2:C4))/SUM(C2:C4)
The solutions can be in VBA or within the spreadsheet either way.
View 1 Replies
View Related
Jul 14, 2009
I have a very large workbook which includes 100 sheets (A) each containing fixed data relevant to one unit (n) (in this case n = a building; there are 100 buildings), a few sheets (B) containing parameters & settings, and then 1 main sheet (C) which does complex calculations pulling & combining data from sheets (A) based on the parameters in sheets (B). The workbook is so large, (100MB+) that it has to be set to calculate manually.
One of the settings in one of the sheets (B) dictates which building (n) is currently being looked at. As such, sheet (C) effectively builds a report for building (n) and so can report on just one building at a time, that building (n) being determined by a drop-down box listing all of the available buildings and that list is in turn defined in one of the sheets (B).
If I want to get a consolidated picture for all buildings I currently manually change the building choice (n) in the dropdown box, and then run a macro (m1) which updates the report to show what I want, selects the specific bits of the report I want and copies the selection, switches to a separate pre-formatted consolidation sheet I have created (D), jumps to the last row in that sheet, pastes values and then jumps back to Sheet (C).
I then need to manually change the selection (n) and then rerun macro (m1).
What I would like is another macro (m2), which automatically loops through the different values for (n) so that the whole process is automated. Then in a perfect world, perhaps even a separate macro (m3) which would give me a list of tickboxes so that I could select specific properties for which to run the report.
View 9 Replies
View Related
Oct 2, 2006
I have recorded certain macros. But after executing macro 'Undo' do not work. How can I do it ?
View 3 Replies
View Related
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
Mar 7, 2008
I have a excel worksheet with the following columns: First name, Last Name, Email address, domain of email, product type, date registered. The list consists of about 50,000 entries. I want to sort the list by the domain of email(which I am able to do already) Once this is done, I want to find all instances of where a domain appears at least 10 times on the list(such as webmessenger.com appears 40 times, so I want to get that data).
For those instances where the domain appears at least 10 times, I want to pull those rows out of the intial list and put them in a new list(the new list will be sorted by domain and will only have people who have a domain which appears at least 10 times). To make this a bit more clear, The initial list I have is a list of people who registered to use the software my employer makes. We are trying to locate companies which may have many people using our consumer version of the software. When there is a large amount of people in the same organization using our software, it would benefit them to upgrade to the enterprise version due to enhanced managment features. By running this filter, I can see which companies have at least 10 users registered to use our software. Of course I will remove any Gmail/yahoo mail/msn/hotmail... pretty much any public email domains and just leave the ones that are obviously corporate emails.
So far, I think it probably has to be done with a pivot table... I was able to get a table that tells me how many instances occur from each domain, but I cannot get it to display the actual data(it just says IE. company.com 200, yahoo 120, etc... I need it to show me the 200 rows of company.com emails and extract them to a new sheet so that I can then follow up with company.com and see if they are interested in the corporate version.)
View 2 Replies
View Related
Apr 16, 2013
I am using Excel 2010. I am a novice user.
I have a lot of data to filter / sort. I want to initially to create a filter for a column of data - which has the format similar to hierarchical paths to files. The data is a mix of text/numbers. e.g.
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_254__5_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_253__5_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_255__5_0/d
[Code] .........
Doing an alphabetical sort of this date would return the following order. As you can see while each strings in unique - there are many instances where they are simialr - if you ignore the unique numeric values at the end of the string.
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_4_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_230__6_0/d
[Code] ......
So what I want to do is to create a filter for the strings - but ignoring the numeric bits at the end i.e.
reg_[0-9]+_+[0-9]+/d
The strings are obviiously of varying length and the number of hierarchical paths is different, so I can't split string on "/".
Similarly folder paths names can contain "_" so can't split string on this either.
As I don't know how many "/" or "-" instances there will be in the string I don't believe I can use the find function. Also as the amount of number will be different i don't think I can use =right(a1,X) either.
I may be able to search for the pattern above - as this is probabay unique - so maybe it's something like the following pseudo code:
Function GetString(txt As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "reg_d+(_)+d+//d"
GetString = .execute(txt)(0)
End With
End Function
If I do require VBA code - how do I then use this for creating a column filter? Or will I have to extract the filtered data first from the column (and its associated row data) into another worksheet to use?
Once I have the filter in place I want to create tables using the filtered data - so for example each column value above has a lot of associated data values in each row e.g
26 pathA/path_123/path_456/data_out_reg_0_0/d
32 pathA/path_123/path_456/data_out_reg_17_0/d
8 pathA/path_123/path_456/data_out_reg_4_0/d
So my table would show the name "data_out_reg" and the range of values 8-32
View 1 Replies
View Related
Jan 13, 2013
I am trying to build a macro using two files. The file 'master data.xls' has student details in a table containing 10 column heads as attributes (roll, name, address, email etc). The file 'Student Profile.xls' has been formatted to show individual student details in the form of a report. I want to prepare a macro such that each time I enter a new record in the "master data.xls", the data from new entry is printed in the format as given in "Student Profile.xls" as a pdf file which is saved with a file name defined by "(roll number) (First Name) (Last Name)" and the same is emailed to the student's email ID as specified in the "master data.xls" with the mail containing an address to the student name (ex: Dear Tom) and a standard text message in the body.
View 1 Replies
View Related
Jun 6, 2014
I have a workbook has a "Summary" worksheet. I would like to be able to do the following:
(1) Sort columns D, G, J, M,... (the number of columns are different each time I run the macro that I have).
(2) create a combo box to be able to the data (that sorted in 1) by month (1,2,..., 12).
see the attached file
View 5 Replies
View Related
Feb 20, 2013
I have 2 worksheets. Sheet1 has a list of data (Range = "ClosedOpps"); Sheet2 has a Region dropdown list with Domestic and International as choices. What I would like to happen is when I pick a value in the dropdown list, ClosedOpps list filters by that value. Region is the 5th column in Sheet1.
View 1 Replies
View Related
Nov 7, 2012
I'm trying to build a macro that iteratively runs a goal seek, then reduces a certain value (Let's call it "Bogey") in a different cell by a small amount (note that changing Bogey will affect the desired goal seek value), then runs the goal seek again, reduces Bogey by a small amount again, etc etc, and keeps running in this loop, with the restriction on this loop being that a different cell, affected by Bogey--let's call it "Constraint"--doesn't drop below a certain amount.
View 5 Replies
View Related
Nov 15, 2012
On sheet1 I got a lot of columns with data, starting from row 8, which is the header.
On sheet2 I'd like to retrieve the filtered data, based on the value mentioned in cell A1 (on sheet2)
So if for example cell A1 on sheet2 shows "Peter", the code should filter the data on sheet1 with "peter" in the header.
The filtering should be based on cell color (red in this case)
The filtered data should then be copied to sheet2 starting from A3
View 3 Replies
View Related
Feb 25, 2014
I work on an excel spreadsheet all day and I'm constantly cuting and pasting a value to filter another column. I would love to have a macro button that would automatically do this.
The job card sheets are labled 'Page 1' through to 'Page 175'. As I am working on each job card sheet I need to filter column on another sheet within the same workbook. This is indicated on the attached picture.
So in a perfect world as I would enter in the PLANT ID number on to any job card, then hit a macro button on the toolbar and the Pole No column would then be filtered by the value in the PLANT ID cell.
As there are multiple job cards the macro would probably need to use the 'active cell' value to filter by. But as you can probably tell I'm no expert so you judge the best way to do it.
MWTS034G22 Job Card Sheet
[URL] .....
Windows 7 (32bit)
MS EXCEL 2010
View 2 Replies
View Related
Apr 24, 2014
I'm trying to add a line of code to filter some data based on a date in the range cell C3.
This is my code;
[Code] ....
It doesn't seem to work.
View 4 Replies
View Related
Feb 25, 2007
I have a list of items in column A. And a "flag" in column B. I have attached a small sample. What I need to be able to do is "Hide" all the items in column A where there are no flags for that item in column B. This I can do. The problem is that if a flag shows for an item, I want to show ALL of the rows containing that item, even if there are no flags for some of them, and hide the rest.
View 3 Replies
View Related
Aug 27, 2009
I have a workbook with two spreadsheets, one blank and one with a Header row and a list of employee names and random dates that will continue to be added to at the bottom per a copy and paste.
What I need is to have a form dropdown on the blank sheet (per a cell reference) that shows employee names and dates that has that name next to it in the data sheet.
The employee names are in three columns Last, First, and Middle and the dates are in a separate column (A).
I guess I need a VLOOKUP that can take the range and pull into the dropdown the employee name and every date associated with that name.
View 10 Replies
View Related
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
May 2, 2014
I just want to filter and sort the data based on names and hours.
Here it goes:
1. Filter Column F.
2. Filter it by: UK & M UK
3. After that, filter Column E in "Ascending order"
4. Count the data that are:
-starts 1:01AM - 7:59AM and display the total count in Sheet2, if there are no data in other specific time, then display a value "0"
For example for UK and M UK:
In Sheet 2, ("F5") = 3 as there are 3 data, G5=1, H5=0 and so on and so forth..
I've attached my file : Filter and Sort.xlsx
View 3 Replies
View Related
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
Apr 17, 2014
I would like to build a graph that compares 2 sets of data but only plots 1 dot where the two intersect. I have seen this graph made by others in Excel but I do not know what it is called or how to make it. An example data set is below.
ID Age Height (cm)
1 5 125
2 7 140
3 8 138
4 11 152
I would just like to plot the location where the age and height for the ID's cross and not 4 plots with the age and 4 plots with the height.
View 1 Replies
View Related
Aug 12, 2014
I am trying to build a spreadsheet containing data validation (drop-downs). So far, so good.
My goal:
* I want to have various cells show dropdowns based on the value of a cell (per row) which itself can be changed by the user using a drop down.
* I want to have the same various cells show dropdowns that are also based on the value of a cell which has a fixed value per column (in other words, a header).
I have tried a lot of different approaches, including using the INDIRECT function, named ranges, dynamic ranges using a table, INDEX and MATCH. All have failed.
The main issues I see have to do with the fact that my data has several possible values for each given header and classification. Also all values are not unique.
The best result I have ever got returned a dropdown list based on the header bot returned the entire column rather than limited to the classification values. So for example I had all instances of 'Main Colour' to choose from, not just those of the classification value.
View 2 Replies
View Related
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
