In the attached sheet, i want one formula in column C which calculate the total cases based on the country by searching the data in sheet "Data".
When I select the specific country, the count should be populated automatically.
Book1.xlsx‎
I have a data to filter and count it based on the time.
As you can see on the attached file, there is a table of time range wherein I will input the total counts of the partners.
For example:
I will filter Column B with "One" & "M One".
After filtering, on the table, i will count the data that is fit on the time range.
Let say on 1:00am time range, i just want to count the data that is less than 2:00am on the filter data, and put the result on the corresponding partners. Put "0" if there is no data on that time range.
I have attached my file..
I am trying to use VBA to filter a pivot table value (Top 10 etc) based on a cell value on a seperate sheet.
1. Not sure if I reference Set pvtField = pvtTable.PivotFields("Employer_Name") or Set pvtField = pvtTable.PivotFields("Sum of Value")
2. The command filter (For Each pvtItem In pvtField.PivotItems _
pvtFields.xlTopCount _ pvtFields.Value = filterVal _ Next pvtItem) is causing difficulty.
Sub Top_Filter_1()
Dim pvtTable As PivotTable
Dim pvtField As PivotField
[Code].....
I have a column containing 8000 records, some of them are in upper case & rest are in proper case, is there any way wherein i can filter all records which are in upper case without using macros, i mean is it possible to do it using ISNA function ?
View 2 Replies View RelatedI am trying to set up a worksheet that shows shifts using a Capital "D" and a lower case "d" (one is for a 12 hour shift, the other for only 6 hours). Is there any way to set up a Countif formula that makes the distinction?
View 12 Replies View RelatedI'm fairly new to using VBA and have come into what is probably a simple problem to solve. However, I am looking to sort out my code.
VB:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells.Count > 1 Then Exit Sub
If IsNumeric(Target) And Target.Address = "$H$3" Then
Select Case Target.Value
[Code] .....
From the above the macro runs only with the Range H3, I would like have this code operate for the Range H3 to H10... I can't quite get it to work with a few formats I have tried.
It sits in the "This Workbook" code window to run each time the wbook is opened. What I want it to do, is based on the date in Sheets("Control").Range("L3") select a particular cell in another sheet called "Risk Sum". I'm assuming this is easy to do, I've come up with the following, but it just doesnt want to work ...
View 9 Replies View RelatedI have a string of names that run together without spaces or commas between each name.
"Danny TrejoJean Claude van DammeVincent SchiavelliGabrielle FitzpatrickDavid 'Shark' FralickPat Morita" for example.
Is there a way to add a comma and space between a lower case and upper case letter?
Sub Addy()
Do Until ActiveCell. Offset(0, -4) = ""
Renamer = Proper(ActiveCell)
ActiveCell = Renamer
ActiveCell.Offset(1, 0).Select
Loop
End Sub
fail? Trying to remove all capitals from names/addresses. Error message is "compile error - sub or function not defined"
I've 2 lists that contain similar info but 1 of the lists holds info I need to join.
I've a unique case sensitive id that I'd like to use to join the lists.
I usually use VLOOKUP but it is case insensitive and I cannot understand how to make it case sensitive.
The 2 lists are in 2 separate worksheets as they each hold a lot of data.
There is a Summary sheet from where data has to be copied to 3 different sheets(for 3 accounts) I have highlighted the columns that have to be copied. The macro will be run everyday, and each day only 3 rows from Sheet ‘Summary’ will be copied to the other sheets.
Important point: Only copy rows that have Column D value = GBP
Which Sheet to copy to? Check Column A ---
Case 100 = Sheet(“ONE”)
Case 200 = Sheets(“TWO)
Case 300 = Sheet (Three)
The Orange ones – Column C,I, and N are copied as follows: C goes to Column A, I goes to Column C, and N goes to Column J in one of the 3 output sheets.
It should not overwrite previously existing data, simply copy to next empty Row (By Empty Row I mean Column A is empty, columns after M may have values in many rows)
Then the Yellow columns. I need them copied on W26:W32. So that’s cells in Column E,J,K,L,M,R going to W26:W32. These yellow column figures must OVERWRITE previous data (unlike the Orange). The Blue cells that you see in Sheets (ONE,TWO and THREE) contain formulae that I want copied down to the Next Row when there is an entry made in Column A.....
I'm trying something interesting with Select Case where instead of using a static Case, my Case is based on the value of a cell.
here is the example of the present code:
Code:
Select Case Range("F75").Value
Case Is > Range("F30").Value
Range("AB59").Value = "Use"
[Code].....
I've got a column of data that I need to split into further columns, the only way of identifying the split from the data is if a lower case character appears next to an upper case character.
For example, a cell in column A could look like;
Computer ProductsDrivesDVDExternal
And I'd need to split that out to cover columns A B and C with
Computer Products / Drives / DVDExternal
I'd need to do that for all the cells in column A.
Is there a way to change the text in a cell to proper apart from 2/3 letter words which I want to keep as upper? Basically can it ignore all words that are 2 or 3 letters long, but change all other words to proper text?
View 3 Replies View RelatedI've got a pretty intense macro already written, a lot of Select Case components. At the end, if nothing matches I'd like to just copy the cell above to the cell below. However, there is a range of about 400 cells in length, so I'd need some sort of wildcard for range.
Rows("2:2").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Dim Cell As Variant
For Each Cell In Range("A1:OL1")
Select Case Cell.Value
Case "Eng1"
Cell.Offset(1, 0).Value = "Engine One"
tons more in the middle here
Case Else
Cell.Offset(1, 0).Value = "N/A"
Rather then returning "N/A", how could I reference the cell above and just copy it instead?
I have a challenge when using autofilter.
In the attached file I have sales reps with sales numbers.
I found out from Ozgrid to use subtotal formule in C2 to sum only lines visible. Ozgrid, thx a lot for that input.
However, in cell C3 I want to count number of records, but when filtering sales rep "A", then formula "counta" still counts all records, i.e. 27.
What formula should I use to count only visible records, e.g. 7 records for sales rep "A"?
I've got a spreadsheet with over 60,000 entries each with a different post code. I want to filter and then count to find the most re-occuring post code. However, I want to do this just on postal area, so I need to edit the info so the last 3 characters of the post code are removed, eg...
SE10 4BX
becomes
SE10
other than doing it manually is there anyway to just command excel to delete last 3 characters from my list?
I decided to try to change it into a Case Statement. Here is what I have now. But the problem seems to be this time at this line: When I have "01" in C5 the script just keeps going?
View 14 Replies View RelatedWhen I use a simple formula such as:
=upper(a1)
that will obviously change whatever is in a1 to Upper Case - but it will put it in the cell that holds the formula.
What I want to know is:
Is there any way I can format the cell to run the formula when the information has been pasted into the spreadsheet
I have a spreadsheet that includes a column with location names and a column with location numbers. What I need to do is count the visible unique numbers and names (seperately) in a certain column when using a filter. I have found the formula to count the unique values and a formula to count the visible cells, but not a formula that does both.
View 5 Replies View RelatedData: four variables A,B,C and D from A2:D6000
I want a count of unique D values
when A=xxx
and B=yyy
and C=zzz
I have named the A,B,C,D ranges as RangeA, RangeB, RangeC, RangeD
I have used the function
=SUM((RangeA="xxx")*
(RangeB="yyy")*
(RangeC="zzz")*
But what do I use to count-unique values of variable D ?
=SUM(IF(FREQUENCY(RangeD,RangeD)>0,1)) doesn't work.
=SUM(1/COUNTIF(RangeD,RangeD&"")) doesn't work either
They don't work because D can occur during different combinations of A,B and C. In other words, the D value of 'Smith' can, and does, occur when A is 1,2 or 3 and additionally when B is Pass, Fail, Withdrawn.
They "don't work" because I can use filters on A, B, C and D to see what the correct answer should be .. and the function doesn't return the correct answer.
I hope I've explained this ok - I've been searching on several excel-help websites for 2 days now and one of the days my client is going to want some results.
Attached I have a spreadsheet with two columns. Both are dates. One is Due Date and the other is Completed Dates. Each row represents a WO (each WO has a due date and complete date). This is what I want to extract:
- I want a count of how many WO's there are in the spreadsheet to start with
- then I want to to be able to apply this fillter = Complete Date is equal to or less than Due Date (basically, I want to see how may WO's where completed after the due date).
- then I want the spreadsheet to automatically calculate the count of WO's with the filter applied
I filtered my database in such a way that i end up with a list in which different companies are shown.
For example:
Companies (column A)
MER
MER
TYH
INT
MER
TYH
TYH
Now i want excel to count for me the number of different companies active (so in this example it would be 3, that is MER + TYH + INT). Does anyone know how to make this formula?
I am working on project of doing Turn around time Analysis of the volumes of work recieved before 12:30PM and after over a weeks time. My data consist of Work ref, Datein, Dateout, Timein, Timeout. To carry this analysis I have to Mannually filer the data as per Datein / Timein and Dateout and then count the no of work ref completed within Same day or one day or more than one day for each date of a given week. I recorded a Macro which gives count of no of work. for one particular date of week and repeated the same for each 5 working dates of given week. this Macro has gone to big and I want to use some looping technique which would change my filtering criteria. I tried doing this by assining a variable to Criteria but Macro gives no values.
Range("A1").Select
Selection.AutoFilter
Range("B1").Select
Selection.AutoFilter Field:=2, Criteria1:="=04/02/08", Operator:=xlAnd
Range("E1").Select
Selection.AutoFilter Field:=5, Criteria1:="<=12:30", Operator:=xlAnd
Range("IV1").Value = "=SUBTOTAL(9,H2:H272)"
Selection.AutoFilter Field:=9, Criteria1:="=L*", Operator:=xlAnd
ActiveSheet.Calculate
Range("sheet1!B3").Value = Range("IV1").Value
Selection.AutoFilter Field:=9, Criteria1:="=S*", Operator:=xlAnd
ActiveSheet.Calculate
Range("sheet1!B4").Value = Range("IV1").Value
I want to repeate the above code for different dates of week 04/02/2008 to 08/02/2008. How to change Criteria using Loopin technique.
I need to count the unique items in a range that don't contain the string "option". I would like not to use VBA for that but I can't make it work with a simple formula.
For now, I have :
[Code] ......
That doesn't count the cell with the content "option" and count correctly the other unique items. However I need to ignore any cell that contains the string "option" (with wildcards).
I have tried and tried to get the VBA code working that will tell me the number of visible rows in an autofiltered set of data, but the result I seem to be getting is always "1". Below is the most simple form of the code that I am using (it is based on previous posts and tutorials on this site). (I have also attached a workbook with sample data and the code)
With ActiveSheet
Set rnData = .UsedRange
With rnData
. AutoFilter Field:=1, Criteria1:="5"
.Select 'demonstrate that the rnData range is valid
lcount = .SpecialCells(xlCellTypeVisible).Rows.Count
End With
End With
For the following code, I'm getting the " Case without Select Case" error (On Case 3 to 5...assuming more are wrong too, but debug can't get there yet). I thought I had it right, obviously don't. Can anyone spot how my code is wrong? ....
View 9 Replies View RelatedI'm trying to compare two range values within a macro to see if they match...if they do/don't I capture and write some other data.
One list is lowercase, while the other list is UPPERCASE.
My current macro needs them to be in the same case because I'm using the following to compare:
If VPNID.Value = BuildHRName Then
How can I change the format of one of the lists to UPPERCASE or lowercase so that I am comparing apples to apples?
if there's any way for vba to detect if each individual character in a string is in caps, and if so, convert to lower case, and if it's in lower case, convert to capitalized
The text will vary in length and content... so he wants to see how we can change:
"This Is Strange" to "tHIS iS sTRANGE"
"THIS IS STRANGE" to "this is strange"
"this is strange" to "THIS IS STRANGE"
In the advanced filter, I am given two criteria one is filter by unit price by less than 1000 and order no atleast 100 .
View 6 Replies View Related