Excel Sort / Filter Function Based On Set Criteria?
Sep 9, 2012I have following data to sort/filter
Sector
Flt no
origin
[Code]...
Is this possible with excel functions?
ADVERTISEMENT
Excel 2010 :: How To Filter / Sort Data Based On Partial Match Of Data In Cell
Apr 16, 2013I 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
Filter & Sort Multiple Columns With Single Criteria
Sep 27, 2008I have a spreadsheet with multiple columns. The first column defines a "route", and the next two list "start" and "end" cities for that route. The fourth column lists the length of each route. There are only a limited number of cities, so the same entries appear in both "start" and "end" several times. I would like to use Autofilter to sort the list for every appearance of a given entry in either "start" or "end". Is there a way to make Autofilter sort mutiple columns simultaneously?
I could achieve the desired end result with Advanced Filter, but I want something with the ease-of-use and immediate update/response of Autofilter. Advanced Filter requires explanation (as well as lots of clicking and typing) whereas Autofilter is self-evident. I also want to avoid VBA Macros as they are not well-understood by the users who will use this spreadsheet (and any VBA Macro will require very specific input to work properly.) Is it possible to do what I want? Or is Advanced Filter / VBA the only way to do it?
Excel 2010 :: Highlighting Rows In VBA Based On Multiple Criteria And Sum Function
Apr 25, 2014I am trying to write a macro in VBA excel 2010 that compares 2 sheets.
The macro should be something along the lines of if column 7 on sheet 1 = column 1 on sheet 2
AND
on that same row if column 6 on sheet 1 = column 3 on sheet 2
highlight green
** also on sheet1 there can be the same batch ID so if it is the same batch ID it needs to calculate the sum and look at that amount...
Sheet1:
settleid
min Tran Date
Payment Vehicle
total Deposit
total CF
total MRI
RMBATCHID
475-T
03/03/2014
Connect
$562.95
$19.95
$543.00
6G000001450835
[Code] .......
Results >
Sheet1:
settleid
min Tran Date
Payment Vehicle
total Deposit
total CF
total MRI
RMBATCHID
475-T
03/03/2014
Connect
$562.95
$19.95
$543.00
6G000001450835
[Code] ..........
Copy And Paste Function Messes Up Because Of Custom Sort / Filter?
Jun 5, 2013what I do with excel: I have an excel sheet that has over 18,000 rows in it. Since it would be a nightmare to scroll around to find what I want, I use the Custom Sort and Filter options under Editing>Sort&Filter. So for example, I can omit 17,800 rows using a specific setting so that I can work with a more reasonable 200 rows. Moreover, the 200 rows comes from all over the spreadsheet. Meaning their row numbers are not always consecutive.
Here's the problem: Whenever I try to copy anything from this "edited or filtered" excel sheet, the resulting paste is not an exact copy. Excel perfectly copies the first rows up until the point where the row numbers ceases to be consecutive. So, the copy function messes up somehow when the data being copied comes from a different section of the original 18,000 rows.
In case this isn't clear enough...
Let's say that the original file has rows 1,2,3,4,5,6,7,8,9,10
Once I filter/custom sort, I see rows 1,2,3,8,9,10
When I try to copy/paste 2,3,8,9, excel copies 2,3, but messes up the rest of the 8,9. And I end up with a totally useless copy that's generally shorter than it's supposed to be.
Excel 2010 :: Filter - Sort Not Working?
Oct 24, 2011I have a filter on the Qty column but when I sort Z -A or A - Z it does not work. I have tried formatting as number but no success, I have never seen this before, why the sort wont work? I am using Excel 2010 in Win 7
View 5 Replies View RelatedFilter And Sort Data Based On Names And Hours
May 2, 2014I just want to filter and sort the data based on names and hours.
Here it goes:
1. Filter Column F.
2. Filter it by: UK & M UK
3. After that, filter Column E in "Ascending order"
4. Count the data that are:
-starts 1:01AM - 7:59AM and display the total count in Sheet2, if there are no data in other specific time, then display a value "0"
For example for UK and M UK:
In Sheet 2, ("F5") = 3 as there are 3 data, G5=1, H5=0 and so on and so forth..
I've attached my file : Filter and Sort.xlsx
Advanced Filter With Function As Criteria Range
Feb 27, 2008I have a worksheet with 6000 rows (W1), and another with 2500 rows (W2).
I need to check whether the values of W2 are found in the second column of W1. As in if(iserror(search(valuex,worksheet2!B2)),"",A2) ---> resulting in something like:
If the value is found in the cell B2 of column B on W1, then return its reference which you find in A2, otherwise leave a blank.
I need to check all 2500 values in all 6000 rows.
I know for sure that I will have limited hits (max of 200) so I would like to create a list on W2 (the values) where I check if they are found in W1 and return only the 'hits'. I would like to filter out the blanks.
YOu can filter the blanks, I know, but you need to have a 'full' version (I thought) with all the blanks and the hits and then and only then you can filter.
But is there a way how I can use the advanced filter, with a criteria range using a function. Something like: criteria range --> if(iserror(search(valuex,worksheet2!B2)),"",A2) is not equal to "".
Sort Based On Different Criteria
Jul 23, 2014I have a list of people that I want to sort according to their age in ascending order. If several people have the same last name (family members), I want those to be sorted together. After clicking on the SORT-button the result should look like this:
[Code] ...
Attached File : Test2.xlsm
Filter Based On 2 Criteria
Oct 29, 2013In the advanced filter, I am given two criteria one is filter by unit price by less than 1000 and order no atleast 100 .
View 6 Replies View RelatedHow To Filter Information Based On Two Criteria
Apr 29, 2014I have been struggling hard to pull information out of a table using vlookup and match & Index functions. It does not seem to be working. Below is my criteria:
I have a data of call centre agents with names , dates and calls offered answered details. On another sheet i have given a list of names of agents in a drop down list and all the dates of the week. Now i want that when a user selects a particular agent name from the drop down list, the data fetched under '07042014' date column is against the number of calls answered by the selected agent on this date. I have 5 columns of title '07042014' , '08042014', '09042014', '10042014' and '11042014' Capture.PNG. Attach is the file how it looks like.
How to use match and index function or any other function to fetch information against this date as per the agent selected.
Sort Array Based On Criteria From Drop-down
May 18, 2009How can I sort an array of data based on the selection from a drop-down menu? And can it be 'secondary' sorted with the result from a second drop-down (ascending or descending is unimportant)?
View 2 Replies View RelatedSort Ascending/Descending Based On Criteria
Aug 2, 2006I have an Overview worksheet that is pulling info from all other sheets in the workbook.
What I want to do is order that info in ascending order depending on the worksheet name.
So I want all the worksheet names that start with 'AB' say sorted in ascending order and all worksheets that start with 'BC' in ascending order.
Now this I have at present working by a macro I created which once the info is loaded onto the sheet I just selected the info and ordered it in ascending order. The thing is though the next time I enter this Overview sheet I might have an extra worksheet that starts with 'AB' and this will not be included in the order, I will have to adjust it everytime so I need a more automatic ordering process.
Dynamic Columns - Filter Based On Criteria?
Feb 19, 2013I have a 6000+ row database with 5 columns across. The 5 columns have the following headings: Hair Color, Eye Color, Age, Location, and Salary.
I have created macros that will filter each column based on criteria - for example:
[Code].....
As you can see, at cell J10 and K10, I list the filter that is used for a particular search. Some searches, will only have 2 search criteria, some may have all 5, etc.
On a separate worksheet (in the workbook), I have a "Report Tab", my question (after a long build up) is how do I dynamically change the columns and values on the "Report" tab depending on the search that I perform? For example, if I did a two variable filter (Hair and Salary), those would be the only two columns on the report - if I did all five variables for the search, all columns would be on the report, and so on.
Filter Rows Based On Multiple Criteria
Mar 19, 2007Example attached. I need to filter rows based on a start date and stop date, columns C and D. So for example the filter date is 01Mar07 (located in A5). As this date in this cell is changed the rows are filtered accordingly. I need to filter rows so that any row with a start date which includes Mar 07 is shown and I need to include all rows that have an end date in Mar 07. This would result in the inclusion of an event that started in Feb and Ends in march being displayed.
Additionally, I would need to clear the filter. I'm just starting out, I'm sure this is easy for you all the excel experts., and you may probably have a better method to approach this.
VBA Auto Filter To Select Field Based On Certain Criteria?
Feb 5, 2014I'm currently using the following:
[Code] .........
However, i'd like it select the field ie 2-17 based on a year in a cell on another sheet "x" in cell E3.
At the moment i have a row of numbers 1-17 above the corresponding field cell so for example the number 15 is attached to the year 2013 (although this is dynamic).
I just cant work out how to get the autofilter to select the number that matches the cell in "x" E3 with the same value in T2:AJ2 on filter sheet and then filter the column that matches.
Show (Filter) Rows Based On Multiple Criteria
Jan 29, 2009I'm trying to create a formula (used in sheet 2 column B) that would generate the results in sheet2.B based on the contents of sheet 1. sheet2.B2 would contain a formula searching for the both "Y" in sheet1.C and the text in sheet2.A2 (in this case "E&P"). When each match is found, the contents of sheet1.A should be returned to sheet2.B, as shown in the mockup. There will be multiple matches (at least 15-20) for each search criteria, and I don't know how many there will be ahead of time.
I've tried various formulas, and they either have incorrect syntax and return every row in a range, or only return the first match correct match each time instead of all correct matches. I found some UDFs posted online that claim to do this, but I've gotten them into the spreadsheet and they generate # NUM errors. I would prefer to work with Excel-native formulas if possible, because I'm going to eventually hand this off to someone else to maintain and they may not have the level of expertise to deal with UDFs.
Excel 2013 :: Saving Custom Filter Criteria
Jul 30, 2014I am trying to save filter options to so I can apply the same filter to multiple spreadsheets. For example, I have several spreadsheets with 50 or more school names and I am trying to set a filter that will filter out the same 20 schools each time. Is there anyway to do this in excel 2013?
View 4 Replies View RelatedVBA To Hide / Show Columns Based On Filter And Multiple Criteria
Mar 8, 2014I have a very large table and i need to be able to Hide/show specific ranges based on:
Filter +and+ specific cell values in columns
brief example of the table : tablee.png
So...
1. Filter Column "B" (in this case we select "HELPING")
2. Auto hide/show collumns. - IF "C1" = "Required" THAN Show "C:E", IF "C1" ="N/A" , HIDE "C:E" and so on for every column like above.
There are over 80 columns like the "C:E" range. and I only need to show those that are "Required".
Copy Rows Based On Criteria, Paste As Values To New Sheet & Sort
Apr 15, 2008I have spent a few days searching through forums but cannot find examples that i have been able to successfully adapt ( because they are to complicated for my limited knowledge). I have a workbook with 5 sheets, sheet1 (current), and sheet 4 (archive) are the important ones. I need a macro to
A) copy rows from "current" to "archive" ( to the 1st empty row) if column S of "current" contains "Closed" ( the word is generated by formula).
B) The paste needs to paste special values and number formats ( want to lose formula but not conditional formatting).
C) data sort "archives" based on col A - this puts the newly archived data into correct sequence.Data sort lowest number 1st
D) delete the copied rows from "current".
e) give me a count of how many rows it deleted, (I have a macro to insert rows so will run that manually to recreate the correct number of emtpy rows (with formula and formatting) to bring the current sheet back to usual size.
I tried modifying a macro by RPaulson (based on cells on one sheet to cells on another), to work with entire rows but couldn't get it to work.
Found that paste special uses PasteSpecial xlPasteValues, but , and thats about it.
VBA - Advanced Filter / Find Row Items To Remove Based On Multiple Criteria
Jun 28, 2014I'm working with many rows of data (500,000+) and many columns. To simplify my question, I'm going to provide a simple example using made up numbers and only the columns I'm concerned with.
BillT Doc.ItemQty
F11231012.00
F2123205.00
S1123105.00
RE321202.00
F2321108.00
F2321201.00
RE321203.00
RE999808.00
F27771001.00
RE7771001.00
I am trying to remove the docs that have two Bill types that cancel each other out, where the qtys match and highlight the rows where the qtys don't match. The macro needs to have the positive and negative bill types programatically entered, where for example F1 and F2 are positive and S1 and RE are negative. Keep in mind the data may not necessarily be in order as it is above.
So for example with data above, the rows for doc 777 would be removed completely because the item numbers are the same, the qty is the same, and the bill types oppose each other. Doc 123 and Item 10 lines should be highlighted since their bill types are opposed bu their qtys don't match.
I hope this makes sense. I tired to achieve this using multiple loops and arrays, but ran out of memory when working with the entire set of data. I'm assuming their must be a better way to do this, I'm hoping some of the intelligent individuals here will be able to point in the right direction.
Sort / Filter Rows: Sort A Range Of Row
Nov 26, 2008I would like to ask if it is possible to sort a range of row? What I mean is, if I sort like Row 1, the entire block of row 1 will move as well? Like if i have column A to F, then row 1 of column A to F will move together at the same time.
View 3 Replies View RelatedFilter Results Of Primary Filter Sort
Feb 11, 2008I am in the process of making a database more efficient and am running into a problem with sorting data. I currently am trying to use AutoFilter to sort the data. When I want to then narrow the results further using the same column as the critical and there is nothing that matches the critical, I get everything from the entire database that matches that critical rather than what I want to see, which, in this case, would be nothing.
I have attached a sample file. In it, when All AF 1000 is run and then Selected MAC 2000 Wash is run, I want to see no results instead of seeing all MAC 2000 Washes from the original data set. I can do it using IF/THEN but I am looking for a faster way to do it.
Expand Table To Include Missing Times From Data And Sort Based On Multiple Criteria
May 6, 2014I have a set of data (assume 2 columns, one with a long name and the second with a time). The names contain variables that must be used as criteria (a single entry may contain "Blue" and "On"/"Off") and times vary, based on when the Name turns "On" or "Off" [in minutes: 25 (On), 47 (Off), 89 (On), 100 (Off) and 137 (On)]. I need to create another table that automatically inserts values for all times, to include the missing times (0 min thru 24 and 26 thru 46, etc.). EX: If the first entry is "Blue-On" at "25 minutes", the cells from 0 minutes to 24 minutes are each "0" and become a "1" at 25 minutes - the following cells are "1" until 47 minutes (where it is turned off). (1 and 0 represent "On" and "Off", respectively)
How can I created a formula to insert the correct numbers into the correct places? I'm willing to have multiple cells with formulas and simply hide the columns that are doing the calculations.
Function To Return Value Based On Two Criteria?
Aug 10, 2014I'm working on a spreadsheet that I need to return a value to "Unit Price" field in worksheet "Master Inventory" based on matching the "Product" field and the "Construction" field from the "Unit Pricing" worksheet.
In essence, I would like the "Unit Price" field to match the "Product" field from the "Master Inventory" sheet to the "New Product Description" field on the "Unit Pricing" sheet, then match the "Construction" field on the "Master Inventory" sheet to the column headers on the "Unit Pricing" sheet and return the value that corresponds to both criteria.
Ex: On the "Master Inventory" sheet, I would like the "Unit Price" field to match the "Product" (Book Browser) to the "New Product Description" (Book Browser) on the "Unit Pricing" sheet and then return the value where the "Construction" (Laminate) matches the column header (Laminate) on the "Unit Pricing" sheet which would return the value of "$240.00".
I've tried using a vlookup function, vlookup/match function, index/match function and an index/match/match function. I've attached a sample workbook.
Average Function Based On Criteria
Jul 29, 2006I'm trying to find the best Macro or Formula to get this done.
Sheet 1 has a list of salesperson in column "A" and the total numbers they made on Monday (in the same row, column "E"), Tuesday (In the same row, column "F"), Wednesday (In the same row, column "G"), Thurday (In the same row, column "H"), and Friday (In the same row, column "I"). I also have a sheet for each individual salesperson. I need to find a Macro or a Formula that I can use to give me the average of the numbers if "A5" = the salesperson in Sheet 3 then avarage the numbers from "E5" to "I5" and I want the results to show up in cell "D35".
Advanced Filter Code - Criteria Range More Than 1 Row Breaks Filter
May 1, 2014I 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...
Worksheet Function Max With Multiple Criteria In Excel VBA
Apr 30, 2013I would like to use worksheetfunction max with multiple criteria in excel VBA. I have the following data:
Col A
Col B
a
1
a
2
a
3
[code]....
In excel i was able to use the formula =MAX(($A$2:$A$6=$A2)*($B$2:$B$6)).
I would like to be able to do the same in VBA using the worksheetfunction.max. I was trying something like
worksheetfunction.MAX(($A$2:$A$6=$A2)*($B$2:$B$6)) and it didnt work. How to proceed?
Locate Date Function/Formula Based On Criteria
Jul 7, 2006I am trying to find the starting effective month for a workyear. The criteria for the selected month is that the data BEFORE the effective month is all zeros. I manage to get the result if the data AFTER the effective month is ALL non- zeroes. If there is any zero, the data fetch will be inaccurate due to the COUNTIF formula. Is there any other way or formula that will enable me to get the result. File attached for testing and reference
View 6 Replies View RelatedFormula/Function To Return Multiple Values Based On Criteria
Aug 25, 2006I would like to be able to use the Vlookup function to return more then one value as a result of the criteria. I have a cloumn of dates that populates the X axis of a gantt chart with data whilst the Y axis will be populated by a site reference resulting in a program of work, the Y axis data is the result of a vlookup function. My difficulty arises however with multiple sites, for instance where two or three sites will be visited on the same day. The vlookup function will only return the first value it finds in a range to the formulated cell. The result being a missing site(s) from the gantt chart / work program. is it possible to return all values to a cell i.e. site1, site2, site3. using a vlookup or do i need to use another method of doing this?
View 2 Replies View Related