I have a spreadsheet with columns A to I filled. I want to filter the data so that:
Show all rows where I= a value, plus show all rows where E= the value in row E where I = the value selected.
eg
E=10, I=value
E=10, I does not=value
E=12, I does not=value.
In the above case I want to filter to show first and last row, not 2nd row.
I 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:?
How do I go about using an advanced filter to filter a list of data e.g.
boat
boat
boat
car
car
truck
and have the filter extract only the boat entries to another worksheet, so on another worksheet I end up with
boat
boat
boat
1- Force cell format date to by (yyyy/mm/dd) only, with worng msgbox( validation).
2- Make the first day of a month in a color cell
I've Tried this In Conditional Formating (=VALUE(right(A1;2))=1) but didn't work
3-Make Advanced Filter to filter data between two dates .
I want to use a Pivot table to filter data to show just the studies that contain patients from the 'South' area?
As per example below I want to be able to see all the patients in all areas but only for studies that have patients from the south. I put together an array formula that works well for small tables but is too much with one one my sheets that contains 200,000 rows.
Before filtering:
Study ID
Study Short Title
Study Patient ID
Area
1346
LLP
90126
Northwest
[code]....
I have a excel worksheet with the following columns: First name, Last Name, Email address, domain of email, product type, date registered. The list consists of about 50,000 entries. I want to sort the list by the domain of email(which I am able to do already) Once this is done, I want to find all instances of where a domain appears at least 10 times on the list(such as webmessenger.com appears 40 times, so I want to get that data).
For those instances where the domain appears at least 10 times, I want to pull those rows out of the intial list and put them in a new list(the new list will be sorted by domain and will only have people who have a domain which appears at least 10 times). To make this a bit more clear, The initial list I have is a list of people who registered to use the software my employer makes. We are trying to locate companies which may have many people using our consumer version of the software. When there is a large amount of people in the same organization using our software, it would benefit them to upgrade to the enterprise version due to enhanced managment features. By running this filter, I can see which companies have at least 10 users registered to use our software. Of course I will remove any Gmail/yahoo mail/msn/hotmail... pretty much any public email domains and just leave the ones that are obviously corporate emails.
So far, I think it probably has to be done with a pivot table... I was able to get a table that tells me how many instances occur from each domain, but I cannot get it to display the actual data(it just says IE. company.com 200, yahoo 120, etc... I need it to show me the 200 rows of company.com emails and extract them to a new sheet so that I can then follow up with company.com and see if they are interested in the corporate version.)
I'm using Excel 2010 and I applied a Data Filter to a simple table. I then messed around with the drop downs in each column, sorting the data by different criteria. After doing this, is there a simple way to get the table to revert back to its original order/form?
View 3 Replies View RelatedI 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
Trying to use Excel Data List to create a database style report. IE. Originally blank sheet, which is only populated by data containing data matching "filters" input into cells ( say A1 & A2 )
I.e. A1 = Delivery week to be filtered by, and B1 Manufacturer Name
So if I type week "1" into A1 & Manufacturer "Microsoft" into A2, it will show a table only containing data Microsoft, Week 1, and associated data for those lines across the screen.
Week 1
Microsoft
PO number : Date Ordered: Address 1, 2 3 etc....
0011 01/01/13 Somewhere
0015 02/01/13 Anywhere
0213 05/01/13 Nowhere
I know this is much easier with a database, however my manager insists a database cannot be used, and it must be in a spreadsheet format !
I have a database named Data with a few 100 records. I am attempting to view the top 10 records by Sales or PBIT. The Userform has two option buttons (Sales and PBIT). If the user checks Sales the top 10 sales records must display and the same for PBIT.
View 4 Replies View RelatedI need to filter some data. So I want to have a time based filter. I have created a bench market time...so I need to say if the time stamp on data point 1 is greater then timebenchmark then fill with data from celx.
Value time stamp Benchmark NEW Cel
1) 38 12:30 12:15 38
2) 24 12:31 12:15 24
3) 14 12:14 12:15 blank..nothing..empty
if timestamp is > Benchmark then fill NEW cel with Value
if timestamp is < Benchmark then fill NEW cel with Nothing (ie leave blank)
If title 9 contain sam then j Column Answer is I column 1 Number
Sheet1 ABCDEFGHIJ1Title1Title2Title3Title4Title5Title6Title7Title8Title9Answer2AAAAAAAAAAAAAAAA1sam13AAAAAAAAAAAAAAAA1sam14AAAAAAAAAAAAAAAA2sam25AAAAAAAAAAAAAAAAotherother6AAAAAAAAAAAAAAAA4sam47AAAAAAAAAAAAAAAAotherother8AAAAAAAAAAAAAAAAotherother Excel tables to the web >> Excel Jeanie HTML 4
I have two tabs in one spreadsheet and I want to have the results chosen in one tab to filter in the other tab. So based on my drop down box result chosen in the first tab, I want the data in the other tab automatically filter only the result that was chosen.
For example, if I have the word "apple" in the drop down then I want all my data in the second tab to filter by "apple" in column B.
Is this something that would be possible?
I have some data that looks like this:
5差し上げる
5机の上
5申し上げる
5上手な
5上着
6テーブルの下
6下げる
6下宿する
6下手な
6下着
and in another sheet, corresponding data like this.
上5
下6
中7
...
後53
手54
新55
I want to be able to filter out the rows from the first sheet when a symbol (kanji) from the right hand side is included at a number higher than that of the second sheet.
For example, 手 appears as a number 6 in the first sheet but doesn't appear until 54 in the second sheet. Therefore I would like to delete the row with 手 in the first sheet.
Is there a simple way to do this? If not, can anyone suggest a way to program this? I was thinking of writing a little VBA code that reads the the characters and then compares them to every number above and if it doesn't find it, deleting that row but I have 2 problems.
1. How can I tell VBA to actually delete a row (not just the contents preferably)
2. These are asian characters which I'm not sure are well supported in strings which I would need for comparison?
Is it possible to filter a top * for already filtered data. E.g, i have sales people each having their total sales per month , what im trying to achieve is when i filter for only 1 person i want to get the top 10 sales months for that person.
View 2 Replies View RelatedI am hoping to quickly display our inventory & WIP values by a specific job number (Column F). When I filter the job number I would like the new values for inventory & WIP to be displayed in cells N2 & P2. Is this possible?
View 1 Replies View RelatedI am using below code to filter my data.
[Code] .......
I want filter data with blanks cells.
I get columns of data by the tens of thousands and would like to be able to filter email addresses from the other content. I've searched for a macro but can't find anything.
Does anyone know of one that would work to filter (example) data like:
(input) charlie jones email me at:name321@email.com..555-555-5555
to
(filtered data) name321@email.com
i was thinking or an if,then with the following conditions:
-must contain "@"
-must end in ".com" ".net" ".org" etc...
-must not begin with " " "!" "@" "#" etc...
I'm using the below code to get only those rows which contains 'Deploy' or 'Pre-prod' but it gives less rows than its supposed to do.
View 11 Replies View RelatedI am trying to use 2 macro's to filter data in a spreadsheet.
Down the A Colum i have a series of names for different students and across row 1 i have a series of different exams (exam 1,2,3,4,5 etc).
I am trying to get a macro to work, whereby if i select for Example Jonny Briggs, it will display on a separate work sheet, all the exams that Johnny has passed.
Also, i am trying to use a second macro, so that if i say 'select exam 5', it will show me all the people who have passed exam 5.
With this filtering, i also want it to remove any columns or rows that are not applicable to the data i want to see. For example, if i want to see what exams Jonny Briggs has passed, i DON'T want to see which exams he hasn't. The same as if i want to see who has passed Exam 5 - i DON'T want to see all of the other exams
MACRO 1
Sub Copy_Row_With_AutoFilter1()
Dim WS As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim rng2 As Range
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
'Name of the worksheet with the data
Set WS = Sheets("Sheet1") '
I have that list in column B and I want to put only those cells that end in "RET" in column C and put only those that don`t end in "RET" in column D, by using an automatic method, otherwise I`m gonna have to do it many times in many worksheets manually ;/
=+IF( 'Bella Vida_DRH'!$E72=9,'Bella Vida_DRH'!A72,"")
I am trying to create a sheet that will search for all the rows that contain a 9 in a particular cell
then it will copy that row into a new sheet
if not =9 then it should go and check the next row etc. etc.
this formula works for one cell but doesn't goto the next cell if the value is false
I would really appreciate if some one could point me in the right direction
I am trying to filter data on one of my worksheets the column format is as follows:
supplier,product,price
the price it totaled at the bottom of the price column. I need to be able to filter by supplier to create a purchase order leaving all products by that supplier in the rows allong with the product name and the total price for that supplier. using the autofilter i can filter by supplier but the total cost remains the same. it is the Total cost that is causing me problems.
Is there a way i can filter down data using dates and then copy to another worksheet. So lets i want copy all things with todays date on it and paste it to another worksheet.
View 2 Replies View RelatedIs there a way of find the data list (criteria list) presented in the drop down menu of the AutoFilter and copy it to somewhere???
View 2 Replies View RelatedI have a column of data that i would like to filter using a macro and place the unique data in a separate column as completely independent data.
View 3 Replies View RelatedI have a spreadsheet containing data relating to addresses, the postcode and house number are in seperate cells. I want to input postcode and house number into a userform (" find record") have that locate the entry in the worksheet, then return the 18, cells in the row to a second userform ("updater") to be updated/edited and the updated data returned to the cells it came from, by necessity some of the cells in the worksheet use data validation lists. I have the two userforms and have included listboxes in "updater" and have linked these to the data validation lists in the worksheet.
View 3 Replies View RelatedHow 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 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 can we copy and paste filter
For Example I have copied table data and want to paste same date in J and K column then how can I do this ?
File is attached for ref : Copy and Paste.xls窶
Actually my Basic question is how can I copy data from filtered data and paste in visible cells only