A B C D
COL 0
COL 0 1
COL 0
COL 0 1
COL 0
EDM 0
I need to add the number of 0's in column B if Column A is COL and C OR D is 1. correct function yields 3 for this example. I've played with =sumproduct(if)) and =sumproduct(if(and))) etc... can't figure it out.
I have data in three columns. The first column contains the machine name (Robot 3, Robot 4, and Robot 5), the second column contains the paint color (1-20), and the third column contains the data that I need to sum. For example the data could be paint purges, the number of purges from each robot. Each row of data will contain a number of purges for each robot, for each color. The data is provided by day, so I will be summing the data weekly.
View 8 Replies View RelatedI'm working on a workbook that will combine a cash flow report by category and also by date. The categories can change (which I think is the easiest part) but so also can the frequency of data. For instance, you choose how you want to see the data, monthly, quarterly, annually and this will change the Row that the data should be summarized based off of.
Examples:
1) Annual: Sheet 1: Row = Rental Revenue and Column = 2001. I want to pull from sheet 2 all of the Rows that are mapped to Rental Revenue and all of the Columns that are mapped to 2001.
2) Quarterly: same idea but by quarter.
3) Monthly: No issue, just a SUMIF.
In Sheet 2 I have added a mapping at the top of each column for the Quarter, Year or Month that should be included in the Sheet 1 heading Row.
I tried using a sumproduct on both row and column using the whole data set underlying, but this did not work.
"=SUMPRODUCT(1*('Sheet 2'!$A$9:$A$140='Sheet 1'!$C28)*('Sheet 2'!$E$9:$GA$9='Sheet 1'!E$27)*('Sheet 2'!$E$10:$GA$140))",
It gives me a #N/A result.
I have tried to attach a table of what I am trying to do.
Sheet 1
Choose Frequency
1
Annual
[Code]....
I am trying to select a range based on two variables which store the column numbers. what I have is:
View 4 Replies View RelatedI want a formula to do the following. Count all instances when column A contains S or A and column B = 1. The example below would equal 2 (ONES MEETING CRITERIA ARE IN BOLD).
Example
N 2
S 1
A 1
D 3
X 2
how to return a value based on two variables. I was trying with hlookup and offset but excel kept ignoring my thoughts.
Here is a sample of how the data looks like and what I want to do:
excel.jpg
Depending on the ID which is entered in G2, cell H9 has to get the value from D4.
I know that ID is gonna have to be extracted with find function from column A. So every time an ID is entered (in cell G2) it would need to look for it in column A and then return the value VACLEG from column D.
I'm trying to create a simple checkbook application for a friend. I've gotten pretty much everything I want figured out except for two functions, which are basically the same thing. I'm trying to find the sum of all deposits and of all withdrawals based on two entered dates.
Basically, they enter the dates, and the formula finds the dates, then sums all the deposits/withdrawals between them. I attached the workbook with some sample data in case I wasn't really clear.
I am making a weekly plan for machine inspection at the end of every column i need to sum how many machine will be inspected.
L= area
L1=10 ,) L1 has 10 machines ... this number can be change in the future that is why t is variable. for example:
L1=10, L2= 25, L3=20
week1
L1
L3
sum ?
Maximum value based on multiple variables. Basically what I have is something like this:
View 3 Replies View RelatedI have a list of serial numbers. There are many groups of 8 same serial numbers, where group consists of 2 projects with 4 SN per project (because of 4 different events). I want to create a formula that marks one project with 4 out of 8 SN for deletion based on set of 3 dates assigned to them. In short I need to count 4 rows per project as one unit.
Serial
Project
Event description
Date1
Date2
Date3
Here is a list on what information matters when making decision if to mark project for deletion or not.
1. Project1 has no Dates entered compared to Project2. Mark Pr1
2. Project1 and Project2 has no Dates entered. Mark Pr1 (random, does not matter which should be removed)
3. Project1 has 2009 Dates, Project2 has 2011 Dates. Mark Pr1 because dates are older
4. Project1 has less Date entries filled than Project2 (same year). Mark Pr1 because less Date fields entered.
I can somewhat do it for separate rows, how I can make these rules apply for whole project as one unit related only to one SN at a time. Biggest problem is there is no pattern of dates entered. Sometimes one row can be filled another missing out info and etc.
I am trying to write some code that keeps throwing me the error message "Autofill method of range class failed". But I cant understand why, I think it may have something to with my range notation:
Code:
Sub SummariseSheets()
'collates individual client development data from consultant
'worksheets and compiles in one sheet on summary page
[Code]....
The error is in the "Selection.AutoFill Destination:=("C" & LR & ":C" & LR2), Type:=xlFillDefault" line, however I cant figure out why? The code essentially takes a section of data from multiple worksheets, then pastes it onto the summary page. It then adds the name of the sheet the data has come from into column C next to the relevant rows.
I am trying to do a sumif based on two different columns. For example In col A I have part #s, in col B i have quantity, and in col C, I have the different representatives. I want to import the qty sold onto another worksheet. In the cell for the total number I want to know, how many of part# 401 were sold by rep A.
Here is the sheet that the information will be drawn from:
Part#QTYREP
4011A
4023A
4031B
401 1 A
Here is the sheet that the information will be output:
Rep
Part#AB
4012
4023
4031
i have data on number of employees in culomn A range from 1 to 10000. i need to generat four dummy variables in column B,C,D and E as the following
s=1 if number of employees 1-50 and zero otherwise
m=1 if number of employees 51-100 and zero otherwise
l=1 if number of employees 101-1000 and zero otherwise
xl=1 if number of employees 1001-10000 and zero otherwise
I have a datasheet with 6000+ entries. One column has lists of companies, of which there are around 300 and their are entered into this column multiple times. I organized and identified the largest ones but a work file of 6000 is making it a long process to find every single company listed, paste their name onto a separate table to analyses. Is there a formula to take all the different company names and arrange them in a list OR list them just once in a column next to it? I will have to do this again and the analysis of the companies is the most important.but I find I need to spend all evening steadily combing the haystack for needles!
View 4 Replies View RelatedWhat I am trying to do is calculate a particular charge based on two main variables the city and weight chosen (each of which will correspond to a particular value) .I have these cities and weights which correspond to a particular price per 100kg when two are selected
My excel data is set up like this
(A).....................(b)......................(c)..................(d)........................(e).........................(F)
.......................Weight..................0-999Kg.............1000-1999kg..........2000-4999kg..........5000-9999kg
1. City
2. Toronto....................................$5.50..................$4.00..................$3.75..................$3.50
3. Winnipeg....................................$3.20..................$3.75..................$3.6..................$3.21
4. Saskatoon..................................$5.4..................$5.2..................$4.90....................$4.70
(I added a,b,c,d and 1,2,3,4 for reference when explaining the formula)
I have a separate row where I am able to enter a weight in one column (A24) and and choose the city from a list in the second column (B24). I need the next column (C24) to display the dollar charge per 100kg from my data that corresponds to the city I have chosen and the weight entered. I am trying to figure out a formula for C24 that allows for instance $5.50 to be displayed when I type in 800kg for A24 and choose Toronto from a drop down list in B24. This row will look like this for reference:
..................A............................ B..............................C.
..................Weight..................City..................Charge per 100kg
24...............800..................Toronto..................$5.50
I have a list of about 700 guys with their names, ages and a coolness factor between 1 and 10. I have different categories that I want them to fit into based on their age and "coolness". The charts at the top of the sheet I've uploaded. Ages are on the top and their coolness rating is on the side. So for instance if somebody is 27 and has a coolness of 6 they would be lumped in the World category. How do I create an IF command that would automatically list what category they're in based on age and coolness?
View 6 Replies View RelatedI just ordered vba for dummies, in the meantime I will have to get my info from the experts on this board. I want to use a name I have in a cell in a msgbox routine.
Currently I have
if Row i Column j is activated then a corresponding variable Aij, Bij, Cij or Dij would be created (if not already existing), or cumulated by 1. The problem as I see it - completely generalised - is to create a variable where the variable name itself is formulaic, as well as its value. I know SAS would have no problem with this due to the order in which it parses data steps, but I'm pretty amateur with other programming languages so I don't know if this is blindingly obvious or downright impossible!
View 3 Replies View RelatedI have a Macro that already works great but now I need to look for another variable to sort it down even further. I think it can be done by setting up a "Case" but still not sure how to do that. It already looks through the range to find all the rows that return a value of "True". Now I need it sort those results so it only returns what has a specified value in Column "AJ". I am looking to do several choices (Listed Here). So the end result will be all the items that return "TRUE" In Column "AI" and are Items that return one of these values per Case "Starters, Appetizers, Soup, Salad, Entree, Dessert, Special" with then copy over in the exact format that is listed in the code below.
For example All the items that are selected with "TRUE" can be narrowed down to all of those that are "Salad" as well - Then copied over in the exact format that is listed in the code below.
What I am doing is breaking out all the different course of food and putting a Header in-between each (That is already built and working) - So I can select all the food from a master list and then put it in order on the "Catering BEO" Sheet with Headers in between
Code:
Sub BEOA4()
Application.ScreenUpdating = False
Dim wsSource As Worksheet
Dim wsDest As Worksheet
Dim FoundX As Range
Dim FirstFound As String
Dim lastrow As Long
[code]....
I have a worksheet ( adage inventory ) and I am trying to clean up some un-needed rows.
I need this deleting of rows based on a couple of variables.
I need to keep any row that Col F has the text "QCCONTROL"
and
I need to keep any row that COL N has the text "HOLD"
and
I need to keep any row that COL N has the text "REJECTED".
Sub Delete_OK_Lots()
lr = Sheets("adage inventory").Cells(Rows.Count, "A").End(xlUp).Row
For x = lr To 2 Step -1
If Sheets("adage inventory").Cells(x, "N") "HOLD" Or Sheets("adage inventory").Cells(x, "N") "REJECTED" Or Sheets("adage inventory").Cells(x, "F") "QCCONTROL" Then
Sheets("adage inventory").Rows(x).EntireRow.Delete
End If
Next x
End Sub
I have a project that looks at a table of train schedules for a range of dates. The data is formated so that arrivals and departures are on the same row but differnent columns. The dates are to the right and the grid is completed with the number of arrivals for a paticular day. I have created a formula that looks at the data and returns the arrival time for that date. It worked fine until the schedule was altered so that the same train could arrive at 2 different times on 2 different dates. So train 976 arrives at 4a on some days and 5a on others. In the example Ihave posted train 976 has 2 passengers arriving at 5a but the formula shows 4a. Is there a way I can have the formula return the correct time based on the day of the month? I should point out that the same problem could exist for departures.
View 8 Replies View RelatedI have three variables; Independent variables x & y, and dependent variable z. x & y can take the values 1,2,3 and can equal each other z can also take on values 1,2,3 but can never equal x or y. I tried using if/and statements to solve this (see below), but I would have to do it for each case and it exceeds Excels embedded function limit.
=If(and(B7=1,B10=1),riskduniform(E4:E5),if(and(B7=1,B10=2),3,if(and(B7=1,B10=3),2,if(and(B7=2,B10=2),riskduniform(1,3),if(and(B7=2,B10=1),3,if(and(B7=2,B10=1),3,if(and(B7=3,B10=3),riskduniform(1,2),if(and(B7=3,B10=1),2,if(and(B7=3,B10=2),1)))))))))
Split From Copy Non Null Cells In Column To Another. I'm trying to copy some of the results. How do you combine two variables with text into one cell. For example i want range("a1") = var1"."var2 so it would display var1.var2 if var1 = var1 and var2=var2
View 5 Replies View RelatedI recorded a macro to perform a graphing operation which will be used in a For-Next Loop which will run eight times. I need to modify the “ range” portion of one of the recorded lines to allow the graphing data to be dependent on variables I have set up as integers. See the applicable part of the recorded code below.
Dim firstrowno As Integer
Dim ltcol As Integer
Dim lastrowno As Integer
Dim rtcol As Integer
‘code which sets the values of the above variables Is Not shown
ActiveChart.SetSourceData Source:= Sheets("PLOTS").Range("AN27:AQ33"), PlotBy _
:=xlColumns
How do I replace/modify the ("AN27:AQ33") in the code above using the variable integers; firstrowno, ltcol, lastrowno and rtcol ?
I have a sheet where I have a "Status" column in each row which could be "Waiting for Band", "Waiting for Organizer", or "Ready to Go!". These options are set as a data validation list. I have 2 cells later in each row where the response can only be "Yes" or "No". I want for the Status column to be set automatically based on the responses in the later two cells. So for example
where C3 and M3 are Yes, the status column in B3 should read "Ready to Go!"
where C3 is no but M3 is yes, the status column should read "Waiting for Organizer"
where C3 is no and M3 is no, the status column should read "Waiting for Band"
The above is actually an exhaustive list of all of the potential outcomes(!)
Another VBA problem I am having to need help with...
Andy Pope and DonkeyOte graciously helped me with the first stage here:
http://www.excelforum.com/excel-prog...-a-time-2.html
where I was trying to loop through 1000 records at a time and retrieve data from my Oracle database based on a single Field ID.
This is the code that ended up working...
I was trying to use the VBA code to generate a data table:
Private Sub createDataTable(WS As Worksheet, initialRow As Integer, numCol As Integer, numRows As Integer)
Dim initialCell As Range 'specify the upper left cell
Dim RefCell As Range 'the reference cell on the caculator sheet
'activate source sheet
ActiveWorkbook.Sheets("Calculator").Activate
Set RefCell = ActiveSheet.Cells(2, 3)
WS.Activate
Set initialCell = ActiveSheet.Cells(initialRow, 1)
initialCell.offset(numRows, numCol).Select
Selection.Table ColumnInput:=RefCell
'the calculation should be automatic, if not, then calculate
WS.Calculate
End Sub
and it kept giving me an input celll not valid error on this line: Selection.Table ColumnInput:=RefCell the thing is, I have defined RefCell as a range object, so it should be OK rite?
I have a table which looks like this:
Name 1 IDNumber Name 2 Name 3 Column 5
Tom20148 John Malmo
Tom20148 Will Malmo
Bob20206 Will Malmo
Tom20206 Will Paris
Bob20206 Rob Rotterdam
Bob20207 John Rotterdam
Ray20207 John Paris
Tom20208 John Malmo
Ray20208 Rob London
Ray20209 Rob Paris
Bob20209 Will Malmo
Is it possible to have excel go through this list and assign each row a number in column 5 based on the names and the IDNumber? Basically, I would want each entry that is identical in name 1, 2 and 3 to be assigned numbers 1, 2, 3, 4, 5 etc based on their IDNumber. So Tom/John/Malmo with the IDNumber 20148 would get the number 1 in column 5, while the next match (Tom/John/Malmo/20208) would get the number 2 in column 5. For each different match of Name 1,2 and 3, I would want the count in column 5 to start at 1. So Bob/Will/Malmo/ 20206 would get number 1, Bob/Will/Malmo/20209 number 2 etc.
Please see attached example book.
In it I want to change name "G" in column A to "X" in column C.
And I want to do it for month of May in column B.
So a match won't do the trick because Name G is in 3 times so it should match on G and May from columns A and B.
How to catch this in a simple VBA line?
I need to create a macro, where the contents of a particular cell are dependent upon summing values based on a word desription from another column. For instance, I would like cell E10 to include a numerical value from cell D5, but ONLY if cell C5 is populated with the word "trust" as opposed to "equity". Conversely, I would also have a macro in, say, cell E11 that would do the same for the word "equity" in column C. I would like the macro to hold for the entirety of different words in column C.
View 6 Replies View Related