I have managed to make a work queue and lots of other stuff for the model, but I can't get it to take orders in the way I want it. Each order has a order number (from 1 to 100) and the orders come in almost randomly e.g. 3, 5, 11, 2, 7, etc. What I want to do is to take the smallest available order that has not been processed in.
The available orders column and processed orders look something like this:
Zero means no new orders or no processed orders. Now the Start processing column should select the smallest not processed order if previous order has been processed.
A have, for now at least, all other problems solved, but can't figure out how to get start processing column check for the smallest not processed order line. I have tried combination of Min and Max functions with If, but it soon requires too many Ifs to make any sense out of it. I also tried the Dmin function, but it wasn't up to the task becouse the model requires ~1000 lines and as Dmin only takes criterias vertically I ran out of columns . So how could find minimum from row one until current row excluding values processed so far and only checking orders available so far?
This formula allows me to find the lowest value in column U where column N contains the text "NO".
{=MIN(IF($N$2:$N$10000="NO",$U$2:$U$10000))}
I want to add another condition so that the formula only returns the lowest value in column U where (i) column N contains the text "NO" and also (ii) column F contains the text "YES".
Say I have 2 columns that in basic form look like this:
Column A Column B Jan 1 Feb 0 Mar 7 Apr 4 May 15 Jun 2 Jul 5 Aug 4
First I want to look up the max value in this column. This is easy =max(b1:b8) Then I want to know the minimum value that occurs after the maximum value. Thus the answer would be 2.
I tried several iterations of sumproduct, small, and few index matches. no luck.
Several thousand rows, column A is name, column B is city, column C is $.
I want to see the max, min, median for all first names.
=SUMPRODUCT(MAX((Name=Joe)*($))) - seems to work fine =SUMPRODUCT(min((Name=Joe)*($))) - will not work, zeros come through, even if there are no 0's for Joe =SUMPRODUCT((Name=Joe)*(SMALL($,1))) - wrong answer
I need to find the minimum difference between any two elements in a row or a column. While it's easy to do for a 3-4 elements by doing subtractions for all elements in the array, doing it for more elements leads to a very long formula.
For example, I need to find the difference between any two elements between C5 and C9: ....
What I am attempting to do is find the MIN value in Column C where values in Column A are equal.
The data would look like this
A B C D (D:D is where the "MIN" Formula will be) Scope1 NameA $100 Scope1 NameB $145 Scope1 NameC $115 $100 (I want the min value to show up here) - (this would trigger a break between scopes, and provide a conditional format separator) Scope2 NameE $450 Scope2 NameG $345 Scope2 NameX $415 $345 -
So every time I put a "-" I would like the MIN formula to trigger in (Row#-1,D)
Sheet 1, column B range is B4:B39 & B54:B85 Data will look like this 20-77 20-77 20-79 20-55 31-251 4-333
Or very similar to it, I want to be able to find in order smallest to largest across 6 cells, meaning in cell c4 I want 4-333, d4 20-55, e4 20-77 and so on for only 6 cells as that is all I have room for on the sheet. of course if there are only 2 sets of data then 3-6 will be blank. but I think if I can get something working I can add that part.
is there any way for a sumif formula to have multiple criterias? for my case, after the formula checks for a condition, it has to check for another condition before summing up the figures.
I've forgotten all the formulas that I learned during college. And that was for Excel 2003; now I'm using Excel 2007. So, hopefully you experts can help me out.
So here's the problem. I need to set up an invoice in Excel but I don't know what formula to use. I'll first describe my invoice and then I'll tell you the problem.
It's a basic invoice where you have customer's info and product's details and prices. So, when I choose a customer's name from a list in a cell, the address and phone of that customer will automatically appear. I don't have a problem at all with this part. And then there's the product details section where you enter a product type, size, color, price per unit, and total price. The price per unit also depends on the customer entered. Each customer has different pricing and I used a discount system for each customer. We basically have a universal price list for our products, but we have different discount percentage for each customer.
I have a list of brands in one worksheet that are abbreviated (Brand 1 = "AB-"). In another Worksheet I have a list of products that start with various brand abbreviations (ex: AB-12345, BP-12345), and in another column on the worksheet I have codes that represent certain characteristics of that style ("1"=flat shot), "2"=shot on model, etc). Now what I want to do is count how many instances I have of products that begin with "AB-" and have a "1" in the other column so I can get a count of how many flat shots i have to do for that brand.
So far I am using the below code to get a TOTAL count of products that start with "AB-", but i cant figure out how to write it so that it checks additional criteria in another column.
A3 contains the text "AB-" and 'photo list'!G2:G5001 contains the list of products that may or may not contain the text string "AB-". Column S (not shown in this code) contains the codes for how to shoot. Hope someone can make sense of this and give me a hand.
Attached is a print screen. I'm struggling with using the min function in vba. I want it to find the minimum cumulative cost in week 0 out of the first three, and the copy the permutations of it (1,0 or 1, 1 , e.t.c.) to Week one column C & D of the model.
I am trying to find the minimum date in column B based on the value in column A and the Maximum date in Column C. I already have a calculation for column C that ignores certain values, i am fine using that date....
On the first sheet, I have....
the "summary of data"
The date in column C is the Maximum value ignoring 3 certain dates (10/10/2030, 11/11/2011, 12/12/2012) I used this formula to find that value.
Now, based on that I need to find in column B the Minimum value based on both the value in Column A (item) and the maximum date already found in colum C (Max)
Look in Column A and find the cell that contains "Std A 1" and the cell that contains "Std A 2".
Compare the corresponding values in Column B, and multiply the lowest value by .2 I've tried using various methods (find, if cell.value.instr) but I keep getting errors. Can anyone point me in the right direction?
i have the sheet which have the table which look like
Person task done
A 5
B 4
C 1
D 1
now i want is there is any way to find minum value in task done colium and return the person name and like in this exapmle, 2 person have done have done minium work.. it tell me both those name
Referring to the post Find Min Value In Multiple Columns And Match The Header. I would like to go little bit further and see is there a way to find and match not just in multiple columns but in multiple tables (see the example)
I am working with a nonprofit to set up their financial ledgers. There is one workbook with 12 sheets, one for each month. The goal is to be able to set up a formula that searches through all 12 sheets for every donation that a specific individual has made. For instance, let's say that John Smith gave x amount of money on 1/1/2000, y amount of money on 1/10/2000, and z amount of money on 2/2/2000. This data will appear on 2 different worksheets. I have the following formula, which allows me to look through one sheet at a time:
Where Column A in each sheet is individual name, and column B is amount donated. Sheet "Jan" is the data for the month of January, and sheet "Smith" is the culmination of John Smith's donations for the full year. Using this array function, I am able to retrieve all data for John Smith in the month of January, but I can't find a way to make one function that searches for all of John Smith's donations in each month. Is there a way to build an array function in VBA that would accomplish this?
i am trying to filter data based on more than one criteria (8 to be precise). I have some data in one worksheet and i need to transfer it to other worksheets depending on certain criteria. for example if cell A1 has A or B then it should go to "temp1" spreadhseet, if A1 has C,D, E, F, G or H then it should go to "temp2" worksheet etc.
Is there a smart way of doing this rather than writing a number of with statements using 2 criterias each and hence copying data in more than one attempt (and thus slowing down the macro)?
I did think of using creating a dummy column, then using If statements to write True or false in that column, using true & false to filter and copy the data and then finally deleting the column. but as i understand i can not have more than 7 nested if statements but i have 8 criterias.
I am having trouble creating a formula that will do what I need it to do. In C4 the I already have a formula that states if B4 is greater than 94.99% then it will equal 5. What I need it to do is if it is equal to or greater than 95% then =5. Also if it is equal to or less than 94.99% - 92% =3 and if less than 91.9% =1. Here is the worksheet I am using....
I'm trying to modify this code in order to do the following.
I want to choose first column and then the criteria for filter, then i want to choose a second column and criteria to filter the remain values from the first filter.
Here is my Sub Filter() Dim Myrange As Range Dim CriteriaVal As Variant Dim CriteriaVal2 As Variant
Dim KillColumn As Integer Dim KillColumn2 As Integer Dim ActiveColumn As String Dim AC Dim LastRow As Long Dim rng As Range
If I have to match MAX from Row 1 and MIN from Row 2 and get the EXACT MATCH as well as more than 1 Match?
I have 3 Rows and 4 Column Matrix
from A1 till D1 contains values as 10,20,50,40 from A2 till D2 contains values as 30,20,10,40 from A3 till D3 contains values as TOM,****,HARRY,JONES
THen my answer would be HARRY, however if there are more such combinations present then I would require a list of all of them.
Ex2 For Multiple Lookup from A1 till D1 contains values as 50,20,50,10 from A2 till D2 contains values as 10,20,10,40 from A3 till D3 contains values as TOM,****,HARRY,JONES
I have for example database in three columns - A, B, C: __A_______B_______C Order1___Item___99-99-99 Order1__________10-15-78 Order1___Item___88-88-88 Order2___Item___10-18-25 Order2___Item___10-15-25 Order3___Item___10-15-25
I have two criterias: Order1 and Item How to find in DB sheet these data and copy in to other sheet?
