Wildcard Filtering/sorting
Feb 5, 2010
We have an ISP based antispam system which can sometimes incorrectly pickup messages as Spam. I would like to scan through all quarantined messages every week to check that this does not happen. I can export a report from the program to excel but what I would like to do is remove subject lines with common spam words (Viagra, Russian etc) so I don’t need to check these, this would in effect reduce the report by 85%.
Ideally the ‘spam word’ list would be on the second sheet as in example and could be added to as I find obvious words.
The ideal end result would either be all lines which have a word from the ‘Spamwords’ sheet are removed or all lines which do not match the words in the spam list are copied to a new sheet.
View 8 Replies
ADVERTISEMENT
Nov 29, 2007
in the table i have
A1-apples
A2-bananas
A3-eggs
A4-balls
A5-bananas
A6-apples
A7-apples
I need one list that will show me all items that are on list so i would get:
A50-apples
a51-bananas
a52-eggs
a53-balls
to simpilfy this list would show me what types are there in the first list
View 9 Replies
View Related
May 30, 2009
I have a list with rows containing NAME, CLUB and TIME (A5:C124).
I'd like to be able to create a new list which would contain the fastest 3 TEAMS along with the combined time (SUM) of the fastest 3 times for each CLUB. Not all CLUBS would have 3 entries and these would need to be excluded.
View 10 Replies
View Related
Jan 14, 2009
I need to do sorting and filtering the similar records to new worksheet. I need you help to do this job, give some idea or command to use for the following steps.
I have attached the worksheet newtran.xls.
1. I need to find the last column, the column will be increased or decreases. so that we need to find the last column at time of running the macro. Now the value is there till column DD
2. I need to concatenate the column B, D, F to last column find out at 1 step. Insert a new column and place the concatenated value.
3. Entire row will be Sorted based on concatenated value column.
4. Find the similar row on concatenated column and entire row need to be moved to new worksheet.
View 11 Replies
View Related
Dec 7, 2013
I have an excel database that contains a code to identify specific people.
NAME ADDRESS PHONE CODE
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
Nov 1, 2008
Background: I am HR manager for a construction company & keeper of the call-in list of personnel who are looking for work. I have a simple sheet that has columns:
Date Name Craft Experience ...more info...
If each call-in had only one craft, wouldn't have a problem. Those who are multicrafted ar listed e.g. "EL, MW, BM" In the column C. A caller two days later may be listed as "MW, BM, EL" We input the data as they say it since that is usually their order of expertise. (Yes, I know that it should have been set up with each craft having its own column, but I inherited the sheet & it has 4000+ entries)
I wrote a couple of small macros & assigned buttons on the sheet to allow the users to sort the sheet by date, or name, or craft. My customers (project managers) have requested to be able to sort by craft but have all the folks with any specific craft listed together.
Example (Excel 2003): ..
View 4 Replies
View Related
Apr 23, 2013
Here is the original table:
This is with a filter on:
You can see there that some inserted object (in this case, PDF files shown as icons), are moved. I need to find a way to immobilize every inserted object within each cell boundaries. I want to freely sort or filter and avoid this kind of problem that gets worse with more rows, columns and inserted objects.
View 1 Replies
View Related
Jan 30, 2014
I am using Excel 2007 and I have a worksheet with multiple columns, some of these columns are protected and some are not.
I then lock the worksheet with a password so the user can't edit certain cells that are protected but can modify the unprotected cells, the problem is the user cannot use the filter or sort the workbook.
Is there a way to allow filtering or sorting but still lock down the worksheet.
View 3 Replies
View Related
May 27, 2014
I have a very big Excel file (62 MB). I need to be able to filter by one column (FACILITY) and sort the whole file using the (REGISTRATION NO) column as well as finding the missing sequence number in the (REGISTRATION NO) column. The problem is that the (FACILITY) column has more than 200 different facility name. I am thinking of macro as I have different Spreadsheet I need to do the same steps in each one separately.
I have attached a copy (example from the data that I have) .
View 14 Replies
View Related
Feb 21, 2009
Have a cell that says something like "123 main street".
I want the cell next to it to = Main if it detects main in the other cell and if not then it should say no. I tried this.
=if(B2="*main","Main","No"
However, it is not working.
View 9 Replies
View Related
Jun 7, 2006
I transferring data from the mainframe and some data has ????. I can not replace them with something else because excel think it is wildcard. The reason for this request is I was trying to count the number of cells that has ???? and the count was something different each time. If you want to have fun try it out. It will count ?, ??, ??? but not ????.However my question would be how to change this (????) to some thing like unknown...
For Each rngC In rngJbn
rngC = Trim(rngC)
If rngC.Value <> newvalue And rngC.Value <> "ab-" Then
'MsgBox "****" & rngC & "***"
Résultat = Application.CountIf(Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible), rngC)
MsgBox rngC & " " & Résultat & " " & i
End If
newvalue = rngC.Value
Next
View 6 Replies
View Related
Nov 13, 2008
Is it possible to use the wildcard in an IF formula? For instance, I have a spreadsheet with text and numbers in the cells, the number of characters varies in the cells so a left, right or mid is out. I want the formula to look to the cell and if any part of the text contains "ABC" then I want to return 1. =IF(C18="*ABC*",1,"") Cell C18 containts the letters ABC in order but my formula is still returning blanks?
View 4 Replies
View Related
Aug 3, 2013
The idea
[Code] .....
Dropdown list
-------------
O11 = "Land" , "Sea"
P11 = "A - H" , "I - O" , P - Z"
Directory = C:MammalsLandA - HDate_120313 - Carnivore - Code_A3622
The list of filenames in the "A-H" folder has different dates and codes, how do I go about fixing wildcard *Carnivore* so that regardless of the date and code, there will be a match.
list of files in A-H folder
------------------------
Date_120313 - Carnivore - Code_A3622
Date_030213 - Herbivore - Code_A2231
Date_051212 - Hybrid - Code_A4431
View 10 Replies
View Related
Jun 23, 2014
How to use this wildcard in a IF statement.
[Code] ......
I have a column I'm adding a value to another cell if a piece of text is found in the column I'm looking in. The * is not working at all.
View 2 Replies
View Related
Aug 28, 2007
The use of * as a wildcard in excel can be extremely useful, however, can it be turned off? I work in the education sector and am currently number crunching GCSE results. When it comes to calculating A* grades versus A grades, B grades, etc, I cannot seem to do it because of the wildcard.
View 12 Replies
View Related
Dec 7, 2007
I have tried everything I can think of to get this to work.
I am searching column B for anything containing "Tease", then if found, look to see if column J contains a "W".
Excel doesn't report an error, but this doesn't work:
=SUMPRODUCT((B3:B40="*Tease*")*(J3:J40="W"))
View 10 Replies
View Related
May 1, 2009
Hi I have this formula:
View 3 Replies
View Related
Oct 31, 2011
As far as I know, a wildcard can't be used in an "if" statement. I have a list of "job names" that vary slightly from time to time. When we use a Bunnings "(building account) people give a job name such as "121 William Drive". Sometimes however, the person in the store spells it incorrectly. Is there a way to find a wildcard in the following manner?
Find any word that begins with "121" and ends in "ve"
In a1 it says: "121 wilaim jnes drive" I have tried the following formula in b1:
=IF(a1="121*ve","yes","no")
Why does it say "no"? Is there a formula for using wildcards in an "if" statement?
View 2 Replies
View Related
May 15, 2012
Trying to count how many cells in column b contain a value.
using
Application.WorksheetFunction.CountIf(Range("B:B"), "" * "")
getting a type mismatch error.
View 2 Replies
View Related
Mar 27, 2014
I was wondering is there away where i can lookup A:A anything begging with Ves* ? and then i need to copy the formula down to pick up the next one in the list
im guessing something like =VLOOKUP(A:A,"Ves*") but i havent got a clue
View 1 Replies
View Related
Feb 18, 2009
In a recent thread, I learnt how to import HTML, using ADO into Excel.
To import the relevant variables, my code is as below: ...
View 9 Replies
View Related
Jan 7, 2014
I need to lookup data from four different columns to match a value in another sheet. The lookup value has to start with "4" in order for it to be the correct match. I've tried the following formula for just two columns:
=IF(VLOOKUP($C2,'Sheet 1'!$D$2:$H$10000,5,FALSE)="4*",VLOOKUP($C2,'Sheet 1'!$D$2:$H$10000,5,FALSE),IF(VLOOKUP($C2,'Sheet 2'!$E$2:$G$10000,3,FALSE)="4*", VLOOKUP($C2,'Sheet 2'!$E$2:$G$10000,3,FALSE),"False"))
The problem I have is that the wildcard function isn't working in my If statement (apparentley that's how Excel was designed). The first vlookup returns the value "40042565" but Excel doesn't show it matching the criteria with the wildcard ("4*").
Any way to lookup the value in C2, search through multiple columns, and return the value that starts with "4"?
View 3 Replies
View Related
May 14, 2009
I've this question, how do i extract a part of the text using wildcard(example "ID=") in the string in the whole columns and copy to other columns or delete the unwanted.
Below is a example which i create for better understanding.
View 11 Replies
View Related
Feb 8, 2009
I'm trying to use a "WildCard" in an Array Formula to both simplify and handle variations in several formulas.
My work sheet is used for Time Cards. I have a tab for each of the 52 weeks in the year and each tab is labeled "W1", "W2" for Week 1, Week 2, etc.
On my SUMMARY tab, I'm using an array formula that looks at cell "'SUMMARY'!D6" for a project number, and than searches through all of the Week Tabs for anyone that has charged time against that project.
The employee would enter the project number in D12:D35 of a given weeks tab with each row representing a different task for that week. In some cases they may have several rows for the same project number.
On the SUMMARY tab, the formula for Week 1 is in cell E6. For Week 2 it's in F6, etc.
I was thinking I could simply place the sheet tab name in the row above (i.e. D5, E5, F5, etc., and then write the formula for 1 cell and copy it to all of the other cells on that row.
Currently my formula looks like:
{=IF($D$6="","",SUM(IF('W1'!$D$12:$D35=$D6,('W1'!$N$12:$N$35))))}
I was thinking the formula might look like:
{=IF($D$6="","",SUM(IF('&E5&'!$D$12:$D35=$D6,('&E5&'!$N$12:$N$35))))}
But that isn't working.
Additionally I was hoping to do something similar on the SUMMARY tab by referencing a different spreadsheet with something like:
='[&A1&]Sheet1'!$B$51
Where cell A1 has the value "Time Sheets - 01-17-09.xls"Currently the formula looks like:
='[Time Sheets - 01-17-09.xls]Sheet1'!$B$51
But this isn't working either.
View 10 Replies
View Related
Mar 31, 2009
I've been looking around for a way to use the countif function in excel with a wildcard, I have a excel list with a column containing data which looks like: ....
View 12 Replies
View Related
Nov 5, 2011
I am trying to return how many cells contain a string of text as entered by the user but I am struggling to put wildcards around my search term. Below is the code I have so far.
Code:
Dim search as String
Dim occurance As Integer
search = InputBox("Search for", "Search Term")
occurance = WorksheetFunction.CountIf(Cells, search)
MsgBox (occurance)
View 2 Replies
View Related
Dec 29, 2011
I have a spreadsheet where there are lines of different Wage Types but only certain combinations are needed. For example I have Pay Area 1234 which needs all wage types except those beginning with a 9.
To do this I have set up Sheet1 to look like this:
12341
12342
12343
12344
12345
12346
12347
12348
and not 12349 as I do not require this type.
The problem here is I need to add a wildcard to the end of these so it finds anything that starts with 12341 etc.
Sheet1 contains the personal details of employees and I have concatenated the Payroll Area and Wage type in column D.
Code:
Sub deleterows()
Application.ScreenUpdating = False
Set MySheet = Sheets("data")
LR = MySheet.Cells(Rows.Count, "D").End(xlUp).Row
For i = LR To 2 Step -1
[Code]...
View 9 Replies
View Related
Jun 20, 2012
I maintain a control list of all properties held by the company. Each month I have to manually review all property-related transactions (see transaction description column) and input in column "Key 1" the property it relates to by reviewing the data in the transaction description and finding the property in the control list. Is there a way I can automate this process with a forumla?
some key points:
- the name in the control list can appear at any positiion in the transaction description
- various transactions relating to the same property will appear in the transaction description listing many times and key 1 will need to be completed against each entry
- if there is no match between the transaction descripton and the control list then the relevant cell in column "Key 1" should show as "Not Found"
Control List
Transaction Description
Key 1
Bligh
[Code].....
View 5 Replies
View Related
Jan 31, 2014
I would like to Vlookup an association of data in a matrix where not all information is available. e.g:
Vlookup of following data (concatenate):
A1&B1&C1&D1
A2&B2&C2&D2
A3&B3&C3&D3
but in the mapping I would have a wild card (*) in certain columns (not always the same column)
The concatenate would be for exemple:
A1&*&C1&D1
A2&B2&*&D2
*&B3&C3&*
I know it works on the other way around (Vlookup of concatenate with wilrdcards) but I can not have wildcards in my source data.
View 7 Replies
View Related
Mar 7, 2007
Currently I have some code in a macro that opens a file like so -
Dim theDate As Date, thePath As String, wb As Workbook
theDate = Sheets("ActiveDon").Range("Eventdate").Value
thePath = "C:PROJECT" & Format(theDate, "MM-YY") & " xxx Data.xls"
Set wb = Workbooks.Open(thePath)
This works swell if the file is, say "01-07 xxx Data", but in truth the xxx is variable (but there's always only one). I'd like to make
thePath = "C:PROJECT" & Format(theDate, "MM-YY") & "*wildcard*" & " Data.xls"
so that it will open the file whether it's called "01-07 CA Data.xls" or "01-07 MA Data.xls", or whatever.
Is it possible to use wildcards to do this?
View 9 Replies
View Related