I 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.
I 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:
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.
Example 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.
I'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.
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.
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 :
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...
Currently I got 1 vba coding which only filter with 3 criteria. Can I alter the vba to have 4 to 5 criteria in the filtering? Below is my VBA coding. Let say I want to add 1 more filter for column C2...
I have a excel of about 5000 rows and 80 columns. Each row corresponds to a particular set of experiment. I want to reduce the number of rows by removing useless experimnets like controls etc.
I want to search the row for some key words(ex. control) and if it present then remove it. It should prompt user to enter the keyword and the original file should not be disturbed. It can be copied to 2nd worksheet.
How to give a cell link instead of any value for filter criteria, For example I need the data between two date range, the first date is in cell A1 and second date is in cell B1 by using macro. I have tried by using below code also but after filter all data hiding.
Dim a As Date Dim b As Date a = Range("a1") b = Range("b1") Rows("2:2").Select Range("A2").Select Selection.AutoFilter
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
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".
I am using the code below to coax Autofilter into letting me use three criteria. I can hard code the values in for two of the cells (B6 and C6 in this case) but for it to be of any use I need the array to use the actual values found in those two cells.
With Worksheets("Sheet2") Cells.Autofilter field:=1, Criteria1:=Sheets("Sheet1").Range("B1").Value Cells.Autofilter field:=6, Criteria1:=Array("2880", "2879"), Operator:=xlFilterValues, _ Operator:=xlOr, Criteria2:=Sheets("Sheet1").Range("D6").Value End With
How should this be setup to allow the array to read the two actual cell values?
What i need to do is basically delete every row in my spreadsheet that contains the criteria "L-0.###" in said column. by ### i am referring to 3 decimals, such as:
L-0.250 L-0.000 L-0.555
and so on. you get the idea.
with the below program i got it working so i can type in:
and have it sort out everything proceeding L-0. ...problem is i need to keep the data such as this:
L-0.53 L-0.00 L-0.0
or basically everything not 3 decimals in with a head of L-0.
here is the macro i'm using:
Sub DeleteRowByString() ' ' DeleteRowByString Macro ' Macro recorded 7/10/2007 by Khenzel ' ' Keyboard Shortcut: Ctrl+Shift+S '
'Get the criteria in the form of text or number. vCriteria = Application.InputBox(Prompt:="Type in the criteria that should be removed from matching columns. " _ & "If the criteria is in a cell, point to the cell with your mouse pointer", _ Title:="Ferguson Enterprises, Inc.", Type:=1 + 2)
'Go no further if they Cancel. If vCriteria = "False" Then Exit Sub
'Get the relative column number where the criteria should be found lCol = Application.InputBox(Prompt:="Type in the relative number of the column where " _ & "the criteria can be found. (Ex. A=1, B=2, Etc.)", Title:="Ferguson Enterprises, Inc.", Type:=1)
if possibly a modification in my coding to make this work, or simply a criteria i can type in to make it happen.
Anyway, in my workbook I have a master sheet which contains all the information entered (this master sheet will continue to grow as more information as entered). I want to create a userform to allow someone to search the data by selecting a date range (from/to) and a criteria (Pending/Settled) and for the results to be copied into a new sheet (simply I don't want the raw information tampered with in an unregulated way).
i want to display the results of one criteria using VBA codes instead of just filtering one by one to display the result and collate it. I have attached the sample. I want to find the "subcodes" of name "kathy". to be able to do that, i filter "kathy" to display all the "codes" then filter all the "codes" to find the "subcodes". is there vba code for this one so that when i input the name, all the subcodes will be displayed?
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
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.
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:
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?
I have had no luck searching for an answer so better to ask. I am using auto filter on Sheet1 and filtering a column of dates. What I need is for the criteria for the auto filter to be pulled from cell H1 on Sheet 2. Is this possible or is auto filter the wrong direction to go? I am looking to find all the rows on Sheet 1 that contain the date found in cell H1 on Sheet 2. Then I am going to cut and paste that data to another worksheet. I think I have all the rest of it figured out but am stumped at the filtering portion.
I'm trying to use an advanced filter to filter a large data set using several parameters. I had this worked out before but there have been some parameters added that have thrown my filter off balance and I'm struggling getting it back.
Here's the criteria I need to filter
Column 1: 85
Column 2: 3
Column 3: BU 1 BU 2 BU 3 BU 4
Column 4: BA 1 BA 2 BA 3 BA 4 BA 5 BA 6 BA 7
Since I have an uneven list of criteria, I'm struggling to figure out the layout with the 'AND' condition. I know I can copy the 85 and 3 figures to each line but when I have 4 figures in column 3 and 7 figures in column 4, how can I create the 'AND' condition for all these criteria?