IS there a way to filter a dataset using the criteria in a hidden column? Auto-Filter will not allow this, and Advanced Filter is not user-friendly for the end-user of this file.
View 2 Replies
Data manipulation question here: how to dynamically filter and sort a multi-column data set? My end goal is to be able to (1) quickly collapse all columns into one single column, (2) remove all duplicates, and (3) sort the information in ascending order. A reference sheet is attached in case it's useful.
View 5 Replies View RelatedI am having trouble finding an efficient way to expand a set of data that I have by adding another column to it.
Look at attachment : help.xlsx
I can do it manually but I have 5000+ rows of data that I need to selectively expand do accommodate the data from the new data set.
I want to keep the raw data on one sheet, and have a graphical representation of that data on a second sheet. On the second sheet I would like the user to be able to filter the data on the first sheet which will update the graph as filters are applied.
Before I start trying to code something in VBA, is there a way to display the filter at the top of the data on the first sheet on my second sheet? So I would have an exact replica of the filter cell on the first sheet functioning exactly the same way, but on the second sheet?
I have a large itemised call bill that i need to do some regular analysis on and wondered if I could automate most of it.
In column C is a list of mobile numbers, in column F the numbers they called (this is an itemised bill so each line represents one call, meaning each number has multiple rows) finally in column K is the cost of each call.
I want the macro to look through column F (number called) and if there are less than 5 instances of that number that are under 0.30 each in cost to be deleted.
Example: if in column F the number 07500 100100 appeared once with a cost of 0.29 I want it deleted but if it appears 6 times with an accumulated cost of 3.50 i.e. more than 0.30 per call averaged out, then i want it to remain on the sheet
I currently use this filter to display rows with exact values as shown in this example:
Sub New_SheetUnpaidInvoices()
Application.DisplayAlerts = True
Worksheets("Closed Yr. 07").Activate
Columns("S:S").Select
Selection.AutoFilter
ActiveSheet. Range("$S$1:$S$1127").AutoFilter Field:=1, Criteria1:= Array( _
"BOB JONES", "JIMMY SMITH", "SUSAN LEE", "JONES SMITH LEE"), Operator:=xlFilterValues
End Sub
Sometimes, however, the worksheet cells contain simply combinations of last names like "JONES SMITH LEE" or other combinations. And, the last names appear in random order. So, I'd like to able to filter all rows that contain portions of the string without having to worry about the order and the exact string, etc. If the autofilter can return rows anytime it finds Jones, Smith or Lee regardless of order, that's be great.
I'm trying to use the advance filter for multiple criteria in one column. It works fine if the criteria is ="*10*" (ie where the cell in the range has 10 in the string) plus other = criteria. What I cannot get to work is where the range doesn't equal 10 in the string. I've tried ="<>*10*" and other variations to no joy.
View 4 Replies View RelatedI have attached two documents. One is called "Zone Destination" which is a template I designed. The other file is called "Schedule5_4" which gets downloaded from a work server once a week that contains all employees and their shifts for the entire week. What I am able to do so far is extracting the first row using index and match but I don't know how to extract any of the rows that follow. an employee might have several rows for one day based on a lunch or if they are working in multiple zones during their shift.
Zone Destination File -- start tab has the template in place that gets copied over when you create a new tab.
-- employeeroster tab contains the employee roster that i use to match with the schedule5_4 file. i changed the names and also reduced the amount to make it easier to read. i have over 80 employees but for this example, i only made up a handful.
schedule5_4 File -- this file contains all the data that I need to pull from. The criteria that I am using is by employee and date. I'm matching from the employee roster tab and also the date in cell a1 located in the zone destination file.
[URL]
I am recording and editing steps in order to procure unique ID's from a dataset and eliminate the manual intervention for this process. Macro works seamlessly in most of the scenarios except for the one mentioned below where it performs a step which is not really required.
Let me explain the scenario in detail:
-Sheet 1 contains the list of trades as per client's system and Sheet 2 contains the trades as per internal system
-Need to do several permutation and combination in Sheet1 in order to extract the unique ID's from Sheet 2
-Scenario 1 - lets assume I have 100 records I concatenate few common fields in both sheets and use the simple code mentioned below to fill the all rows with the lookup formula
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
First scenario itself might populate the unique id's for all the 100 trades or might give partial result which will prompt for the next scenario. If the first scenario generates the Unique ID's for 99 records I can filter on "#N/A" and update a different formula to fetch the unique id using different combination. However in the initial stage I wouldn't know the # of unmatched records because of which I have the standard selection till end and fill down codes in the macro, which would end up filling all the blank cells till the end, which takes unnecessary time.
simple validation code which ensures that if the # of unmatched record is more than 1 only then the selection till end and fill down codes comes into picture otherwise simply update the formula and populate the result.
In one sheet I'd like the user to select from 3 dropdown lists certain predefined values.
On the second sheet there is a long list of unique cells (one column, that can't be split into usefull columns with text to columns or something). I want to find the cell that holds the three text choices. These can be in different order to make things more complex. How do I create a search that finds that one match.
Example
The user selects "AAA" and "DDD" and "FFF" from the dropdown lists
The formula should find that one cell that holds this value: "FFF JJJ GGG DDD CCC AAA". This is the only cell that holds all three chosen values in one text. In the end I would need to have the row number of that cell,
I want this macro to run when I select sheet1 so I have dropped the code into sheet1. The macro runs fine as a stand alone but I get a 1004 protection error when placed into the worksheet module. Sheet "data2" is unprotected. I have tried "calling" the macro from the sheet1 module but it loops. I know it's simple but it's kicking my ***. data2 is hidden and workbook is unprotected as of now.
View 3 Replies View RelatedSo the find method works on visible and hidden rows depending if you use LookIn:=xlValues or xlFormulas; however, it does not work on hidden rows caused by a filter.
I have a range that is filtered, and I want to find a value in a column that may or may not be the filter criteria (and thus hidden). I do not want to show all the data, do the find, then reapply all the filters again. I could loop through the column, but I am looking to see if Excel offers a more efficient way.
I found a great bit of Advanced Filter code that works great, and fixed a problem of clearing a cell breaking the filter.
But if I want to increase the criteria from 1 row to 2, so you can start to include And , Or operations, it breaks the filter. Even an attempt at a manual one fails, until you put the criteria range back down to one row, then it's fine again.
I've tried changing the Target Row to >2 but that didn't work. how to make the criteria range bigger, and no problems of breakage if you clear the cells? It makes for a very useful automated Advanced Filter.
Here's the code :
[Code] .....
Database = the named area of raw data.
DATA is the name of the raw data worksheet
The criteria range should be AZ1:BC3, but of course royally breaks it...
Is there a way to provide filter with a list of criteria but when it doesnt match all of the criteria it still uses the filter on the criteria that it does match?
E.g i have this code
ActiveSheet.Range("$A$7:$N$31997").AutoFilter Field:=1, Criteria1:=Array( _
"A", "B", "D", "E", "H", "I", "R"), Operator:=xlFilterValues
However sometimes for example B will be missing, or H or B H I will be missing etc... is there a way to provide all of the criteria and it will not error if the criteria is not all there?
I am trying to develop a compliance report with Excel 2007) based upon a simple pass/fail criteria. The subtotals must be tracked both by Device (column) and by Requirement (row). There are macros (not included in the attached sample) that hides both columns and rows. I successfully found an example which I modified to correctly calculates data for a column when rows are hidden (see GOOD function below). However, I'm totally clueless on how to calculate data by row when columns are hidden (see BAD function below). What I'm trying to figure out is highlighted in red in the attached spreadsheet.
GOOD
=SUMPRODUCT(SUBTOTAL(103,OFFSET(B$2:B$5,ROW(B$2:B$5)-MIN(ROW(B$2:B$5)),,1))*(B$2:B$5="Pass"))
BAD (returns 0 and includes a circular reference)
=SUMPRODUCT(SUBTOTAL(103,OFFSET($B2:$F2,,COLUMN($B2:$F2)-MIN(COLUMN($B2:$F2)),1))*($B2:$F2="Pass"))
I have an excel file with a table in it. It contains 2051 records (attached). This is just a sample, the original file has around 30,000 rows.
When I start using filters, I run into problems:
Step 1: Filter by Unit, condition (e.g.) Unit_23
Excel shows in the status bar the following message: 437 of 2050 records found.
Step 2: If I scroll to the bottom of the table, the row numbers are colored in blue (normal for a filtered list) however the last row is not colored and it actually should not be shown since its unit is not what I filtered for (its unit is Unit_25)
Step 3: Clear the filter of Unit
Step 4: Sometimes (depending on what I filter for), one or more of the bottom rows are hidden!!!
Attachment 301726
When I hide columns in a column chart the different fill colors I used on specific columns no longer show up with the colors I originally had. Is there a way to maintain the proper sequence of column fill colors even when some columns are hidden?
View 1 Replies View Relatedis there a code to hide specific columns that i dont really need. i dont want to delete them, just hide them. even if i try to unhide them manualy from Format- Unhide they dont unhide. These columns stay hidden at all time.
View 2 Replies View RelatedI have a filter and need 2 (two) criteria.
1) In a (long) list of dates I need to see only the last month
2) From that selection (last month) I need to filter a date.
Note: I would like the filter to only show the last month as the list becomes very long.
Current solution
Column A has all the dates
Column B is a copy of column A
I use a small macro
Currently I got 1 vba coding which only filter with 3 criteria. Can I alter the vba to have 4 to 5 criteria in the filtering? Below is my VBA coding. Let say I want to add 1 more filter for column C2...
Range("A2").Select
Selection.Sort key1:=Range("E2"), Order1:=xlAscending, key2:=Range("B2"), Order2:=xlAscending, key3:=Range("A2"), Order3:=xlDescending, Header:=xlYes, ordercustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
I have a excel of about 5000 rows and 80 columns. Each row corresponds to a particular set of experiment. I want to reduce the number of rows by removing useless experimnets like controls etc.
I want to search the row for some key words(ex. control) and if it present then remove it. It should prompt user to enter the keyword and the original file should not be disturbed. It can be copied to 2nd worksheet.
I am trying to format all cells on all sheets (hidden or otherwise) as "Locked" so when the sheets are protected the user can't see the formulas. This macro individually selects every sheet in the book and applys the formatting. Is there a way to modify this code to accomplish the same thing without having it actually select every sheet? The only reason it is an issue is that after running the macro you end up on the last sheet in the book.
View 7 Replies View RelatedI have autofiltered a column to meet a certain criteria (which hides some of the rows), then I want to put a cell with the sum at the bottom of that column. When I do this, it takes the sum of all of the rows in that column, even the rows that are hidden. Is there a way to only take the sum of the rows showing?
View 5 Replies View Related1. find the last cell used in a column when the rows that contain the data are hidden. I tried using Range("A65536").End(xlup).Select. but when the rows are hidden it doesn't give me the last cell used.
2. I am trying to restrict my search to just one row. When I try using the cells.find function it looks for the whole sheet but I just want it to search for some particular data in just one row or a specified range.
Is there a way to lock a column hidden so that it can't be unhidden? Maybe with a password? I tried saving the file with a password but I could still go in and "unhide" that column.
View 3 Replies View RelatedIn the advanced filter, I am given two criteria one is filter by unit price by less than 1000 and order no atleast 100 .
View 6 Replies View RelatedHow to give a cell link instead of any value for filter criteria, For example I need the data between two date range, the first date is in cell A1 and second date is in cell B1 by using macro. I have tried by using below code also but after filter all data hiding.
Dim a As Date
Dim b As Date
a = Range("a1")
b = Range("b1")
Rows("2:2").Select
Range("A2").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:=">=a", _
Operator:=xlAnd, Criteria2:="
I have a Excel sheet which has a Product type values ASP,VSP etc in the first column. The second column has values Product Sub Types,which has values abc123, abc123456,abc123789, def123,def123456,def123789.The third column ServId's abc01,abc02,def01,def02.
If the Product Type is ASP,then I need values abc123,def123
But if Product Type is VSP, then I need values abc123
abc123456;abc123789
def123
def123456;def123789
I have a dataset, which I would like to sort on multiple criteria. The code I have is :
Dim Lst As Long
Dim Hdr As Range
Lst = Range("A65536").End(xlUp).Row
Set Hdr = Range("A6:AD" & Lst)
With Hdr
.AutoFilter
.AutoFilter Field:=9, Criteria1:="Complete" 'Match Status
.AutoFilter Field:=14, Criteria1:=Array ("Pending", "Technical", "PR", "Regional", "=") 'Case Study Status
End With
Field 9 Match Status will always be Criteria "Complete"
Field 14 may contain different variables depending on the dataset. I want the filter to bring back all Match Statuses (Field 9) "Complete" and multiple Case Study Statuses in Field 14.
This code filters correctly on Field 9, but is only bringing back blank cells in Field 9 when there is data that has Case Study Status "Pending", "Regional" and "Blank".
I would like to filter a row using the following criteria: date should be < or = todays date.
I have tried the below but it doesn't work
Selection.AutoFilter Field:=11, Criteria1:="
