Is it possible to use VBA to calculate the number of numerical occurance within a column of data, and then categorise the results (onto another sheet) based on a grouping variable found in another column of data? I've been trying to create a macro to do such a thing, but I'm not even close ....
I'm using WinXP with Excel 2003 - I have a column of highway sign description data (16k+ rows).
Example:
Curve Arrow Right Curve Arrow Left Turn Arrow Reversing Curve Arrow Right Winding Road Arrow(plus many more unique entries)
I'm using SUM and COUNTIF to total the number of times "Curve", "Reversing", "Turn" and "Winding" appear in the column.
My formula is: Code: =SUM(COUNTIF($F11:$F16196,{"*CURVE*","*REVERSING*","*TURN*","*WINDING*"}))
Which works great EXCEPT what I really want is the number of cells with any of those key words, not the total count of those words. The example above should be 5, but since row four contains more that 1 of the key words I'm getting 6.
I have a list of ID# (Col A) and dates (Col B). The ID # of reference is Col (F) and today's date (Col G). I need to count how many future dates there are in the list (Col B) that are unique and in the future from today's date.
I'm still fairly new to all these formulas and functions which is why I'm not attempting this in VBA.
I've attached an example work book and I would like the result in H2.
I have a few pages of information and have grouped rows collapsed to show only the summary information. When I try to print only visible summary rows, whether I use print, print selection, copy and paste to another sheet or copy and "paste special" values to another sheet, all the hidden/grouped rows print or the summary rows print separately on different pages. Is it possible to print only the summary rows.
I have sheets with over 40000 rows. I grouped them in 60 and I need the average for every group on the 61st (which is blank). For example, when collapsed I have visible the following blank cells:
A61 A122 A183 A244 and so on.
What I want is to enter a formula in A61 for the average of A1:A60 then drag down and have the correct values in all the rest (average of A62:A121 in A122, etc.).
I have a workbook (Sort Test.xlsx) where multiple rows belong to the same "group". Is there a way to sort by a value (e.g. name of the test, date of the test, etc.) while maintaining the formatting and keeping the "groups" together? There are 3 sheets in the work book. The first shows the sheet as is, the other two are examples of how I would like to be able to sort the data.
Basically, I have a table with rows as person, and variables as features. If a person meets a feature, the that cell writes 'X', now, I hope to create a master variable that capture all the features, basically counting how many features a person has met. For example, Joe has three features; "have balls," "cool guy", "good with ladies". In the cell in question would be "X" "" "X". And if I count, then it should be 2.
I have a protected worksheet which enables only certain sections to be edited, data inputs etc. I also have a set of rows grouped which need to be activated depending on data type for particular projects to be captured. Problem is, when the sheet is protected, when users click on the + and - buttons to either ungroup and group the rows depending on type of project, this feature is disabled.
I get a popup alert that tells me "You cannot use this command on a protected worksheet. To use this command you must first unprotect the sheet ....". Problem is I don't want all users to have access as giving them the password defeats my purpose of protecting the sheet.
What I'm trying to do is input a formula in col G which will look for instances of the city named in col F in both cols A and C. This should then return the total of these, from cols B and D that have the letter "F", into col H. Therefore, in the attached example, cell G2 would return "1", G3 would be "0" etc.
Should I be using VLOOKUP or COUNTIF, or maybe a combination of these or something totally different?CityCodeCount.xls
is there a formula that gives a true or false statement upon searching a list (ie A2:A6) and finding multiple entries? like, when i search A2:A6 and there are two fields that contain the word "hello", is there a function to say "true, there are multiple entries of the word 'hello'"
I need a formula to count the number of times the same thing occurs in a particular column. i.e. tree 1, tree 2, tree 3. I just tried the countif function and it is returning "0". Not sure if that's because the data I am looking for is part number/part text. Does this make a difference? There are also spaces between the number and the text. Also, the range I am asking excel to look in is based on a concatenate formula.
If you can see the example spreadsheet that has been uploaded, I need VBA to try and count how many times the number 1, 2 or 3 occurs in the column A and give that figure in b15, 16 and 17 respectively. I also need it count the number of times a particular product appears in column B and give those figures in b19, 20 and 21.
I'm working on a spreadsheet that contains a list of various instances of zip codes between a couple of months. I'd like to count the number of times each particular zip code occurs within each month. And, if possible, if I could get that count to populate into another cell on another sheet within the spreadsheet,
I have several groupings of rows in worksheet. I need to have each separate grouping be able to automatically expand or collapse based on a value in each group.
This might be really simple but i don't get it. I have a column with country names (strings). There would be 5 instances of "USA", 10 of "UK", etc, etc.
I made a column next to it, where i want to count the number occurances ....
I found this code for a button, so I can collapse and expand a set number of rows within that sheet. It works exactly the way i want it to, however, now I have a second sheet in my workbook, and I used the same button. Problem here is that when I activate the button, it opens the same rows in EVERY sheet in the workbook. "For each ws in Worksheets" so my question is what is the term for it to only work on a certain worksheet?
Code: Private Sub ToggleButton1_Change()Application.ScreenUpdating = False Dim ws As Worksheet With ToggleButton1 If ToggleButton1.Value = True Then For Each ws In Worksheets
[Code] ........
Other questions is about the ability to send an MS Outlook email from inside of excel.
I've been trying to find something that does the following:
Click button, Form pops up, has drop downs to select recipient, has field for subject, has field for message body, sends email.
I have a workbook consisting of two sheets: a Monthly Class Schedule (Divided into five one-week blocks. Each block's vertical is Mon to Fri, the horizontal is 9 class slots. There are 45 class slots pw). Each class has one student and the cells are filled 'Student Number, First Initial, Surname' eg, '666 J Smith'. Students may be scheduled for several class slots per week and some class slots have no students scheduled. When the student attends a 'P'is added at the beginning of the cell eg,'P 666 J Smith'.
The Student Attendance Report has the students listed vertically on the left, arranged by Student Number, and then a column for each week.
At the moment I have to manually count the classes each student has attended for each week and enter the attendance figures in the appropriate week column. It's a PitA so I want to automate the process by using the S#s in the Attendance Sheet to search the week blocks in the Schedule Sheet for 'P S#' and auto-fill the week columns in the Attendance Sheet.
I need a formula which I am using to count instance of sickness. I include a sample copy of my data which I hope will make my request clearer! I'm currently using the below formula to count the number of continuous instances of any code starting with "SICK" =(SUMPRODUCT((LEFT(C$10:C$37,4)="SICK")*(LEFT(C$10:C$37,4)<>(LEFT(C$10:C$37,4)))))
I would also like this formula to count any code starting with LTS as well (Long Term Sick). Due to the nature of sickness there may be continuous periods where both SICK and LTS codes are used, at the moment I can not come up with a formula to return the value I need. These columns may contain other codes but for simplicities sake I have only shown the relavent ones.
I'm doing a study of when people are most productful in their day. I just need to count how many units a person made within say 4 hours of starting work.
I am trying to write a macro to clean up a CSV file which automatically hard codes titles throughout the spread sheet. I am getting an error on the "FindNext" method below. I am getting "Unable to get FindNext propety of the Range Class" error. Also, the code for the For loop for multiple lines does not appear to be working.
Sub cleancsv() lastRow = Range("A1").End(xlDown).Row pmpt = InputBox(Prompt:="What text are you looking for?", _ Title:="Text", Default:="i.e.: Finished Goods Inventory") numrows = InputBox(Prompt:="How many rows to delete (counting original):", _ Title:="Number of Rows", Default:="i.e.: 1") Set cell = Range("A1:A" & lastRow). Find(pmpt) If Not cell Is Nothing Then For l = cell.Row To cell.Row + numrows Range("A" & l).EntireRow.delete Next l End If Do
a quick macro/VBA project that would allow my to duplicate existing rows based on the count in one of the cells. For example, my data currently looks like this: