Advanced Filter To Filter Cell Comments With The Cell Values
Sep 23, 2006
I have many sheets which I am using the advanced filter to filter the data to a single summary sheet. Everthing works great with one exception. I have cell comments added with relevent info to to cell values. When the advanced filter is performed the cell comments are not transfered to the summary sheet with the cell values. Is there a method to filter the data to another sheet and keep the comments?
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...
I cannot work out the syntax to run an advanced filter for all Non-Blank Columns! I have attached an example sheet, and you can see my syntax in cell D4. I am looking to bring through a list of all line that are both Status= "NOT QUOTED YET" and Project Name= NOT Blank. Unfortunatley, the Status Row will be set as NOT QUOTED YET when there is no project name enetered, hence the problem.
I'm trying to set up a a spreadsheet where a user can enter search terms to filter a list of data. I'm using Advanced Filter so I can use wildcards and operators (>, < etc) and copying the results to a "results" sheet.
My problem is that some of the data entries have multiple values associated with them and this makes the filters values act funny. I have come up with two ways of setting out the list so far but both have faults:
Option 1: Use Alt+Enter to separate multiple values ie: Description Asset # Site Product Type Speed Comments
[Code] ....
I can filter this table for machines with a speed of 1000 using the advanced filter parameters:
Description Asset # Site Product Type Speed
1000
*1000
1000*
The user gives an input of "1000" and the spreadsheet automatically adds the wildcard * on the two rows below so that the advanced filter looks for Speed = 1000 OR *1000 OR 1000* (this collects the speeds 15001000 and 1000800) I would get this as a result:
Description Asset # Site Product Type Speed Comments
[Code] ....
The Problem: I can't think of a way to use operators like > or
I have a list of Window sizes and types in Range B4:B:43. The descriptions repeat because in Column K I am listing a location for each window in a house.
Example: B K 3050 SH 1/1 Dining Room 3050 SH 1/1 Kitchen 2030 Fixed Foyer 2030 Fixed Living Room
In the Same sheet Starting on B:45 I want a list of only Unique Window Types:
B:45 and Down: 3050 SH 1/1 2030 Fixed
I'd like this to automatically appear after populating the first list. I used an advanced filter the first time and it worked, but it is not reliable and sometimes returns duplicate values or give me an error message. Plus once again having it happen automatically as the list will change each time I access the sheet would be great.
I want to be able to filter so that the consecutive cells for x1 and x2 show up or red followed by blue. So when I filter it will only show rows 1 & 2 and 6 & 7. X1 X2
and there would be many of these values in the spreadsheet.
Column J1 is called: Resource Name which has 1,000+ staff other columns have corresponding Hours, Project names etc
I want an advanced filter where I select COLUMN J:J and filter this whole sheet based on say 25 names (in the format they're in) e.g
Frank, James Wilkonson, Paul, etc
In the Advanced Filter, Ive selected J:J as LIST RANGE, but how do I input an OR statement in the Criteria, as above i.e where name is Frank, James OR Wilksonson, Paul OR
Alternatively, I have the list of the 25 names in Sheet 2, can the Advanced Filter do a VLOOKUP then filter entire Sheet based on the names provided?
I have a condition in advanced filtering as >90%. However, I would like the "90%" to be calculated via a formula in another cell and this condition to reference it. Is it possible?
I have a file that has a column of dates. I would like to use advanced filter to filter anything with a date that is <today()-1 but I can't seem to make the fomula work. I can make it filter on =today()-1 but when I use the < it doesn't calculate today's date and gives me nothing.
Basically I have set up a list on sheet1, this is now on a drop down in sheet2 and my raw data in sheet3.
I want the table in sheet2 to just display the product items I select in the dropdown but it is displaying all the raw data (although is updating when I update the raw data)
Granted I copied the code whilst researching but I'm just not quite there.
Could someone possibly look at the attached and let me know where I am going wrong. Also as you can tell I am very new to teh VB application in Excel, could anyone reccommend a good place to start learning the very basics?
why the Advanced Filter works in the macro but not in the UDF?
Code: Public Function strUniqueVal(rng As Range) 'rng variable must include sheet name Dim c As Range rng.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
I have a worksheet that conatins multiple columns that are populated from a sql query.One column is a parts list.This has 1705 rows.
I have another worksheet that contains a list of parts in one column.This list is varying in row length.
I want to be able to produce a list that only contains matching data , including () around parts and if not too difficult an indication of data that is not in first list but is in second list.
When I do an advanced filter using the second list as the criteria i am not recieving all of the data from the filter , ie in the first list there is parts in (1234XYZ ) but in the second list the part is 1234XYZ , this part is not being resulted in the advanced filter unless I put brackets around the part in the second list . There is no way of knowing which part is in brackets in the first list.
I have a huge list (over 900,000 rows) of text codes (e.g. ABC-12345) on Column A Spreadsheet 1 and would like to remove the ones that are not included in Column A Spreadsheet 2.
I changed all of the text on Column A Spreadsheet 2 to have the in front of it (e.g. ABC-1234) and tried to run the advanced filter but it does nothing.
I need to lookup and concatenate all the header values where the value in the corresponding row equals a certain value. For example, column D, has the column header value if the cell values equals "X". Likewise column E has the column header value if the cell values equals "Y". I have too many columns (this is just a simplified version) to use nested if statements.
resource1resource2resource3 XResources YResources Jeff X resource2 John X X resource2 resource3 Jim Y resource1
I'm not an excel newbie, but I keep thinking there must be a simple solution that I am missing.
i am trying to work out the coinage for wages ie. 5546.75 needs to brake down into dominations ,notes and change i live in south africa so we use the desimal system. i have tried to use floor and mod as formulas but at some points it returns an error or incorect result.
I am trying to perform a function that searches through the first column for any active "A" account and then copies the corresponding rows information into another worksheet. I think I need to use the advanced filter function however I can't seem to figure it out.
I have a list of subscribers, each with an account id and the years for which they have subscribed. Each account id can be listed up to five times. I am trying to find out how to use advanced filter(or some other way!) to find those accounts that were subscribers in any of the previous four years but not the current year.
I want to create an advanced filter criteria along the lines of =AND(col1>begindate,col1<endate)
where 'col1' is the title in the cells at the head of the appropriate columns in the data range (B1), criteria range(F1) and extract area range (I1), and 'begindate' and 'enddate' are named single cells.
i am trying to use the autofilter command to paste 4 columns else where on the page using unique = true. this only will run once. when i try to run it again everything is blank. please see code below.
Sub copyindex01() Columns("L:O").Select Range("O1").Activate Selection.Delete Shift:=xlToLeft Range("G2").Select Columns("G:J").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Columns( _ "M:P"), Unique:=True Range("L1").Select End Sub
I created an advanced filter macro which analyzes the "Orig_Appmt_Data" dataset from the "Summary" worksheet and provides the resulting analysis on the "Effective Rate" worksheet. However, when I change the name of the workbook, the macro fails. I was hoping someone could provide some assistance in helping adjust the vba code to take into account the subsequent changes in workbook filename. Here is the
I am trying to create my critera section for an Advanced Filter, but it seems like Auto Filter uses each row as a specific filter.
i.e. If I need to filter all the "2"s in column A, but both the "1"s and "2"s in column B, I need to create two different filters like this:
Column A Column B 2 1 2 2
Instead of just:
Column A Column B 2 1 2
Is there any way I can use auto-filter without having to put in every possible combination of my criteria in as different filters? Auto Merged Post;I did try creating columns next to each other, but that seems to only work as an "AND."
Per my example, I need to return everything with column A = 2, and column B = 1 or 2.
Except in my actual spreadsheet, it's more like where A = {1,2,3,4,5} and B = {0,9,8,7,6} and C = {a,b,c,d,e}
I have a large spreadsheet of data, with various columns with different bank names, the amount (column H) each bank holds plus loads of other columns. Some of the banks are duplicated, so in a way I want to do an advanced filter to get rid off the duplicate lines, except that I need the amounts (column H) summed.