Percentage Of A Value After Filtering
Sep 17, 2009
I would like to know whether it's possible to evaluate the percentage of a text value ie 'Approved', after filters on other columns have been done.
AREA YR DECISION
HN 09 APPROVED
NYM 08 REFUSED
RC 09 APPROVED
HN 09
RC 08 REFUSED
? %
So if I filter by AREA and/or YR, I would like the % of how many were 'Approved' in that filtered range (ignoring any blank cells in the DECISION column).
So far I have a basic
=TEXT(COUNTIF(C2:C6,"*Approved*")/ROWS(C2:C6),"0% ""Approval Rate""")
but it doesn't work once I filter by AREA or YR, and doesn't ignore blanks.
View 5 Replies
ADVERTISEMENT
Apr 3, 2013
I have a workbook that I've built for a project. I've attached a sample workbook. What I'm trying to do, for the entire sheet or workbook if possible, is turn any Cell with a percentage of 30% to 49% yellow and any cell with a percentage of 50% or more Red. I would also like to move the ID's of the variable cells, for example Id number 9922, to the cells beside the description of the rows, Affected would be an example, if the information contained in the same row as the ID meets with a set of variables.
For example I only want the ID's moved if they correspond with IDsub 1-25. One more thing, the people who will be using this spreadsheet will be copying data from a website when it is imported it does not insert the values as numbers. I would like to format the cells, in example workbook they would be any of the cells labeled ID IDsub Variable or Number, so that anything put in that cell will automatically be converted to a number.
I should also probably add that the formatting will be done on approximately 80 rows a sheet with 47 sheets.
View 2 Replies
View Related
Apr 26, 2013
calculating the percentage of a percentage and writing the formula for excel.
There are 295 people in a room, of the 295, 75 or 25% are mothers. (I know how to calculate 25% - 75/295 = 25.42) of the 75 mothers 35 have 3 children, 32 have 2 children and 10 have 1 child.
35 is what percent of 25%
32 is what percent of 25%
10 is what percent of 25%
View 3 Replies
View Related
Nov 10, 2009
I am having an issue filtering data in a spreadsheet:
Each set of information has 5 lines of information (title, information, manager, status, sponsor) and the first column of each data set is a merged 5 row box with the area of operation (this does not have to be merged if this is creating the issue). I need to be able to filter by area of operation and have all 5 rows showing. Right now, Excel only shows me one row for each set of data with the area of operation as one row.
View 2 Replies
View Related
Mar 11, 2014
I have a spreadsheet with Row 1 as the header and Row 2 as sub-headers. I want to filter automatically to 2nd row, but whenever I press on Ctrl+Shift+L, I have the first column filtered. I have another spreadsheet that whenever I press Ctrl+Shift+L, it automatically filters the 2nd row instead. I'm not pretty sure what's the difference. I have my spreadsheet frozen on cell B3 (not sure if it has something to do with freezing cells. I know I can select 2nd row before pressing on Ctrl+Shift+L, but is there some other way to have this automated?
View 2 Replies
View Related
Feb 15, 2009
If I give excel the advanced filter criteria <=2/15/2008 it seems to list all the dates prior to and on that date. Are there any pitfalls to this filtering technique (i.e. would any dates fall through the cracks)?
View 2 Replies
View Related
Nov 14, 2008
I'm quite newbie for VBA for excel, my friend asked me a favor to filter the data on his excel, so when he enter some keyword, the table would sorted (only show the row that have that keyword), and if possible that he enter it few keyword.
keyword example VB, oracle
table example ....
View 10 Replies
View Related
May 13, 2009
I have an excel with the following information
A1 - Is a dropdown which will have names of the systems (EAI,Mediation,CDF
..,All)
Row2 will be header which will have following data
B2 - Project Id
C2 - System ID
D2 - Project name
E2 - Project Phase
F2 - Status
from g2 to GN2 there are about 60 resources names entered
From C3 to F3 the data wil be entered and appropriately a chk mark will be
made to the corresponding resource wroking on that project
Say i have about 10 systems provided as dropdown for entering data in A1
(Ex: EAI, MEdiation, .....)
There are resources working for each system
when i apply filtering on a system name i shoud be able
to show only those resources working for that system and all others should be
hidden
Also under the "system" dropdown i will have an option "All", when "All"
option is selected i should be able to see all the data for all the systems.
EX: if i take system "EAI" and if the resources working for eai are there in
AO2 to AX2 then i want the resources from AX2 onwards and before AO2
not to be shown.
If i take a system "Mediation" and if its resources are from Z2 to AI2
then i want resources before Z2 and after AI2 notto be shown.
If "All" option is chosen then i should be able to see the whole sheet
with all the data for all the systems and all resources.
View 12 Replies
View Related
Oct 9, 2009
I have a filter that works in Excel 2007, and I'm trying to figureout how to make it work in Excel 2003. OrderNums is an array of multiple criteria. The below code works perfectly in Excel 2007.
View 3 Replies
View Related
Nov 3, 2011
I've filtered my data - such as selecting a date range - and then run the pivot, but the pivot ignores the filter and gives me data for all the dates. my workaround is to copy and paste the filtered data to a new sheet and run the pivot off that sheet. This works fine, but maybe I can avoid this step?
View 1 Replies
View Related
Dec 11, 2013
I have a worksheet with data in named tables on several sheets. Each of these tables has a column called "filter" (this is not always the same column number between sheets). The values in this column are either 0 or 1, depending on if that particular row is relevant under the book's current selections.
I'm trying to figure out the code that will take a table and filter it to show only the rows which have a 1 under the "filter" column.
View 2 Replies
View Related
Jan 31, 2014
I have added below code in module to get list of parts which are not delievered.
I am trying get code which will end the macro as soon as its do not find mentioned crieteria i.e.
("Not Delievered")
Selection.AutoFilter
ActiveSheet.Range("$B$1:$B$5").AutoFilter Field:=1, Criteria1:="Not Delievered"
View 1 Replies
View Related
Apr 14, 2014
I have a sumif function in a cell which adds values from a table from another sheet. When I apply a filter in the table, the sumif still includes the invisible rows. Is there a simple way to add only the visible rows with a function based on a criteria like with the sumif. i.e. after certain rows are not visible, still a further slection has to be made base don another cell in the same row.
View 2 Replies
View Related
Jan 22, 2007
I have a worksheet with about 1000 entries and I have this is column F so it can sort out all Euston entries into 1 block. =IF(E1="EUSTON",0,1) What I need to do also if it is possible all entries in column B end in even no's it sorts it out into the block that Euston is in and if their odd no's they go into the other block. Dont know if I can add to this or need something else.
View 9 Replies
View Related
Feb 2, 2007
to dynamically filter a list as shown below
View 9 Replies
View Related
Feb 26, 2007
I have a list of addresses (including street numbers and names), but want to select out addresses that match another list of street names only. I have only used vlookup before, but because one list included street numbers as well, I can't find exact matches.
I am using excell 2007.
View 9 Replies
View Related
May 30, 2007
I have a three columns of data in A,B,& C.
In column A a From Date
In column B a To Date
In column C a 6 digit number
I would like some code to achieve the following in this order:
1) Find any duplicates of the 6 digit number in column C and then place them on the same row as the first.
I.E if the following three matches are somewhere in columns A, B and C:
A B C
08/07/2007 22/12/2007 731655
22/12/2007 29/12/2007 731655
29/12/2007 05/07/2008 731655
I want them to place on the same line in date order like:
08/07/2007 22/12/2007 731655 22/12/200729/12/2007 731655 29/12/2007 05/07/2008 731655 (this line would be on the line above)
2)After all the above has been done I would like the code to go through and looking for matches of dates between column B and A
For example if I had the below two dates somewhere in the list:
A B C
08/07/2007 04/08/2007 729955
04/08/2007 01/09/2007 729293
I would want the bottom one to be moved to the same line as the top like:
A B C D E F
08/07/2007 04/08/2007 729955 04/08/2007 01/09/2007 729293
If there are more than one match like below:
A B C
08/07/2007 04/08/2007 729955
04/08/2007 01/09/2007 729293
04/08/2007 01/10/2007 729273
I would like the following:
A B C D E F
08/07/2007 04/08/2007 729955 04/08/2007 01/09/2007 729293
04/08/2007 01/10/2007 729273
3) AFter this was completed I would like to look for date matches one day apart for example:
A B C
08/07/2007 04/08/2007 759955
05/08/2007 01/09/2007 759293
And then place the bottom set of date along side the top like:
A B C D E F
08/07/2007 04/08/2007 759955 05/08/2007 01/09/2007 759293
View 9 Replies
View Related
Sep 16, 2007
I've been trying to write code to filter a list of date base on day of week (Mon, Tue, etc)
My problem is that i can't find a way to code the filter criteria... since the date does not contain any week of day constant. i don't want to create another column in my sheet to show the week of day constant and filter this instead.
Here's my not yet finished code...
Sub DayOfWeek()
Dim sAnswer As String
Dim bWkDay As Byte
Dim loMyData As Range
Dim iFilterCriteria As Integer
Dim Dateserial As Integer
Application.ScreenUpdating = False
sAnswer = InputBox("Please select day to display data (first 3 letter of the day, eg. mon)", Title:="Select day to display")
View 9 Replies
View Related
Nov 29, 2007
in the table i have
A1-apples
A2-bananas
A3-eggs
A4-balls
A5-bananas
A6-apples
A7-apples
I need one list that will show me all items that are on list so i would get:
A50-apples
a51-bananas
a52-eggs
a53-balls
to simpilfy this list would show me what types are there in the first list
View 9 Replies
View Related
May 12, 2008
I have a simple excel sheet and two of the columns show a Due Date and a Received Date. All I want to do is highlight any cell in the Received Date column where the Received Date is greater than the Due Date. The spreadsheet shows Purchase Orders and shows when they were due to be received and when they actually were received. i'd like to be able to show all orders that were received past their due date.
View 9 Replies
View Related
Aug 4, 2008
I hav a 2 columns. Column A contains data for 2 engineers, "paul" and "john".
Column B contains the hours they work each day for a month.
i would like to filter "johns" results and find out how many hours he works in total. i do not wish to use the autofilter, is there an easier or quicker way of filtering it???
john 11:03:08paul12:04:09john 15:11:44paul18:09:00john 05:34:24
View 9 Replies
View Related
Jun 4, 2009
Is there a way to filter my data with the combobox and have the listbox reference the filtered data directly?
I am populating a listbox from a rowsource, housed in a sheet in my workbook, the sheet in my workbook is populated from another sheet that is filtered by a combobox selection and pasted to the sheet with the rowsource range.
My listbox is a multiselect with 5 colums and columnheads, named ListBox42
The sheet with the data to be filtered is named Building Material
Building Material is filtered in column “M” with a color
With ComboBox24 selected name: Names “M1” which activates the color down the column for filtering.
The ComboBox24
Private Sub ComboBox24_Change()
Worksheets("BuildingMaterial").Range("M1").Value = ComboBox24.Value
Sheets("BuildingMaterial").Select
ActiveSheet.Range("$M$1:$M$4247").AutoFilter Field:=1, Criteria1:=RGB(0, _
176, 240), Operator:=xlFilterCellColor
Sheets("matfilter").Cells.ClearContents
Sheets("BuildingMaterial").Select
Range("B1:F4249").Copy
Sheets("matfilter").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("BuildingMaterial").Select
ActiveSheet.Range("$M$1:$M$4247").AutoFilter Field:=1
Application.CutCopyMode = False
Sheets("matfilter").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Tom").Select
End Sub
My RowSource is a defined name “=matfilter!$A$2:$E$4230 “
View 9 Replies
View Related
Jan 20, 2010
I have set up a spreadsheet with a drop down box. Which says "Show All", "Show Pending" and "Show Completed" This is in Cell H7
Below that I have a table of information from A10 to I1200. These are a list of jobs
In column I is the date the job was completed, if it has a date the job is done, if it has no date its still pending. I wanted a macro that meant if you click on Show all, all jobs show, if you click on Show Completed on the jobs with a date in column I show and if you select Show pendning only the jobs without a date show
I did try this by recording a macro but failed miserably
View 9 Replies
View Related
Feb 25, 2010
I am trying to filter a worksheet based on an array I set up. Below is the code that sets up the array and then the code for the filter. The problem is the filter isn't filtering anything and showing no results.
With Worksheets("UPT")
VAcusipz = .Range("F2", .Range("F" & Rows.Count).End(xlDown)).Value
End With
ActiveSheet.Range("A5:I80000").AutoFilter field:=2, Criteria1:=Array(vacuspiz), Operator:=xlFilterValues
View 9 Replies
View Related
Feb 14, 2013
Found the below code that Dims r as 1. This would work fine if my data began from A1. I want to apply the search and filter from A18 onwards. On attempting to change R to 18 it worked with the exception it did not filter the last 16 lines (They should not have appeared).
[code]Sub Show_Only_Name_AinU_Balance()Dim r As Long 'rows to checkDim Hiders As Range, Found As RangeDim Cond As String''The Name you want displayed is from cell E5Cond = Worksheets("CLSSI Home").Range("E5").Value'Sheets("AinU Balance").SelectApplication.ScreenUpdating = FalseCall Show_All 'Unhide previous names if anyFor r = 1 To ActiveSheet.UsedRange.Rows.CountSet Found [code]....
View 1 Replies
View Related
May 1, 2014
How to highlight, filter, or otherwise in someway mark all records on a spreadsheet that do not appear on another spreadsheet?
On Worksheet1, I have columns of data for First, Middle, Last, Clock Number, Address 1, Address 2, City, State, Zip Code and Employment Status. On Worksheets 1 and 2, the data and columns are 100% identical, except Worksheet1 has names I want excluded from a database import process I'll be running on the 2nd worksheet.
Is there an easy way or a quick VBA script that would allow me to highlight or filter out of Worksheet1 any rows that are not in 2?
View 1 Replies
View Related
Mar 28, 2014
how to filtering data from the columns please (not the rows)..?
Usually we could filtering the rows with auto filter, right..?
Now, is it possible to do that with columns..?
View 8 Replies
View Related
Jul 25, 2014
I am working in a worksheet with 15k rows of data. I have sorted and added subtotals. Is there a way to remove data based on number of occurrences? Example, if the same account number is listed less than times in the worksheet, i would like to eliminate the account number from the data. How can i achieve this?
View 1 Replies
View Related
Mar 31, 2007
I'm building a yearly workbook with multiple worksheets (i.e. one for each month, quarterly reports and one final yearly report). My question is that although I can have the data transfer easily to my quarterly and year end report sheets, I can't seem to figure out how to filter my data and then calculate an average based on personnel averages in the various categories I have set up.
My example is that I want to be able filter my personnel shifts (i.e. 7am, 3pm, 11pm, etc) and then set up a formula which can for example calculate a column of data and average it only based on the employees working that particular shift. My formula should adjust for the shift time I pick.
View 10 Replies
View Related
Jan 28, 2008
I am trying to filter three columns of data to a given range without having to using auto filter on each column. I would like to have a Macro or Control Button or something easier than picking each set of data each time I want to filter.
Attached is a sample file.
I am not well versed in Excel and this is my first post so please bear with me in this process.
View 14 Replies
View Related
