Filter Data For Viewing Using Multiple Conditions
Jun 16, 2009
I am far from advanced in excel, but I have been tasked with creating a macro which includes filtering.
1) I have a workbook called Workbook1
2) I need to copy Sheet 3 from Workbook1 cells A1-J5000 into a new workbook called Master.
3) Column D is labelled Status and the two statuses are Enabled and Disabled.
4) I need to copy anything labelled Enabled to a new worksheet in the workbook already created called Enabled and then the same for Disabled. I then need to put it into date order which is column F labelled Date.
I have been trying to work on this all morning, but have found that Excel doesn't like Macros with filters and I don't know the first thing about VBA
View 4 Replies
ADVERTISEMENT
Aug 21, 2009
1. I have 4 columns (let's say D,E,I,J);
D E I J
Account1 Sum1 Account2 Sum2
151 93,79 BF2479 30,00
1476757 93,79 BF7 22,00
BF2479 30,00 151 93,79
86041 84,03 23235 30,00
534 6608,45
2. I need to clean the multiple identical data in the list by both Account no. and Sum (of course the list is much longer!) so that the list should look like this:
D E I J
Account1 Sum1 Account2 Sum2
1476757 93,79 BF7 22,00
86041 84,03 23235 30,00
534 6608,45
I must say that I am a beginner in Excel, so I tried to use this:
=IF(OR(E5=$J$5:$J$430);IF(INDEX($I$5:$I$430;MATCH(E5;$J$5:$J$430;FALSE);1)=D5;"";E5);E5)
for column E, and
=IF(OR(J5=$E$5:$E$452);IF(INDEX($D$5:$D$452;MATCH(J5;$E$5:$E$452;FALSE);1)=I5;"";J5);J5)
(which is reverse of the first one) for column J; (The ranges are the ones used in the real sheet), not for the ones in the example above!); The formula does seem to eliminate a dew unwanted data, but does not give me all the wanted results!
View 4 Replies
View Related
Apr 23, 2014
with below example i m using multiple data filter but i want to delete the multiple data using checkboxes instead of filter.
View 3 Replies
View Related
Dec 29, 2009
hope the below example is clear to understand and makes sense .....
View 10 Replies
View Related
Dec 27, 2012
What I would like to do is combine all data (from sheet: Page1,Page2, and Page3) into one sheet named 'Consolidated'. Unfortunately, as you will see from the attached file, my programming and understanding of VBA programming is pretty naive.
What I am after is, when I combine the data, from all sheets into the one, the data should be
1. Automatically Sorted by Patient Name
2. Group, results of the same patient from all sheets one below the other in seperate rows
3. Delete any other rows that may have a patient name and ID, but rest of the rows (New Mole,Clinically Suspicious......Benign Naevus) are blank. (This happens because of the way I have designed my userform. For first visit the details are entered in Page 1, then for second Visit Details are entered in Page2, and for third visit in Page 3. So when I enter the first visit details, the Patient Name and Patient Number are copied into Page 2 and Page 3 even though the other details remain blank. I have done it this way because, if say the Patient comes for visit the second time, the clinician may or may not be aware which visit this would be for the patient. So as soon as they enter patient number, it will give them a message that Patient already exists. So when they click Open Existing Patient Record, the form autopopulates with any notes from their previous visit into the respective page (i.e Page 1,Page 2 or Page 3).
Page 1 (Page 2 and Page 3 are exactly the same)
Patient Name
Patient Number
New Mole
Clinically suspicious
Changed from mapping Photo
Level of Suspicion: Score
Monitor
[code].....
View 3 Replies
View Related
Dec 15, 2007
I would like to return the value in the "Temp" column that meets all the conditions of the columns "letter", "day", and "time." Specifically, the "Temp" of "A", "Weekday", "East." I have attached a small spreadsheet to help.
The suggestion of an Index with a Match Array worked but the problem is that my spreadsheet has over 5000 rows and my data has to meet three criteria = 15000 cells to cross reference. And I need to set up a spreadsheet that would do about 300 arrays. So obviously my excel is soooo slow I can't even get 10% complete.
Any other suggestions? I've read (from Dave I think) that database is a powerful alternative but have not been able to find any info on this and it's not an Excel function. For your reference I'm attaching a demo spreadsheet of what I'm trying to do with the two Index + Match arrays that work for small files, not mine.
I looked into Pivot Tables but it can't handle all the row and column headings.
View 4 Replies
View Related
Jul 26, 2014
Want to be able to select data from Column C (3 data validations/drop down menus) and have the corresponding values display in the appropriate fields in column H. Not sure if this is possible?
View 4 Replies
View Related
Apr 25, 2014
I receive data for time windows on jobs. my issue is 10-11 time frames appear before 8-9 time frames when I move data into a Pivot Table. This happens because the data I receive does have "08" signifying 8 so the "1" in 10 causes the 10-11 to appear before the 8-9. Is there a formula I could use to make this change? Currently if I want to view items in sequential order I have to go into the file and add a 0 before any jobs that start at 8 or 9am
View 3 Replies
View Related
Jan 13, 2007
I have a text box. I want it to pull the data from Cell B2 and list it in the text box. The problem is that I want to be able to change the data if the user clieck inside the box and changes it.
View 3 Replies
View Related
Nov 17, 2007
I am trying to create my critera section for an Advanced Filter, but it seems like Auto Filter uses each row as a specific filter.
i.e. If I need to filter all the "2"s in column A, but both the "1"s and "2"s in column B, I need to create two different filters like this:
Column A Column B
2 1
2 2
Instead of just:
Column A Column B
2 1
2
Is there any way I can use auto-filter without having to put in every possible combination of my criteria in as different filters? Auto Merged Post;I did try creating columns next to each other, but that seems to only work as an "AND."
Per my example, I need to return everything with column A = 2, and column B = 1 or 2.
Except in my actual spreadsheet, it's more like where A = {1,2,3,4,5} and B = {0,9,8,7,6} and C = {a,b,c,d,e}
View 7 Replies
View Related
May 20, 2008
I have a workbook with 236,000 rows of data (accross 4 sheets). Each sheet is identical layout with differnet data. An example of the data is:
Policy NumberScheme Number Name Agent code Scheme Name
KxxxxxxxxxxJxxxxxMr A example5/xxxx Example Scheme
KxxxxxxxxxxJxxxxxMr A N Other6/xxxx Another Example
To do a search for a scheme number for example I would filter the 4 sheets using the scheme number I needed, then copy and paste the info from the 4 sheets into a 4th sheet so that I could work with the data.
I need to know if its possible to do the following:
create a useform to act as a GUI to that if a scheme number is selected it will search/filter the 4 sheets and present the results in a 5th sheet
View 7 Replies
View Related
Nov 24, 2013
I have Excel 2003. I am working on a problem. I have multiple sheets for various purposes of my customers with all various columns. However i require to have a master sheet which gives me the due dates customer wise in one place. i.e it selects the customer, the worksheet purpose (say upcoming event) and the due date filed from various worksheets and combines into a master sheet,sorts the same customerwise,due date wise.
Using VBA i did create a worksheet which does this but using advanced filter but however how do i do it for all worksheets?
Using macro to go individually into each worksheet and collate data into one seems very unreliable to me. Is there a solution?
View 1 Replies
View Related
Jul 18, 2014
How to apply a filter over the array "a" shown below and get the result in a new array "b" containing the filtered values based on the following conditions (the conditions criteria could be 1, 2 or 3. In this case only 2):
Criteria1 in Column 4="yellow"
Criteria2 in Column 3="ggg"
And only show values of columns 1 and 3.
If were using an SQL query would be something like this:
Code:
SELECT F[1],F[3] FROM "Table" WHERE F[4]="yellow" and F[3]="ggg"
The output array would be as below:
Code:
b=[{12,"ggg";140,ggg}]
this is the array:
Code:
Sub test()
a = [{"122","53","ggg","yellow";"140","9","ggg","yellow";"16","-22","ddc","yellow";"127","-37","ddc","green";"53","-28","ggg","grey"}]
'Filter code to get array b
'
'
End Sub
View 1 Replies
View Related
Feb 28, 2013
Using VBA, I am running code that filters the data in my column "Account" on my main sheet, creates a new sheet for each account in that column then copies the filtered data to the new sheet. There will always be the same list of Accounts with the exception that there might not be data for one or more.
In that case, I still need it to create a new sheet with the account name but just have some text stating "no data available for his account".
I can find code to search if a specific sheet name exists but, in my case, I don't know which one won't have data at run time.
I was hoping I could put a static list of the expected sheet names into an array and loop through that list and create the sheet for any that it can't find.
My list: FTL, DTB, CAR, BLD, RSG, STS
View 2 Replies
View Related
Mar 27, 2008
excel filters.
I have excel document over 50 pages and i need to filter lines that begins with certain letters like ADS, SGH, FAQ. I know how to filter one by one but i need all 3 to filter in same time-
How can i make multiple lines to filter in one search?
View 8 Replies
View Related
Oct 5, 2013
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:?
View 2 Replies
View Related
Oct 8, 2007
I've got no clue about all this, but I've had to get specific formula examples and fill in the blanks in order for my timesheet to work. There's just one final problem if somebody could please help.
This is a timesheet for a 5 day work week. I need to count the number of unique log numbers for a specific activity. The log numbers counted must be unique across the entire week, not just for each day, which means I want the formula to count the unique log numbers across multiple sheets.
The formula also has multiple conditions. I got 2 columns. The first part of the formula needs to verify a word, say, "split" and if it does it checks the adjacent cell for a unique log number. If both arguments are true, it counts the log as 1 unit.
Here is a working formula for only one page.
=COUNT(IF(D4:D29="split",IF(FREQUENCY(C4:C28,C4:C28)>0,1,)))
Here's 2 problems with this formula:
1. I will count if it encounters a blank cell in the Log numbers the first time (which will happen as not every activity we do has a log#), but it will stop counting if it encounters a second blank cell.
2. I don't know how to make it work across several sheets.
This is an alternate formula which works and skips the blank cells, but I don't know how to add the multiple condition of "split" and to have it work across multiple sheets. I just copied it Microsoft. As I said, I don't understand it, I just fill in the blanks.
SUM(IF(FREQUENCY(IF(LEN(C4:C29)>0,MATCH(C4:C29,C4:C29,0),""), IF(LEN(C4:C29)>0,MATCH(C4:C29,C4:C29,0),""))>0,1))
View 11 Replies
View Related
Jan 14, 2010
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
View 9 Replies
View Related
Mar 15, 2007
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 .
View 5 Replies
View Related
Mar 31, 2014
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).
View 2 Replies
View Related
Oct 16, 2009
I'm trying to divide my data into 6 different groups, based on 2 different criteria. First, I am not sure how to write the logical test to take 2 columns of data into consideration (using "&" and "AND" do not work; I am not sure what else to try), and second I can't figure out how to write the formula so that it can select from 1 of 6 conditions.
So, overall here is what I want:
If DL2=3 and CK2=1, then I want this to be labeled as '1'
If DL2=3 and CK2=2, 2
If DL2=1 and CK2=2, 3
If DL2=2 and CK2=2, 4
If DL2=1 and CK2=1, 5
If DL2=2 and CK2=1, 6
These 6 conditions cover all possible combinations of numbers in the two columns.
View 13 Replies
View Related
Nov 29, 2008
My data looks somewhat like this :
X Y Z
DOU Quarter Sum
0 Q1 = sum of 1's in Q1
1 Q1 = sum of 1's in Q2
1 Q2 = sum of 1's in Q3
0 Q2 = sum of 1's in Q4
1 Q3
1 Q1
1 Q4
0 Q2
A cell in the SUM column must contain the sum of 1's in column DOU if under column Quarter the value is Q1. Similarly for Q2, Q3 & Q4 if only the corresponding value under DOU is 1.
Its like.. count the number of 1's per quarter and come up with the sum in 4 distinct cells for each quarter.. i.e. quarter-wise sum/count of 1s
View 9 Replies
View Related
Aug 9, 2009
If ActiveCell.Value < -25 And ActiveCell.Offset(-2, 0).Value < -25 And ActiveCell.Offset(-3, 0).Value < -25 Then
Range("C4").Select
End If
End Sub
I am looking to do something similar on excel 07 as follows:
Range("O16").Select
IF ActiveCell.Offset(0,-1)Value ="1" And ActiveCell.Offset(0,-2).Value = "1" Then
ActiveCell.Value = "1"
Compile error expected: Then or GoTo At the First value (which I have highlighted in red)
Also would like the If formula to repeat for range O16:O36.
View 9 Replies
View Related
Jan 29, 2007
see attached workbook.
I need to create a conditional sum in the report tab in the yellow cells that will sum all the values from the list sheet, based on the following conditions.
1) The name is in the list
2) The code in the list is one from the codes tab.
I saw somewhere that I need to put an 'OR' statement in the conditional sum but I can't get it work. It seems to add up values for the code 'HOLIDAY' and I don't want it to.
View 3 Replies
View Related
Nov 29, 2013
I cant seem to rack my brain around how to go about making a formula in order to achieve my desired effect using the IF Formula. The data is as follows:
Worker Status %
S1 75% and higher
S2 42.8571% -74.9999%
SH Anything below 42.8571%
My sheet pulls the workers status from a preset data table, lets say S2 for Joe. I want the cell beside his status to compare what his percentage is and display what the status should be (based on the conditions of the above table). If hes working 73% it will turn up S2 and I can compare the value from the table and the value generated based on his working percentage (I would use conditional formatting to color the cell red or green to indicate if it matches or not.)
View 4 Replies
View Related
Dec 17, 2013
Is VLOOKUP suitable for if one wants to lookup a value from a defined range based on two assumptions?
Enclosed is an example file.
I want to lookup the values in sheet1 in the defined range in sheet2 and write enclosure and density to sheet 1
what method to use?
nicolaiBook1.xlsx
View 6 Replies
View Related
Mar 1, 2014
My spreadsheet looks like this:
Column A: Individual
Column B: Not used in formula
Column C: Not used in formula
Column D: Location
Column E: Duration in Minutes
Column F: Pay (Column E)*(Column G)
Column G: Formula
(simplified version listed below)
[Code].....
To be clear of what I'm trying to achieve:
When in location 1 & 2 (Column D), the pay for each individual is a different rate depending on the duration in Column E, but when in Location 3, the rate is consistent no matter what the duration.
My question is:
How do account for individuals who aren't individual1? That is, my understanding of the above formula is that I need to list each individual that will appear in Column A for it to work. What if I just want to apply the above to individual1, but apply some other calculations to all other individuals?
I had tried adding these lines (with their corresponding closed-parentheses at the end)
[Code]....
But it doesn't work. I'm guessing it's some sort of OR, but I don't yet grasp how.
I should note that my formulas are all on one line in my spreadsheet, I just have them on separate lines to make it easier to find my way around.
View 14 Replies
View Related
Aug 4, 2005
I have a table in Excel:
The first row is time in years.
The second row is method name,say,"A","B","C".
I want to count the number when the time is less than 5 years AND "A"
method is adopted.
I tried this:
count(if(AND(C2:Z2<5,C3:Z3="A"),C2:Z2) but it didn't work.
how to revise the formula?
In the mean time, count(if(C2:Z2<5,C2:Z2))worked as well as
countif(C2:Z2,"<5")
View 9 Replies
View Related
Jan 4, 2007
I'm trying to get a sum only if 2 conditions are met.
Column A has dates (ex. 01/02/2007) in a random order. Column B has a location in a text format (ex. London). Column C has a series of values formatted as [h]:mm.
What I'm trying to do is get a sum of the values in column C where the corresponding values in columns A and B = the date and loction I specify. I've tried...
=SUM(IF(('Jan ''07'!A$2:A$750="1/2/2007")*('Jan ''07' E$2:E$750="London"),'Jan ''07'!J$2:J$750))
but it's returning zero values regardless of the criteria being met or not.
View 12 Replies
View Related
Nov 16, 2008
I have a table like this:
Name (A).....Date-in (B)....Date-out (C)
Name1........27/12/2008....3/01/2009
Name2........25/11/2008....28/11/2008
...
I want to count the rows (in the entire table) that B and C dates intersect with a reference dates (say J1 and J2).
It is a booking table so I want to know if the apartment is available for the reference dates (i.e. no bookings for that days).
I tried first a simple double conditional to know if a date is inside two dates but it didn't work:
=SUMPRODUCT(--(B:B<J1),--(C:C>J1))
View 9 Replies
View Related
