Filter Under Multiple Characters?
Nov 26, 2013
I have a spreadsheet containing 5 columns of text, I need to identify any that contain any of the following characters;
` ~ ! @ # $ % ^ & * [ ] | } { " _ ; < >
Is it not possible to do this via either a custom "contains" filter or Find/Replace without doing it one by one ?
This is something i will need to do monthly as a data cleanse activity, and ideally i need it to be quick and allow me to see quickly out of a large list which contain these characters in the form of a filter or highlighted cells.
My excel knowledge is limited but if this isn't possible via custom filters, how i can create a macro that will search for the above characters and highlight any cells containing them ?
View 6 Replies
ADVERTISEMENT
Mar 27, 2008
excel filters.
I have excel document over 50 pages and i need to filter lines that begins with certain letters like ADS, SGH, FAQ. I know how to filter one by one but i need all 3 to filter in same time-
How can i make multiple lines to filter in one search?
View 8 Replies
View Related
Nov 20, 2006
This may be a very simple question so forgive me for my ignorance. I have text in individual cells that look something like this (not actually addresses but same format):
Doe, John – 123, Anywhere St (Apt A), Anytown Anystate 12345
I have about 5,000 records. I would like to convert the records to look like this:
Doe John 123 Anywhere St Anytown Anystate 12345
Basically I want to take out all non alphanumeric characters and anything between curved or square brackets. In my minds eye my macro would read something like this:
Do until last character.
If character = alphanumericTrue – Move to next characterFalse – If character = spaceTrue – Move to next characterFalse – If character = curved or square bracketTrue – Delete all text in brackets including brackets then move to next characterFalse – Delete character then move to next character
Loop. I would of course create an additional loop to run down the 5,000 records.
View 6 Replies
View Related
Jan 15, 2008
Working on a macro to replace a list of about 20 specific "Special" Characters in excel, and have ran into 6 that will not work.. Following are the characters: ā, č, ć, ř, ş, ż
ActiveSheet.Select
Selection.Replace What:="ä", Replacement:="a", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Replace What:="á", Replacement:="a", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
View 5 Replies
View Related
Jul 2, 2014
As I know we can use curly braces if the same cell contains multiple characters. e.g. A1={"XY","WZ","YY"}
How about if multiple cells contains the same characters?
View 1 Replies
View Related
Dec 10, 2013
The following code works fine to determine if a particular character occurs within the selected range of cells:
Code:
Sub CheckIfCharacterIncluded()
For Each MyCell In Selection
If InStr(MyCell.Formula, "#") Then
MsgBox ("The " & "#" & " character was found in cell: " & MyCell.Address & " at position " & InStr(MyCell.Formula, "#"))
End If
Next
End Sub
However, I would like to extend this functionality to check for multiple characters, using some sort of array that contains all the characters I want to check for e.g. "#","*","£" and so on, without having to repeat the above code for each character for which I need to check.
View 2 Replies
View Related
Apr 29, 2014
I've got a long list of account numbers of varying length and ultimately need to add a dash and a dot into these numbers. Here's an example
12345678 to> 1-2345.678
123456789 to> 12-3456.789
1234567890 to> 123-4567.890
The standard format is always 3 numbers after the . and 4 numbers between the - and .
I can get to this by doing a series of functions starting with this:
=LEFT(A1,LEN(A1)-3)&"."&RIGHT(A1,3) to get 12345.678 or 123456.789
copy/pasting that value into another field and then doing this
=LEFT(E1,LEN(E1)-8)&"-"&RIGHT(E1,8) to get the results above
I can't quite figure out the format to combine the multiple steps/functions into one so that I'm not copy/pasting values and re-doing the function.
View 7 Replies
View Related
Mar 18, 2014
I have a spreadsheet I work with which contains a formula that needs to take an item ID letter/number combo, substitute the preceeding letter(s) with leading zeros (if the initial numerical characters are fewer than 5) to make the result return a 5-digit number.
The original ID can be in several possible formats (beside each I will post the desired result):
V7684 07684V366 00366V88 00088V60827 60827VS57871 57871VS67 00067
I have the following formula which works like a charm for all except the last example:
=IF(LEN(K2)>5,(SUBSTITUTE(SUBSTITUTE(K2,"VS","),"V",")),(REPT("0",5-LEN(K2))&(SUBSTITUTE(SUBSTITUTE(K2,"VS","0"),"V","0"))))
If the first 2 characters are letters ("VS") and there are fewer than 5 numerical characters, the formula results in a 4-digit number, rather than 5. Using the above example, VS67 returns 0067 instead of 00067.
P.S. I just tried
=IF(LEN(K2)>5,(SUBSTITUTE(SUBSTITUTE(K2,"VS",""),"V","")),(REPT("0",5-LEN(K2))&(SUBSTITUTE(SUBSTITUTE(K2,"VS","00"),"V","0")))) which only works when there are 3 or fewer numerical characters.
View 6 Replies
View Related
Feb 9, 2013
The problem - I'm trying to find a formula that will find text between 2 characters with unlimited instances in a single string and combine the found text to a single string. The character enclosing the text i'm after will likely be a % symbol. This formula would then be replicated down 5000-10000 rows.
eg. 1 - This is %an% example %sentence% to show what I %am% looking for
eg. 2 - This %is another% example of what I %need%
eg. 1 answer - ansentenceam
eg. 2 answer - is anotherneed
Ideally this would not use a macro as it will be applied to an ever expanding data set but I realize that may not be possible.
View 3 Replies
View Related
Oct 14, 2009
I have a list drop down that is dependent on a first list. The first list has numbers, spaces, and "-" at the beginning I need to get rid of to make it a valid name to reference. The "Substitute" function can't be nested enough times to make this work for me since I have a fairly lengthy list for the independent column which has differing numbers at the beginning. The first two examples of the independent drop down (which would dictate the second dependent column and drop down) are:
00 - Preconstruction
01 - General Conditions
I would like to name these something like "Preconstruction" and "GeneralConditions" for valid naming convention.
Second thought:
If character removal isn't the most efficient or possible at all, is there a combination of reference functions that could make this work? Ultimately I want to use these 2 drop downs for reference functions on a second worksheet.
View 3 Replies
View Related
Jan 17, 2012
Is there an Excel formula to remove the spacebar + characters in red, as shown below? I need to be left with only the last name, first name and the semicolon.
Mouse, Mickey ;
Microsoft Outlook has changed the way that email addresses from the global addressbook copy and paste (from version 2003 to version 2010).
View 8 Replies
View Related
Aug 14, 2014
I have 2 separate pivots running off 2 separate data sources. The filters used for each or fairly similar (bar one entry).
Is there a way to have one filter that filters the information in both pivots even though they are not based on the same data source?
Also, with the filter that is slightly different; in one table, I have an option that shows (this is just an example) 'retail and end user' yet, in the other table these are two separate options.
View 4 Replies
View Related
Oct 1, 2013
I have 1000's of rows of data and 8 of the columns are reserved to tag possible defects. All 8 columns pull from the same drop down list of all possible defects. I have 8 columns set aside because a row may contain multiple defects.
What I want to do is look up a single defect (ie "scratches") within all 8 columns. See my attached simplified example. If I filter column B for scratches, the scratched part in row 4 will be ignored. Can I apply a filter across multiple columns?
Filter Example.xlsx
View 1 Replies
View Related
Oct 16, 2009
I am looking for a way to combine three pivot tables in to one. Basically, one sales rep will have data for the current month, the year to date, and the total year. I have to compile this data to one sheet, for 30 sales managers! The problem with running the pivot three times (once for monthly, year to date, and yearly) is that brands that aren't sold in the month are then left off the table, which leads to a mess that it takes far too long to clean up. I hope I've made this clear, but please check the attachments for a better idea of what I'm looking to do...
View 9 Replies
View Related
Nov 18, 2013
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
View 4 Replies
View Related
Jun 13, 2007
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".
View 9 Replies
View Related
Jun 18, 2008
I am trying to run a macro to filter data based on 3 criteria. i want to filter column c to show rows with "Application", "Application / Web", and "Application / Database" which is referenced in the criteria range stated below.... Sheets(" Validation").Range("D95:D97"). for some reason it does'nt pick up the answers when i know that they exist in column c. could it be that they are formulas populating column c?
Sub Macro3()
Range("c4:c200").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets("Validation").Range("D95:D97")
End Sub
View 3 Replies
View Related
Aug 2, 2012
I have a huge excel file. This file contains Projects, Project Manager, departments and sales for different quarters. Each department has sales and the quarter the sale was done. Now I want to create a pivot table where I can see the sales for each quarter for each project or each project manager. Attached file may elaborate the problem. ShaA1.xlsx
View 9 Replies
View Related
Aug 21, 2014
I have six columns.
Column A: dates
Column B: data with relates to A
Column C: dates
Column D: data with relates to C
Column E: dates
Column F: data with relates to E
View 1 Replies
View Related
Apr 1, 2009
The FOB PRICE in sheet JAN09SEA is in any of three currencies, HKD EUR or USD.
The output on sheet CONSOLIDATE should only be USD.
Also, you can see that some countries send their freight in two or even three different currencies. This would need to be accounted for.
Additionally to the currency issue, I need to sort the output to RM or FG. For that, on sheet CONSOLIDATE, we have a button located in G1 to switch types.
E.G. if presses for FG, the formula should only check for the values where the row is marked with FG in column X.
The freight charge is always in HKD and needs to be converted to USD as well. Also it should be FREIGHT = FREIGHT CHARGE + SURCHARGE converted to USD.
This is quite a lot of information. I hope someone can think of a solution for the problems.
If you need more information.
View 9 Replies
View Related
Jun 22, 2013
I am staffing an upcoming event with volunteers and I want to be able to a single volunteer across their various shifts and locations. However, I cannot guarantee that a particular volunteer will always be in the same column on my spreadsheet so AutoFilter doesn't do the trick. I've tried using Advanced Filter options as well, but I either can't seem to get it to work or it won't work with strings.
I've attached a sample of the type of document I'm working in (genericized) so hopefully my below questions will make more sense.
Ideally, I'd like to be able to be able to search/filter for Joe and see rows 2 and 3 appear. Is this possible?
Additionally, I'd like to know who is also on shift together. For example, is there a way to search for Tony and Sarah together and have row 5 appear. I know that I can do this now with filtering column 2 and then 3, but the real document I'm using is over 800 rows long (very big event!) and using progressive filtering would take longer than I'd like.
View 4 Replies
View Related
Nov 6, 2013
Is there a way to filter data based on multiple OR condition with a like parameter.
I have data which has near about 50000 rows and now i would like to filter on a header called "Activity Type" and would like to fetch result for a text which resembles "from ABC group" or "From PQR group" or "From XYZ group".
View 5 Replies
View Related
Jan 28, 2014
Well we are trying to fetch some data using excel filters. But we are unable to apply filter simultaneously on two excel columns. The problem is explained in the attached sheet. If you have any query/doubt in understanding the whole problem then writ it.
View 5 Replies
View Related
Apr 21, 2014
i have my "raw data" and in my "summary" sheet, i want to show the data by the data that is filter in range D15:D21
Essentially, when the user filters the data from cell D15, i want the the macro to paste the data row D27 down/across from the "raw data" table. in addition, i am not sure how to create dependent drop down.
for example, in cell D15 it shows "segment"; in the cell below, D16 (tyre size), i want it to list all tyre sizes within that selected "segment"; when i want D17, to show "speed index" for by segment and tyre size etc.
in the table, i want the macro to show the data based on what the user filters from above. i can then do my calculations but getting this to work is the tricky part.
View 14 Replies
View Related
Mar 5, 2014
Can I use vba advancedfilter to work with more than one criteria?
I presently have one range designated. At the top cell has the field, or column, header name being "Student", then followed by a list of 6 people, located in Sheets("Extract").Range("A1:A7"), which is then extracted from Sheets("Complete").Range("tblPrimary[#ALL]") to Sheets("Extract").Range("AA1") as in:
[Code] ......
I want to also be able to filter out a specific month, whose field/column name just happens to be "Month", but I suspect I will need to change it to "InfoMonth" or the like to avoid the probable key word of "Month"...
The months are numerical in those fields - 1 through 12.
Can I add to the present filtering line or do I need to then create an additional filter?
View 6 Replies
View Related
Oct 21, 2008
I have a range of data (up to 20,000 cells) that has up to 30 different entries. I need to apply a filter to this data to filter out all but 5 of these.
View 5 Replies
View Related
Feb 7, 2012
I have a list of farmers in A1:A1000 with the types of livestock they keep in col B, delimited with commas and spaces e.g:
Col A Col B
Name Animals
Smith Cows, Pigs, Sheep, Horses
Jones Sheep, Pigs, Chickens, Geese, Alpaccas
Price Cows, Sheep, Pigs, Chickens, Rabbits
Williams Cows, Chickens, Horses, Alpaccas, Pigs
I need to be able to filter this list using up to 3 criteria, e.g. Filter all farmers with Cows, AND Sheep, AND Pigs. Applying this to the data above would show Smith and Price.
The user would need to enter the criteria somewhere, preferably in 3 cells, let's say D1, D2, & D3. I reckon I need to use Advanced Filter, but not sure how to do it with all the animal types to be filtered being in one column.
View 2 Replies
View Related
Sep 5, 2012
I am having a problem filtering information within mulitple columns. I do not understand how the advanced search works either, so let me try to explain what is going on. I would post a pic of the table to make it easier to understand if I knew how to.I have thousands of rows in Column A with information, for this scenario a number. Thing is that all these numbers are seperated by many blank rows.
Ex: Row 1 has a number, Rows 2-10 are blank, Row 11 has a number, Rows 12-24 are blank, etc. Over in Column C, for every row that has a number in Column A, there is a corresponding number in Column C followed by Rows of numbers. ex: Row 1 Column A has corresponding information in Rows 1-10 of Column C.If this is clear, understand that this repeats for tens of thousands of lines. When I am looking for a specific number in Column A, I do a filter for just that number.
When that happens, it only brings up the info from that same row in Column C. I need the filter to bring up all ten rows of info in Column C that correlate to the row I filtered in Column A. I know the easiest way is to label all the rows in column A with the same number, but the reason the blanks are in place is so it is easier to read the breaks between the different information.
View 1 Replies
View Related
Jun 23, 2014
I have a data table consisting of entries; for a simple example,
Column header
Entry A
Entry B
Entry C
Entry D
Entry E
I want to apply an Advanced Filter to Exclude multiple items. Say I want to exclude entries A and B. In my Advanced Filter criteria range I entered:
Column header
A
B
But since Excel reads each line of Advanced Filter criteria as "or" - i.e. does not equal A OR does not equal B - nothing gets filtered. I know the proper way to use AND in Advanced Filter is with multiple columns, such as:
Column Header
Column Header
A
B
But my exclusions will be dynamic so I would prefer to have my criteria listed vertically instead of horizontally.
View 4 Replies
View Related
May 22, 2007
I have a macro assigned to a command button that will auto filter a single worksheet. I need to be able to use this on multiple worksheets at the same time. Here is the Macro:
Private Sub CommandButton1_Click()
Range("Q7:Q567").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1
ActiveWindow.SmallScroll Down:=-12
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
Selection.AutoFilter Field:=1, Criteria1:="<>0", Operator:=xlAnd
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A1").Select
End Sub
View 5 Replies
View Related
