I am trying to auto-fill a column based on certain criteria - in other words, fill in blank cells based on a cell above, just a little more complex version. I have added an attachment to give everyone a visual of what I am describing and then maybe this explanation will make a little more sense.
First, if you open the attachment, the left hand side is a small example of what I have. The middle is the condition. And the right hand side is what I'd like to see happene.
In the example, I am matching up a time and when I hit that time, I am adding on a certain number of miles per hours based on the condition and the cell above. Meanwhile, I am filling in the blank cells with the condition until the condition changes. So a cell is incremented based on the condition and gets its value from the cell above.
I have an excel sheet with 2 tabs. 1st tab provides the data for downtime of a manufacturing line. The last column (shift) is blank and needs to be filled based in shift schedule in tab2
On the 2nd tab I have the shift schedule, which tell which shift is working on particular days
Month Date 7:00am- 7:00 pm 7:00pm - 7.00am
Feb 1 Shift A Shift C
[Code] ........
Is it possible to write a macros that will look at the Date and time from tab 1 and assign the correct shift number in the shift column. For example the 1st row of tab 1 reads (Month=Feb, Date = 3:55:59 AM) so according to shift schedule it would fill C shift.
I have lots of data and to assign every event to shift # manually is not possible. So i wanted to know if there is a macros i can use to make life easier and learn at the same time
i m trying to use the sumproduct formula, and OR but i cannot seem to get this right! =Sumproduct(--(A1:A10="Yes"),--(OR(B1:B10="Yes",B1:B10="Mayby")),C1:C10)
I have also tried Array Formula as follows; {=SUM(IF(A1:A10="Yes",IF(OR(B1:B10="Yes",B1:B10="Mayby"),C1:C10)))}
I have also used UDF to for the sumproduct, but cannot make that work! keep giving me value message
Function Function Customer(Service as Range, Outcome as String, Service2 as Range, Outcome2 as String)
I have the following sample data set and I'm trying to return the appropriate column header based on criteria (i.e. DDD) and a number value which will be somewhere within the range of the table. In example below, the value returned should be Header 2 because the value is greater than those in Header 1 column (range H9 to L26).
Column 1 Column 2 January 1000 January 1234 February 1300 March 1600 January 15 March 123 April 234 January 3000
I would like a formula that adds all the January numbers together returning a result of 5249
To move this one step further i would eventually need to add these numbers based on quarters, for example if the value is January February or March in column 1 then add the numbers in column 2.
I'm sure i have done this before using a countifs maybe but my mind has drawn a blank
I am trying to get an average from one column based upon criteria from an adjacent column. The number of days to close a case for race columns Black and White are listed in B5:C16 and E5:F16 and H5:I16. I need a formula to calculate the average days taken to close cases for Males and then the same for Females. Sample below: ...
I have an personnel file with employee info, and I want to create a macro that will look in the "Master" worksheet at the Department column ("I") for anyone in Benefits, and then copy their name from the Name column ("D") into the "Benefits" worksheet. The names should begin pasting in cell "D3" but will recognize if a cell already has a name in it and then paste in the cell below that.
Here is what I have:
Option Explicit
Public Sub RatingbyDept() Dim Dept As Range
With Sheets("Master") For Each Dept In .Range("I2:I1000") With Dept
I keep getting error 92 - "For loop not initialized".
I have 2 columns; 1 with "dates" of incidents, the other with "number of incidents". The dates are not in any order but I want to be able to count "number of incidents" for certain time periods. e.g. If dates are between 01/02/14 and 28/02/14, what are the number in incidents. This number would be represented on a new worksheet.
I've looked at COUNTIF/S, SUMIF but just cant get there.
The below sample is an accurate example of a much larger set of data. Column H will be my desired result.
I need a macro that will look at the matching values (numerical value of month) of Column F, then enter the sum of the corresponding values (rows) contained in Column D for that month. The sum of each month should be placed in Column H at the last value of the row for each particular month.
I want to be able to have a macro that will compare one cell to the rest of the cells in a collum. I have used the COUNTIF() formula and it's getting on my nerves a bit because it seems as though everytime i sort the data, it totally messes up the formula (as in it changes the cell refernces to seemingly randomly generated numbers, but that is a different matter! lol). here is the formula, and it will be followed by the formula that is in the cell directly below it: =COUNTIF(F2:F65536, F2) and the one below it: =COUNTIF(F2:F65536, F3). Only one number needs to change, but when i fill down in excell, it wants to add one to every number, even when i click COPY cells... oh well. So i want to write this macro that will do just that. That way, it has fixed reference parameters e.g. F2:F65536, and one variable parameter e.g. F2, F3, F4 and so on. I'm not too savvy with this whole VBA stuff... the university insisted that everyone uses C++ nowadays, lol. I would like this macro to output its data into an adjacent cell ex. if F2 was compared, the data will be placed in H2.
In my worksheet I have a column with the number 1 in cell G3 ,I want to fill down the column with the number 1 as far as there are rows that have text in them. How do I tell Excel to fill down only as far down as the end of my table.
I am looking to count every 4th column that has a average score less than 90. My columns are set with 3 scores and 4th column has the average of those scores. Like such:
untitled.jpg
I can count them using a SUMPRODUCT(--(MOD(COLUMN function but I cannot get a criteria to work.
Attached is a workbook that better explains what I am trying to do. I am counting pastdue accounts from several different insurance companys. I have several pastdue brackets, and I need the total amount of pastdue accounts for each bracket, for each insurance company. I think the countifs function wrapped in an index match may get the job done, but I have not been able to figure it out. right now I have a countifs wrapped in an if statement, and it is producing incorrect results.
I'm not sure if this is the correct way to display my sample data, but here it is (how to change it. I looked in the guidelines, but I can't download anything onto this computer, so I can't get the HTML maker)
A B C D E F G H I
1 Descriptor Group Identifier Inventory Total Vaue Invetory Type
Group Total Incomplete Inventory Value
[Code] .......
Here is what I would like in:
The sum of the total value of half finished and unfinished inventory for each group.
So, for group one, it would be 145. For group two if would be 38, and group three would be 316. (displayed in column I)
I think an array formula would do this, but I'm not really sure how this would work.
I have a sheet with 2 columns. First Column lists sizes of Devices. Second colum lists a servername. Example:
A B 30 server_a 30 server_a 65 server_a 65 server_a 45 server_b 45 server_b 15 server_b
What I want to do is a device count, not device sum on a criteria based on Column B.
For instance whenever column B equals servername A, then count how many devices it has.
This would equal 4 instead of 190 which is the sum of the devices. I can do a sumif statement, very easily, but I don't want a sum, I just want a count.
There is probably a simple way of doing this in excel but the only way that i can think of doing it is through VBA.
There are about 25 columns and 4000 rows in my spreadsheet. I want my macro to look up 5 criteria in Sheet 1 and the same 5 criteria in the previous years spreadsheet. If all 5 values in Sheet 1 match all 5 values in Sheet 2, i want it to import a cell's value from that row in Sheet 2 to the active row in Sheet 1.
Here is the code I have so far:
Code:
Sub Import2012Numbers() Dim BlockNbr As String Dim Position As String Dim Scenario As String Dim LOA As String Dim ScenVal Dim x As Integer
[Code] ...
I think i've got everything down in terms of the variable definitions etc, but i think the problem is coming from the order or method of the for each and looping process.
I need the macro to take A2, S2, U2, W2, and V2 in Sheet 1 and look through all the rows in Sheet 2 to see if any of the rows match all 5 criteria. Only then do i want it to move on to A3, S3, U3, W3 AND V3 in Sheet 1
When i step through the code i keep getting the error message "Next without for" for i, or "For command already in use".
I've worked on this one way to long and thought I'd ask here now.
Column A Column B A 1 A 0 A 0 B Blank Cell B 0 B Blank Cell B 0 B 0
I have a third cell that I would like to count how many zeros in Column B there are that correspond to the letter B in Column A. Blank cells cannot be counted. This answer should be 3.
I am really struggling to summarise weekly budget data into period budget data by subcategory.
I have column headings A3 being Subcategory B3 being GL Code & C3 being GL Name - where column B & C are distinct values but column A are not. And then in D2 I have the period no e.g. P01, with the Week no in D3. I want to sum all cells that are in a particular subcategory and period - the data set is broken down by weeks.
I am trying to get the average of two columns column A and Column B Column A has text (survey type) i.e. Move In, Mid Year, Year End Column B has the scores given on that survey type i.e. 70 I am trying to get the average of of all the Move In cells from column A for each survey type so that I get average of all the scores in column B.
I have 2 combo boxes dependent on one combo box. When I select an item from the dropdown list in ComboBox1, I want the corresponding data attached to ComboBox1 to be available in ComboBox 2 and ComboBox3.
Example: ComboBox1 ComboBox2 ComboBox3 John Male English
Data for ComboBox1 are in column A2 (Names), data for ComboBox2 are in column B2 (Gender) and data for ComboBox3 are in column C2 (Language spoken).
I chanced upon a code written by royUK from this Excel Forum, which works well for 2 ComboBoxes. This is the code:
Option Explicit Dim rSource As Range Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Is there a way to fill all rows of a column with the same formula without having to copy and paste it? My table is huge (about 6000 rows) and I need to perform the same computation on each row.
I have a column of data with various values and a bunch of blank spaces. Essentially I want to leave the values as is but fill in the blank cells with a number. I’ve written a loop to do this in VBA, which grabs the value in the cell above, but it’s somewhat slow. Is there a more efficient way to do this?
Sub Downfill(Max) ' 'This count variable is used to run the loop Dim i As Long
What i really need it to do is if each row in column H = "Cleared" then to only clear columns A, C to H. Theres a formula in B that needs to be left... any ideas?
Also i need this to work on all atbs as the commandbutton will on a master tab
VB: Private Sub CommandButton1_Click() With ActiveSheet .AutoFilterMode = False
I have been creating a schedule on excel, the schedule includes a top row which has the following headings Date, Agent_ID, title, agent_name, 07:00, 07:15, 07:30, etc up until 21:45
The columns that are named with times are times that indicate a break time. The column named title is the actual shift time, eg 08:00 - 17:00.
I need a formula that would look at my source data, and populate a sheet in the following layout
agent_id, agent_name, title, start_time, end_time
The title be one of the following: Shift 08:00 - 17:00 Tea Break 10:00 - 10:15 Lunch Break 12:00 - 12:30 Tea Break 14:15 - 14:30
If I need to have the shift portion and the break portion appear on separate tabs that would also be ok, but ultimately I need to keep my original source as is, but the change it to be able to upload it into a MySQL database.
