Is there possible filter a partial number in column?
i have many values in column, i want enter partial value in Texbox (ActiveX).
if i enter in textbox 6840.
Result
64801000
64802000
64803000
##############
if i enter in textbox 5480.
Result
54801002
54802002
54801000
54802000
54801001
54802001
54801005
###################
I am using the GetOpenFilename method to allow the user to select a file to open:
vaFiler = Application.GetOpenFilename _
(FileFilter:="Adobe PDF Files (*.pdf),*.pdf", _
title:=Description, MultiSelect:=False)
What I would like to do is 'pre-load' the filename the user can select. They will be picking a file to attach to a part as a datasheet. Almost every single datasheet in the library has the part nuber as part of it's filename, so to make it a little quicker (and more accurate), I want pre-load the filename selection window with *partnr*.pdf. If nothing shows up, the user can delete filename I have entered for them and see all files in that directory.
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
I need to create a consecutive number range
from 10,770 to 14,194
(using only numbers that end in 0,1,2,3, & 4)
In other words, with No numbers that end
in 5/6/7/8/9
Example: 10,770
10,771
10,772
10,773
10,774
10,780
10,781
10,782
10,783
10,784
10,790
10,791
... and so on, upto 14,194
I'm trying to figure out a formula to be able to look at a column of txt and if it finds the word total it need to output the number at the column next to it. If the word total isnt in the text then it should leave it blanks (see below). Basically I want a column that pulls only the totals amounts in the column.
Aaron Drielick 3
Aaron Drielick 2.5
Aaron Drielick 37.5
Aaron Drielick Total 151.0
Akila Subagaru 31
Akila Subagaru 1.5
Akila Subagaru 1
Akila Subagaru 1.5
Akila Subagaru Total35.0
Albert Major 4
Albert Major 6.5
Albert Major 2
Albert Major 19.5
I have an excel sheet that we need to type in an account number it always starts with 8774100 then has 9 more numbers. If I leave it on number formating it always changes the last number to 0 so I format the cell as TEXT. Anyway is there a way to get the 8774100 to automatically go in and only have to type the last 9 numbers? I know I can copy paste but then I have to double click or make sure I enter the last 9 numbers at the end or use the top entry. The data entry people just want to hit enter and go from line to line fast. In the past I just divided the cell into 2 cells and made the 8774100 in column A and columb B was the ending 9 numbers. But they need to be in the same cell.
View 2 Replies View RelatedRight now, I'm trying to find a way to count the number of times a certain phrase appears in a column.
I'm currently using this formula for exact values: COUNTIF(A1:A5,"Hello"), but this only works if an entry in a column is exactly: "Hello"
I want to be able to count a column even if it has more words, such as "Hello how are you" etc., and this column would be counted because it has the word "hello" in it.
1. Counts the number of cells in a column with the same 18-character text string(Full String is 24) using a RIGHT( function.
2. Returns a 1 if there is 1 and a zero if the count is greater than 1.
I have a monthly report. Worksheets for each month, each row is a type of fruit (column A), and the number sold (column B). A new worksheet is added each month with the information. I also have a Summary worksheet - running total of all months: Type of Fruit (column A), Number sold (column B), and Total of all fruits - number sold. I would like a formula for the Summary worksheet - to match that fruit (column A) to any of the other worksheets - matching the fruit (column A), and bring back the number sold (column B). (note some apples are Fuji and some are Honey crisp - those would be totaled together)
ex:
Tab = Jan 14
Fuji apples3
Bananas2
grapes9
TOTAL14
[Code]....
I m trying to use an Autofilter to filter my cells with a Number Filter of is greater of equal to 4 and is less than or equal to 5.
But as you can see I would like to customise is using a range of 2 values which i have specified in Cell P1 and Q1.
I manage to figure out how to reference to this cell, but Im not sure how can i put my ">=" and "<=" operators into my code so i can get it to work exactly how i want as shown in Code 1.
Code 1
Selection.AutoFilter
ActiveSheet.Range("$A$1:$K$118").AutoFilter Field:=6, Criteria1:=">=4", _
Operator:=xlAnd, Criteria2:="<=5"
Code 2
Selection.AutoFilter
ActiveSheet.Range("$A$1:$K$118").AutoFilter Field:=6, Criteria1:=Range("P1").Value, _Operator:=xlAnd, Criteria2:=Range("Q1").Value
This is for Excel 2007,I have two sets of model numbers. One set is the full model numbers of the units we use, and the other is an abbreviated form used to lookup up certificate numbers. I need a way to match these up so I can use one set of search criteria to find out if there is a match. Here is an example of what I need to match with a partial text match:
H,AE35(6,9)36+TD and AE3563636D145C2501AP
H,RE36(6,9)36 and RE36936C145B2505AP
if I could do this with a formula that matches multiple items at the same time (ie; if A & B & C match=true) with the above model number being one of those items (certificate numbers are issued for sets, but the other model numbers are fine).If that's not possible, a one time VBA run to match all of the abbreviations at once would also work. If these items are matched up with a one time VBA, the VBA needs to account for their being more than one match for each abbreviation depending on the size of the unit.
So H,AE35(6,9)36+TD could be matched to:
AE3563636D145C2501AP
AE3563636D175C2501AP
AE3563636D210C2501AP
I have to find out the top first number aftet filtering the data based on particular column. ie each time when i am taking each filter value the row will start from different number.
so how to find the top row number after filter? U got what i mean?
This has got to be simpler than I'm making it out to be, but I'm stuck. I have a column that is created by a formula that counts the number of times a name appears in a list and prepends the number to the beginning of the name. The result is something like:
A1:
1JohnSmith021488
2JohnSmith021488
1JaneDoe102187
2JaneDoe102187
3JaneDoe102187
4JaneDoe102187
5JaneDoe102187
6JaneDoe102187
7JaneDoe102187
8JaneDoe102187
9JaneDoe102187
10JaneDoe102187
11JaneDoe102187
I need a filter that results in a list of names preceded by "1" (but not 10, 11, 12, etc.) The correctly filtered result of the above list would be:
DESIRED:
1JohnSmith021488
1JaneDoe102187
My autofilter searches for data that begin with "1", so instead of the above result, I get:
UNDESIRED:
1JohnSmith021488
1JaneDoe102187
10JaneDoe102187
11JaneDoe102187
There's got to be an easy way to do this, but I can't figure how to account for the variable number of digits (1 or 2 digits) at the beginning. It would probably work to set up an advanced filter for cells that begin with a number less than 2, but I don't know how to do that.
I have applied a filter to my worksheet and would like to know if there is a way to get the number of rows included in the resulting filtered data.
Worksheets("All Work").Select
iCount = GetEnd(Worksheets("All Work"))
Set Cell1 = Cells(2, 1)
Set Cell2 = Cells(iCount, 6)
Worksheets("All Work").Range(Cell1, Cell2).AutoFilter Field:=7, Criteria1:="Unassigned"
Now that I have it filtered I need to know how many rows are there with data. I'll then set up a loop to read the data from each row. I can't use my getend function because it will still end up with 93 rows and the number of unassigned is about 23.
I have a large file with cells filled with text (converted from a database, imported into Excel). The text is in rows of column A and always contains a 16 digit number, which is what I need. I am able to clean up this text and show only this number, but then Excel shows the last digit as a '0'.
An example of the text I need to filter the 16-digit number from: GIRO 6838657 K MAHMODBETALINGSKENM. 7062542158461684 STORNOADMINISTRATIEVE REDEN 1 ZIE DE TOEGEZONDEN KENNISGEVINGVAN VERHAAL OF CJIC.NL/VZD
I've altered some of the information in here so this is fake. The text is messy but all I need is the number.
This is the macro I use to clean up the text and leave only the 16 digit number:
Sub CleanUp
Dim e As Variant
With CreateObject("VBScript.RegExp")
[Code].....
It sort of works, but when I run it, I get this number as a result: 7062542158461680 (last digit is changed into a zero).
I've been trying to insert this line into the macro but it doesn't work: Columns("A:A").NumberFormat = "@"
How do I change the outcome of this macro into the actual number?
If this is impossible to do, I can also manage if there is a way to filter only the last 9 digits from the 16 digit number. I can work with that, too.
I use Excel XP (2002) at work.
I have the need to filter out letters put in after a number in a time card spreadsheet. I'm not sure that using a select case is the right approach. I need to allow the user to put in a number and a letter signifying what type of time it is. Each cell equals a date on a calendar. For example if the user puts in 8s then the code will add 8 hours to the total sick time, strip out the s and just leave 8 in the cell. The problem is that I need to deal with all of the other letters/symbols that they can enter. From what I know of VBA which isn't much a Select Case seems to be way to go without using a bunch of nested If statements. Here is what I would like to do but this doesn't work. This is a short example of what I have tried as far as Select Case goes.
View 6 Replies View RelatedAs I have not tried to do this yet this is a hypothetical scenario. Imagine that a column, when filtered, gives cell values such as 3,6,9,12,18,24,36 ..and so on. Is there a way to select, say 12, and then see all the other values that are divisible into 12. e.g. all rows that have 3,6,12. Another example- select 36 and see 3,6,9,12,18 and 36?
On the attached example sheet, column 'O' will be filtered.
I am using a pivot table to explore the relationship between the two variables in a survey. Question 1 is a "choose all that apply" question, while Question 2 is more "if you said yes to any in question 1, choose all that apply".
So, I have about 10 filters for question 1, where each combination will give me a different number for the responses for question 2. My question is this: How do I get excel to automatically find the # of individuals with the particular combination of filters in question 1?
Ie. if said yes to 3/10 options in question 1, how do I get excel to find out how many actually said yes to the 3/10 questions.
The purpose of this data is for reference to question #2, so that i can utilize some percentages.
I have a column of "timestamp" data (in mins) which i want to filter by a given time interval, say 10 mins. Then i want to count the number of records for each time interval and output the data to a sheet. how can i achieve this? through vba?
I attached a pic illustrating what i want to accomplish.
QQ截图20140429104406.png
How can I FILTER a range and display the unique items, one below the other, WITHOUT blank cells - with only a FORMULA. What I came up with is shown in the attached WB. I would like to present the countries like in C11:C15.
View 5 Replies View RelatedI have a database in Excel 2013 and now I want that when a value (a person's name) is entered in a cell. That then the database sort of filters the list for me, so it's still possible to make changes in the entries.
[URL]
Picture above to specify the search, which I would therefore like to edit
Dashboard_Action Pool Team 7.2.xlsm
I have been all morning working on a simplified version of the tutorial from YouTube: Create your own Excel Search Pt. 4. But came back later so only then that I can not change the data:?
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...
How to automatically filter can filter and blue-colored cells.
View 4 Replies View RelatedWriting code to do the following:
Save advanced filter settings
Remove filter (or simply set to be 'select all'
Run other code (I have this piece of course)
Put filter back on with same selections chosen as when it was removed
(Need this becuase the code in the middle does not work properly when the data is filtered)
I just got into the world of PowerPivot, Excel 2013 and Pivot Tables and am in the process of creating a Dashboard which I will then be uploading to SharePoint 2013.
On top of page I added the new timeline filter which I've linked to my pivot charts. Now what I would like to do, is create an additional pivot chart which looks at whatever date range has been used in the timeline filter and subtract 5 years from that. So, when I select a date range of November 2013 - December 2013 in timeline filter, the additional pivot chart will show the details for November 2008 - December 2013. This is where I get stuck.
I'm using two SSAS cubes which I'm combining together in PowerPivot and then display in Pivot Tables and Pivot Charts.
I have a date of birth in cell "B13". It's formated as xx/xx/xxxx. I'd like to copy this into another cell where I have annual holidays listed, but, I'd like to have it formatted as mm:dd - omit the year.
View 2 Replies View RelatedI have a cell that contains a string which is always formatted the same. "Dated between Date1 and Date2" I would like to put Date2 into a variable; however, I don't know how to get just that part of the cell.
View 9 Replies View RelatedCan anyone explain why the unique filter does not produce a unique result - sample attached?
View 2 Replies View RelatedI 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..
I've a table of data which users auto-filter. I'm wanting to capture the filter criteria each time the filter is applied / re-applied. Can't seem to find an 'event' for the auto-filter.
View 4 Replies View Related
