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?
I'm fairly new to excel and trying to create a simple spreadsheet to provide a log of staff allocation. I've created a first a sheet displaying a table with a week of rota. The first column lists where the staff are placed, the first row the date and inbetween the staff member covering that date. There are 5 further sheets, one for each member of staff with a simple table - first column is date, second is allocation.
Back on the first sheet below the timetable is a further grid showing on the first column staff names, and below each date a formula =VLOOKUP( B$3,David!$A:$Z,2,FALSE) pulls information from the 5 sheets displying whether a memeber of staff is on leave or there allocation on that particular day.
The problem i'm having is trying to get the 5 simple staff sheet to pull data from the Rota ie. I need say B3 to look at the date in A3, find the same date on the Rota sheet, look for the relevent staff name below it (if present), and display the allocation from the first column on the same row as the staff member.
I've tried a combination of Vlookup with Match and Index with Match but nothing seem to work.
I have been working on a project with 30 products and 20 different companies.
I am working on a business case, where there are launch dates for various companies with expected sales, contribution margin, NPV etc.
The idea is that I can create specific scenarios (What is Company1 doesn't launch the products, etc), and there is an accompanying each company. Because it can be difficult to see what products are included in the current calculations, I would like to use some kind of function that produces a list that matches my criteria. (In this case, all cells for a company that are larger than 0).
I would like to find a function that finds all products that have a launch date and create a list of them -- without spaces between non-matched results. And that it updates whenever data is changed (new launch year or new product in company's profile)
I have included a generic excel file to show what I am going for.
I'm trying to Filter a list by the criteria selected in a listbox, and activated by a command button. My problem is the sort criteria. I have 11 regions, numbered 01EPS through 11EPS. Sorting by any of these criteria works just fine.
The problem is that I also want to be able to sort by ALL of the regions at once. I can do this by using a custom filter. I recorded a macro to see what the difference was, and it simply a matter of changing 01EPS to "=*EPS". However, adding that to my case list results in an error message of "Compile Error: Syntax Error".
Here is what I have. 4 Worksheets. The first worksheet is a summary page. I have 350 personnel that are broken down into three different groups. So each group has it's own sheet. Here is what I need to accomplish. Results need to be posted on the summary sheet.
I need to compare cells B2 & D3 for each row on a worksheet and display the number of times they match on a worksheet. For example how many times does EP & EP match on a certain row. I need to compare cells B2 & D3 for each row on a worksheeet and display the number of times they don't match on a worksheet. For example how many times does EP & MP occur. I've attached an example for reference
So we had a month long, company paid (woohoo!) "weight watchers" challenge. On 1 worksheet ("Stats"), I have the Name of every Employee (A Column), then their Start Weight (B Column), End Weight (C Column), Department (D Column) and finally Location (E Column).
I know how sensative some people can be about their weight, so I locked the page and created another worksheet named "UI", which will display the "Average End Weight" for each department. So Human Resources would be the department in cell A2 on the UI sheet, with City 1 being the Column Header in cell B1. There are 8 cities (offices) and 23 departments.
So, for cell B2, I want to scan through the "Stats" worksheet and locate all of the cells in the department and location columns that read "Human Resources" and "City 1" respectively. Then grab the "End Weight" for every row that meets these 2 criteria, add them up, and divide by the number of "End Weights" that were grabbed.
My solution would be to make a separate column for each department (and then each city, so essentially 8 columns to represent all the cities for each department), use a nested (maybe 4) IF statements to then list only the end weight if that particular row meets the criteria, then at the bottom of each separate column, add all the shown numbers up and divide, then draw the information from THAT number into the "Average" cell on the UI worksheet. But it isn't very dynamic and if i want to use this sheet next year, there will be more employee info to be added and it would be a mess..further more, it would be...what...23 Departments x 8 Cities x 155 employees = ~28,500 added cells.
I'm trying to use DGET to pull coordinate pairs from a list and match them up to a label based on certain criteria. I can't seem to get it to work. I'm also thinking that the way I have it set up, the coordinate pairs might not stay together and that has to be the case. My criteria is multiple cell based too and I don't think DGET can handle that.
For example, In order for a coordinate pair to be labeled "B" the x coordinate must satisfy a condition and the y coordinate must satisfy a different condition. Can I use DGET with the AND function?
My sheet called 'Report' finds a type using a vlookup. I then have a sheet called labor, where I want to find put the total amount for labor using this formula: '=SUMIF(Report!$K$2:$K$65000;LABOUR;Report!$G$2:$G$65000)', but it only returns a '-'. I want the formula to return total amount from column G, if column K is type 'LABOUR'. Is my formula incorrect?
I have a list of data that has (Col A) item, (Col B) lot# and (Col C) quantity. In Columns DEF I have the same but column E (Lot#) is blank. Is there a formula I can use that would match Item and Quantity (A and C) with (D and F) and then drop the Lot # form column B into column E if there is a match?
On column A, I simply do an A-Z sort that gives the expected result. So I recorded it as a macro. In column G is a code that corresponds with column A. When the marcro is executed Column A is sorted but the corresponding value in G stays where it is. Clicking the A-Z button works. But when that action is recorded as a macro I get the above quirck. Since VBA is not my beef, I don't really know what wrong.
I can't seem to get this to work the way I need it to do. Let's say I have a 2 sheet workbook. Sheet1 is called "Order" and Sheet2 is called "005". On sheet2 (005) I have 9 columns that are populated. Row 1 is my header row and then row 2+ is all my figures. On sheet 1 (order) I want A1 to look at sheet2 (005) in F1:F10000 to find all cells that have "N" in the cell. I then want it to return value in column A that corresponds with the "N". I want it to list all the ones from Sheet2 (005).
I have a macro I use to filter a large file that I receive each day. Each row of data has an identifier in column C, and this is where the macro looks to determine whether or not to remove that row. Currently, my macro will only remove rows in which column C contains any of the following strings: "B0A0", "B0MH", or "SRB". This part of the macro looks like this:
I want to add additional functionality to this macro. I will always need rows removed where the identifier in column C contains one of these static strings, but there are times when there or other strings that I would like the macro to remove. Ideally, I'd like to have a range on a worksheet where I can type a string of characters in each cell, and the macro will look to this range when removing rows in my large file. How can I modify this existing code to incorporate this?
Say, for example, that I want to designate the range B2:B50 on Sheet 4 as the range where I want to type these strings.
I am trying to use this function to figure out averages for tests. there can be up to 25 tests in the sheet which is where the "YES" comes in. If the test booklet was used for this class then YES will be placed in the cell in field B2:B26.
Now thes issue I'm having is that the formula is doing the math for the fields C2:C26 if there is a number in them if there is even if it does not say YES in the B2:B26 field.
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...
STO-Productivity-Feb0314.xlsxI work in cancer research and I'm trying to find a way to show the amount of studies that staff have in total and their role on the study. I have a data table that contains many fields but the crux of what I'm having trouble with starts with the following:
When I begin with a pivot table I start by just selecting the all of the studies listed in the table and then the two roles that staff can have data or regulatory. Staff can do both roles or just one on a study. I put the roles of data and regulatory into the row labels and the studies that we work on in the values. The problem is that I now only see studies that the staff member works on that meet working on both roles and not the studies that they serve only one role.
UPDATE: see the attached spreadsheet to illustrate further. Ablorh is the employee that I have it focused on and it only shows the one study she has both the regulator and data roles. The 7 other studies that the employee works on in the data role do not show.
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?