I need to filter and clean the criterion based on another column cells.
If the R column has the value 1, then it is filtered and cleaned only the cells of column J
My spreadsheet needs to stay current with the strange layout.
[Code] ....
Attached File : LayOutStranger.xlsx
Is it possible to remove autofilter criteria found in the drop down menus automatically created by autofilter?
View 9 Replies View RelatedI am attempting to create a formula that will, return text results based on 2 criterion.
This is what my sheet looks like
Name Job Full Time / Partime
Bob Admin Full Time
Barry Accounts Part Time
Chris Claims Full Time
Emma Claims Full Time
The formula I have for this is {=INDEX(A1:A4,MATCH(1,(((B1:B4)="Claims")*((C1:C4)="Full Time")),0))}
Which is fine and returns the name Chris, however as you can see from above, Emma also meets the criteria given however, I cannot get Emma to appear also. Is it possible to display more than one entry in a cell/s based on this formula, or am I missing something.
I would use a pivot table to do this, however I need the sheet to be completely automated.
Multilookup based on Multiple criterion without using Arrays
Dear Forum,
I am well aware of the oft-mentioned Array Solution which gives us more than one record based on mulitple criterion using the INDEX function..
the formula layout being as mentioned below:
[ ={INDEX(A2:A100,SMALL(IF((B2:B100=<Condtn 1>)*(C2:C100=<Condtn 2>),ROW(D2:D100)-ROW($D$2)+1),ROW($D$2:$D2))} ]
I like this formula a lot and have used it more often, however it has made my file exceedingly slow..
Considering that it takes every small change in the file to compute around 5 mins or more than this the whole purpose seems to be defeated, I have read a lot of posts on Array Vs Non-Array of late by DonkeyOte.
How to create checked and unchecked checkboxes based on three criterion(in sheet 1)?for clear explanation..it will be like this...firstly,the "SELECT TAKEN only"checkbox will checked/unchecked row d20:r20.secondly,the "SELECT RETURN only" checkbox will checked/unchecked row d21:r21 and so on.lastly,"SELECT ALL" will checked/unchecked all the rows.
Checkbox1.xlsm
This following is just an example:
In sheet1 I have 4 columns; Name, Age, *** and Job.
Sheet2 has three columns; Name, Age and Job.
What I need is VB that will check the *** column from Sheet1 and when it is Male, the Name, Age and Job fields from that row are copied into the cells in Sheet2.
I have this code, which is primarily built by the record macro utility. I know that record macro does some unnecessary steps such as selecting while inserting columns etc which is generally never done in vba.
View 5 Replies View RelatedI have a bunch of "empty" cells. They only look empty visually. In fact, they contain something, because they are preventing some formulas from working. How do I cleanse them and make them truly empty without disturbing the contents of the other cells containing real data.
View 1 Replies View RelatedI have data listed in column A. The data appears in text, blanks, and phone numbers....all in different rows.
I need to capture only the phone number and 3 rows above it no matter what the text says or how many rows inbetween each phone number. Then proceed to copy and paste it to a new sheet into one column.
This needs to continue until i reach the end of column a.
Eg. row 1: the fox
row 2: the pig
row 3: animals
row 4: water
row 5: land
row 6: (780) 111-2222
i need to copy rows 3-6, paste to new column and then continue down column A looking for the next chunk of data to copy and paste.
So it would turn out to look like:
row 1: animals
row 2: water
row 3: land
row 4: (780) 111-2222
All in column B
Is there a way to filter column A but ensure it includes multiple lines of data in a different column.
I have attached an example of what I am looking for:
I need to filter by community but keep all the information listed in columns, B, C, & D.
So when I filter to Thunder Bay I would still see the 5 members listed in column D.
I realize the easy solution is to fill all the empty cells in column A to pick up the data in the other columns, but I do not want to Fill all the empty cells to include the name "Thunder Bay" (not the look I want for this spreadsheet).
I would like to extract competition naps from the competition entries and then remove the string ending "nap" from the data so it can be pasted to another sheet in it's 'clean' format.
on the example sheet column B will always be the first column and the range will extend to either G,H or I (always the max range as there are always 6 to 8 races)
i would like the code to search through these columns of data and find the cell ending in 'nap' then to return this in column i, once done remove 'nap' ending from both the source cell and column i.
once done to then go through all the rows and do similar
the end result would be all naps returned into cell j and all the 'nap' endings through the data range B to I (max) to be removed to leave the horses name only.
in book 3 the first row nap was sommersturm so i have shown the outcome i would like with the nap ending in I1 removed and the horse name returned in J1 again minus 'nap' ending.
the data will always be clean with no leading/trailing/excessive spaces and always be lower case too. sometimes a space is not in between horse name and nap but it's always last 3 characters i want removed still.
In my excel sheet, data present in the column are in dynamic range. After that I want to delete or clean all blank column which is bordered and in fill color. What VB program I have to write.
E.g. if data is present upto column 'J', then I have to clean all columns from 'K' onwards..(where bordered and fill color there)
conditional Macro or filter? Easy to record a Macro which filters, but choosing what (column) to filter based on values of a cell I'm not sure how to go about that. It means implementing IFs into a Macro, or..?
Data on "Staff List" sheet I have a list with all staff details including availability. I need to filter their availabiltiy and copy values over to another sheet. The availabiltiy shows Monday to Sunday and states for which of our offices they are are available. Here the columns BS to BY
Available Location Monday
Available Location Tuesday
Available Location Wednesday
[Code]....
Now I need the Macro to choose which column to filter based on the office set in cell C3 on the "Availability" sheet. And then filter that column for YES.
I have a spread sheet that has three sheets, PROJECTS - TIMES - LISTS Time sheet.xlsx PROJECTS contains a list of 'In Progress' and 'completed' projects for work, with relevant other data, I.e. PROJECT No. / STATUS etc This will grow to 1,000's of rows TIMES will contain the amount of time a person spends on a PROJECT on a daily basis. This will grow to 10,000's of rows LISTS contain basic validation list for the first 2 sheets.
Part A
I would like to have a validation drop down list for the PROJECT NO column in the TIMES sheet that only lists the PROJECT NO from the PROJECTS sheet where STATUS <> COMPLETED. I.e. I only want to display the PROJECT NO's for current projects.
Part B
Even though I only want the PROJECT NO used, can the drop down list also contain other column's data for reference information only before one is selected.
I would like to filter between a date range. the dates i need to filter between are in cells A1 and A2. this range contains the start and end of month. It is referenced from another sheet in the same workbook (='Job List'!C2).
i want to filter; greater than or equal to A1 and less than or equal to A2. How I can do it using the advanced filter method. My data range is C2:C9.
I have data contained in 4 columns. Column A is name, Column B is ID, Column C is Company, Column D is amount. There are duplicate names in column A and duplicate ID's in column B (ID's have correspending Company Names in Column C). I want to have a list so that this combination shows up only once, and then the amount next to it will be the sum of all.
Example:
DataABC DTom9730-003Company B100 Joe10242-001Company A200 Tom9730-003Company B300 Joe10242-001Company A400 Sam9730-003Company B100 Desired ResultTom9730-003Company B400 Joe10242-001Company A600 Sam9730-003Company B100
I want to be able to show only the dates whose months are selected in the drop down in cell A1. I've been able to do this with a Macro that I must run every time I change A1; however, I was wondering if there was a way to have the macro done automatically. I've been trying the Worksheet_Change options with little command. In addition to all of the If/Thens, I've also tried the If/ElseIfs as well:
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
If Target = "January" Then
ActiveSheet.Range("$A$2:$C$14").AutoFilter Field:=1, Criteria1:= _
xlFilterAllDatesInPeriodJanuary, Operator:=xlFilterDynamic
[Code] .....
I am trying to filter my excel sheet based on certain digits in a column of part numbers. The part number has 10 characters. I would like to filter it so that all part numbers where the 4th character is the number 5 or 7 is listed and where the 5th character is a zero. (i.e. R4X5831310 is a part number where 5 is the 4th character; I would like the filter to show this part number)
View 2 Replies View RelatedI work on an excel spreadsheet all day and I'm constantly cuting and pasting a value to filter another column. I would love to have a macro button that would automatically do this.
The job card sheets are labled 'Page 1' through to 'Page 175'. As I am working on each job card sheet I need to filter column on another sheet within the same workbook. This is indicated on the attached picture.
So in a perfect world as I would enter in the PLANT ID number on to any job card, then hit a macro button on the toolbar and the Pole No column would then be filtered by the value in the PLANT ID cell.
As there are multiple job cards the macro would probably need to use the 'active cell' value to filter by. But as you can probably tell I'm no expert so you judge the best way to do it.
MWTS034G22 Job Card Sheet
[URL] .....
Windows 7 (32bit)
MS EXCEL 2010
I have a table with a filter applied. I don't want all of the column headers to be filtered because the data in those column is not suitable filter criteria. Is there anyway I can remove the individual arrows on the right hand side of the cells in question?
View 6 Replies View RelatedOn a sheet called, "Details", I have a pivot table that has three fields in the column area, Calendar type, Description, and Dates. I want to filter the pivot table based on a label filter in the Dates column. The filter should be between two dates (in D4 and D5) that are entered on another sheet and passed to the Details sheet through formulas in cells D4 and D5.
I have attached the following code to a button on a different sheet.
The code successfully filters for the employee name (which is a report filter in the pivot table) which is in a named cell.
I am having trouble with passing the start and end dates to my pivot table filter. I do not get any errors, the filter is simply blank.
Private Sub CommandButton3_Click()
Sheets("Details").Select
Dim pt As PivotTable
Dim Employee As String
Dim SDate As String
Dim EDate As String
[Code]...
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 RelatedHow to automatically filter can filter and blue-colored cells.
View 4 Replies View RelatedI 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 trying to figure out if there is a formula I could use that will calculate the average of a group cells in one column based on the condition of another column. It's hard to explain, so I will show an example. All the data is on a one worksheet and I'm trying to show totals and averages on another worksheet. Location, Days
17, 4
17, 3
17, 5
26, 4
26, 8
26, 10
26, 7
On a different worksheet I would want to know what the average days are for each location. So is there a formula that I could use that will look at column A for a specified location number and then average all the days in column B for that location? I'm using Excel 2003 and have tried using the Average(if) but with no success.
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
I have two columns. Column A had numbers and column B has names. I need a count from column A for each name in column B.
View 1 Replies View RelatedI think I am working all around this in Excel 2003 and feel like I am in the neighborhood, but can't seem to quite get there. On sheet1 I have 2 columns of 100s of rows. All of the cells in column A will be filled in with a date as time goes by (1-May-09 for example). Several of them may be 1-May-09 as a matter of fact. In column B, some of the cells will have a number in them and some of them will be empty. On sheet2, I want to construct a formula that returns a count of cells in column B that have a number in them based on a specific date in column A. For example:
AB
1-May
1-May777
1-May
1-May
1-May
2-May999
3-May
2-May
1-May
2-May111
1-May
If I could get the formula right, it would return an answer of 1 for 1-May in the example, because only 1 of the 1-May entries in column A has a number in the adjacent cell in column B. A formula for 2-May would return an answer of 2 since only 2 of the 2-May entries in column A have numbers in the adjacent cell in column B, and so on for the month. If I understand things right, there are too many arguments for COUNTIF. It seems that SUMPRODUCT should work, but I can't make it so.
I would like to write a macro to add cells in columns C,D,E,F,G & H based on the text (employee) entered in column B. For example I need it to add all the cells for "John S" found within column "C" and then total it at the bottom with a code JSSS (John S straight shop time) or JSOS (John S overtime shop) as per my example. The attatchment is a spreadsheet I use to total billable hours for our employees.
View 3 Replies View RelatedI have a problem which requires a vba routine that is currently outside of my level of expertise...
Here is an example of the data:
TESCO GARAGE 3729
59
REF 177 1092098207 BCC
59
CO-OP GROUP LTD
60
[Code]......
