Finding Values Based On 2 Criteria
Mar 7, 2012
I need to be able to find a value in another sheet based on 2 criteria
The criteria is a number from a column on my first sheet where the results will reside, the second criteria is based on a fiscal year (calculated based on the year of the current year).
The formula I use is as follow:
=LOOKUP(BA2,Budgets!B:B,Budgets!I:I)
and this is what I use to get the column:
=+INDEX(Budgets!B11:B200,MATCH(Budgets!B11:B200,Budgets!B11:AB200,0),3) getting #N/A here
and I use this too:
=MATCH(IF(MONTH(TODAY()) < 7,YEAR(TODAY()),YEAR(TODAY())+1),Budgets!E:Q) getting #N/A here too
If i keep it like that no problems but I will need to change the criteria 2 every new fiscal year.....
What I need is to replace the criteria 2 with something that will find the proper column wher the data is the fiscal year is listed on E11:Q11..
I have tried index and match and quite frankly, I never made it work, always getting an error.
View 9 Replies
ADVERTISEMENT
May 21, 2007
I am trying to find the top two values per group based on multiple criteria. The list I'm working with is not sorted and would be better for it to not have to be sorted as on-the-fly sorts will likely often occur from the raw data and I wouldn't want that to mess up the results I'm looking for here.
As an Example, here's what I'm trying to do:
Make Model Rating
Ford Bronco 64
Chevy Corvette 94
Dodge Intrepid 83
Chevy Chevette 34
Dodge Viper 72
Ford Escape 21
Ford Expidition 53
Chevy Impala 67
Ford Fairmont 11
Dodge Dart 33
View 9 Replies
View Related
Sep 2, 2009
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)
View 5 Replies
View Related
Apr 24, 2014
I have 2 columns of data in Excel which I have brought a small section of it below. As can be seen the values in the left column have a large spike (difference of more than 10) at certain points (in this case at 34). I wan to find the average of the numbers in the right column but only till the point where the large spike happens (in this case the average of the first 5 numbers). I've tried AVERAGEIF but it's not what I want. I want this to repeat for the entire column and give me the averages of each of these groups.
10
32.4
11
38.6
12.5
23
[Code] .........
View 2 Replies
View Related
Jun 15, 2006
I have a main soure data which consist of row & column information. What i want to do is search the data from the source data into my result data as per the attachment file. Example: I want to information of Jan & banana from the main source file to appear in the XXXX
Result data(criteria base on Month & type)
JanApril
BananaXXXX
Apple
Orange
View 4 Replies
View Related
Apr 29, 2014
i have with noting the most recent date of a test and also the one prior to the most recent dependant on location and test type. I require most recent and previous test info to enable a comparrison to be made. All other dates are not required but need to be kept as historical data and can't be deleted.
Is there anyway i could get column E to auto populate and amend itself as more test dates and locations are added to the list?
View 1 Replies
View Related
Jan 2, 2013
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".
View 11 Replies
View Related
Mar 19, 2014
A
B
C
D
E
[Code]...
In the example above I am trying to look up a value from columns C-E. I need to be able to search/index using 2 criteria to figure out which row to match with the given column. for example: If I want to know the invoice qty. for R&D for Jan-2012, so the returned value would be 13. I have tried several different combinations of match and index to get this to work but have had no success. Ultimately what I want to do is have a drop down for the month and year that our VP can select and it will give him the given values for that month.
View 1 Replies
View Related
Mar 28, 2014
I have a data set which has 6 columns (and lots of rows). Every row is different but I want to aggregate them based on 4 fields and then find the average of the numerical column for the results. I basically want to Group based on 4 fields and find the average of the 5th field.
My initial approach was to introduce a column which is a combination of the 4 fields I want to group by, simply in the Excel file (=A2&B2&C2&D2) and then find duplicates of that. I have a solution for this in VBA but when importing new data sets in this method is very slow, so I want to be able to do the whole thing in VBA.
View 9 Replies
View Related
Aug 14, 2008
is it possible to write a macro that will convert absolute values into negative based on criteria? Data gets downloaded from in house system which comes out as all absolute values, now i need the macro to covert absolute values to negative if the amount is either "Our Reciepts" or "Our Delivery"
Below is a small sample of data, real data is around 100 rows
Col DCol EOur Receipts1231Our Delivery1231Delivery Settlement1323
View 9 Replies
View Related
Aug 7, 2013
I'm starting a dashboard, where on the front page I have two combo boxes on the left, and three empty fields to the right. I'd like the three fields to the right to auto-populate table-based values depending on the chosen criteria from BOTH fields (by store and month/date). I've attached a sample of what I've got so far. I've only provided three tables for this example, and I have a table with the same column/row titles for each metric and I have three different metrics I'd like to auto populate: COGs, Sales, and GM% or in the example, metric 1, metric 2, metric 3. No pattern in the table values, just wanted to populate the fields quickly. All fields are organized by store/month-date and I've set up a link to my combo boxes on a calculations tab.
View 2 Replies
View Related
Feb 17, 2012
I am trying to sum values based on 2 criteria -- the date (using a fiscal month) and a Category (A,B,C,D). Here's a small portion of the source data:
Sheet3
JKL1DateValue Plant21/28/2012 50.00 A31/31/2012 10.00 B42/6/2012 40.00 C52/10/2012 35.00 D62/15/2012 8.00 A72/17/2012 100.00 B82/21/2012 50.00 C92/23/2012 10.00 D102/27/2012 40.00 A113/2/2012 35.00 B123/6/2012 8.00 C133/8/2012 100.00 D143/12/2012 50.00 A153/16/2012 10.00 B163/20/2012 40.00 C173/23/2012 35.00 D183/26/2012 8.00 A193/28/2012 100.00 B
And here's the result I want:
Sheet3
NOPQRS1StartEndABCD21/28/20122/3/20125010 32/4/20122/10/2012
403542/11/20122/17/20128100 52/18/20122/24/2012 50106
Feb Total58110904572/25/20123/2/20124035 83/3/20123/9/2012
810093/10/20123/16/20125010 103/17/20123/23/2012
[Code] ...........
View 3 Replies
View Related
May 23, 2014
I have 2 worksheets with the below data.
Sheet 1
Part Number Client X Client Y Client Z
127-003104-03 $38,835.00 $27,756.75 $38,835.00
127-002114-03 $31,600.00 $27,037.50 $31,600.00
127-010113-01 $25,000.00 $24,270.00 $25,000.00
127-001113-03 $25,000.00 $24,270.00 $25,000.00
138-001113-01 $27,900.00 $26,790.00 $27,900.00
Sheet 2
PurchaserPart # Outward Rate
Client X 127-002114-03
I want a formula for "the Outward rate" which will check the 2 criteria of the client name and product code and return the unit price for the product.
View 1 Replies
View Related
Jul 16, 2014
Add Multiple Values Based on Criteria
Search for the word "Clear" in the Task column. If the word "Clear" is found, go to Value column. Add all values starting from "Clear" down to the end of the list. If it is larger than 10, return the word "OK". Otherwise "Poor". If larger than 20, return the word "Good". The length of the list is variable.
Here is the sample worksheet (Please click).
View 7 Replies
View Related
Jun 20, 2013
I've got an interesting problem which I haven't been able to find an answer for.
A B
PR Yes
PR Yes
PR Yes
DN Yes
DN No
DN No
I need to count how many unique values in column a have every row in column b the same value. So counting for yes values, PR would equal one but DN would equal zero.
I used this formula to find out how many unique files had yes values, now I need to find out how many files only have yes next to them.
=SUMPRODUCT(($B$2:$B$14="Yes")/COUNTIFS($B$2:$B$14,$B$2:$B$14&"",$A$2:$A$14,$A$2:$A$14&""))
View 8 Replies
View Related
Oct 14, 2009
I have 3 columns. In 1st there is a name of the product.In 2nd there are different types of product (6 in total) and in the 3rd column a figure attributed to the correspondend type of product. I need to create a summary for every type. For this I want to show the 5 largest figures(based on the name of product) for every type.
Like this:
AAA GK 40
BBB CD 50
CCC FD 55
DDD GK 60
EEE FD 70
.....
View 9 Replies
View Related
Jan 31, 2014
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.
View 2 Replies
View Related
Apr 14, 2014
I need a macro to turn to values all formulas in specific columns only. For example column C, F, I, L,O,...until column UYG, always with the interval of 2 columns.
View 1 Replies
View Related
Nov 12, 2008
a VBA code to do the following:
I have a worksheet consist of some 17,000 records - from ColA:ColDZ. In Datasheet, I have all values. From this, based on some values in ColA all records should be copied into another sheet(s).
I have attached a sample workbook in which I have explained the requirement.
View 14 Replies
View Related
Aug 11, 2007
Is it possible to count unique values in one column based on that column and another column? I have a report that has a column of dates (B:B), column of month and year (C:C), and a column of RMA numbers(A:A). The RMA numbers repeat if there is more than one part on the RMA. I would like to count the number of RMA created each month. Below should be Jan = 2; Feb = 2; Mar = 1............
I have a CSE formula that allows me to count based on two criteria but it does not count unique values and you have to specify the two criteria.
View 4 Replies
View Related
Feb 19, 2009
I'm facing a big problem and I can't found a solution for days.
I'm trying to count/sum unique values in a Range, base on a criteria excluding Blank cells. Basicly I'm using the Formula Below:
=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&""))
Let me Put to you an example for you been able to understand my problem.
Example considering Range "C2:C20" exluding blank cells
(1) - To count unique values
=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&""))
(2) - To count unique values based on criteria in range "B2:B20"
=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")*(B2:B20="E"))
(3) - To sum unique values on "E2:E20" based on same criteria
=SUMPRODUCT((C2:C20"")/COUNTIF(C2:C20,C2:C20&"")*(B2:B20="E")*(E2:E20))
They all work fine. But I need to insert a merge at Row 10 for
better reading purposes and the (2) and (3) are returning (#DIV/0). The (1) formula still work's.
I just don't know what to do guys ....
View 9 Replies
View Related
Jul 20, 2009
I have a problem with excel that I have been trying to solve for the past 5 days! I have 2 tables (one with 500 rows, the other with 15000) that contain information about pipes.
The smaller table, Drift List, is to be used as a reference to fill up the bigger table.
Here is how Drift List looks like:
Size NominalWeight WallThickness APIDriftDiameter AlternateDriftDiam.
5.5 14.00 0.244 4.877 7.625
5.8 14.88 0.253 4.915 ----
...
The other table, Pipe Database, Looks like:
Size NominalWeight WallThickness DriftSize DriftType
5.5 14.00 0.244
...
I have to fill up Drift Size using APIDriftDiameter from the first table, BUT if AlternateDriftDiameter is available, I have to use that instead.
DRIFT TYPE column has to indicate either API or Alternate (Depending on which one I have used).
Here are the two files:
[url]
[url]
View 9 Replies
View Related
Oct 3, 2009
i need a formula that will sum values in Col H based on the criteria set in CELL B1. The tricky part is that i need the summed values to be converted using the FX rates in Cell M:N, i have provided an answer on what the formula should return in CELL C2....
View 9 Replies
View Related
Dec 2, 2006
Option Explicit
Dim lastrow As Long, t As Long
Sub Method()
lastrow = ActiveSheet.UsedRange.Rows.Count
For t = lastrow To 1 Step -1
If Cells(t, 8).Value <> "" Then
If Cells(t, 9).Value = "Y" And Cells(t, 10).Value = "" And Cells(t, 12).Value > _
6 And Cells(t, 12).Value < 60 Then Range(t, 25).Value = 20
End If
Next t
End Sub
Alright, the above code is not working. I am not sure if it is the write part (t,25 value) that is wrong. I want the Y column to be written with a method numbered "20" if the conditions (H is not null, J="Y", K="", and 6<M<60). I have numerous other methods to put in. The reason I'm not doing Case Statements is this is jsut to write the basic code, and then I will have to move it over to ReportSmith using ReportBasic.
View 5 Replies
View Related
May 14, 2007
I need to display values from cells of sheet1 and sheet2 as comment in Corresponding cells of sheet3 whose value in sheet3 is FALSE
Sub try()
Dim Sheet1cellval As String, Sheet2cellval As String
Worksheets("Sheet3").Range("A1:D4").Select
Dim cell As Range
For Each cell In Selection
If Worksheets("Sheet3").cell.Value = False Then
cell.AddComment
cell.Comment.Visible = False
'Getting the value of Cell in Sheet1 for the corresponding cell in Sheet3 whose value is FALSE
Sheet1cellval= Worksheets("Sheet1").cell.value
'Getting the value of Cell in Sheet2 for the corresponding cell in Sheet3 whose value is FALSE
Sheet2cellval = Worksheets("Sheet2").cell.value
'Displaying the values of cells in Sheet1 and 2 as comment for the corresponding cells in Sheet3
Worksheets("Sheet3").cell.Comment.Text Text:="Difference" & Chr(10) & Chr(10) & "Sheet1: " & Reportcellval & Chr(10) & Sheet2cellval & Chr(10) & ""
View 2 Replies
View Related
Jan 20, 2014
I need a way to find a value in a column which has one or more corresponding values in an adjacent column. Then take all of the corresponding values found and count all occurrences of the found values in another column. But I only want to count the entries if an adjacent column is not blank.
Not the easiest thing to describe. Starting to wonder if I need to think in reverse. I hope the attached example makes more sense.
Book1.xlsx
View 2 Replies
View Related
Apr 18, 2014
I have a table based on transport numbers who sometimes have doubles. Example:
Number Postcode Value1 Value 2...
106200 8500 10
106200 8500 5
106200 8600 6
106201 5500 4
106202 4000 1
106202 4000 1
So it works as following:
A transportnumber can have multiple instances of itself, such as 106200. This transport number can also have multiple instances where the postcode is the same. These are the rows i want to combine
So shortly:
Transportnumber double/triple...+ Postcode double/triple...= combine these rows.
if only transport number is double, or only postcode, then dont combine. I want it to combine 2 columns of values, one will b the kgs, other one a price.
So example of end result for 106200 would be
106200 8500 value+value (these 2 rows matched so it combined the 2 values i want it to)
106200 8600 value ( this was unique so it stays unique)
View 4 Replies
View Related
Jan 21, 2014
I have a large sheet (several thousand rows and growing) - see a small cut of the data. The columns following on from this have a list of people's names, hence why a lot of the data repeats (as a number of people attended each program).
Program
Clinic Title
Start Date
[Code].....
formula that will automatically give me that answer?
View 2 Replies
View Related
Jan 9, 2007
I have a worksheet with two tabs.
First Tab
Account Name
Account Number
Second Tab
Account Name
Account Number
Account Ship Location Number
On the first sheet each account name and number only appears once.
On the second sheet there may be multiple entries for each Account Name and Number.
On the first tab for each row I want to compare the Account Number column to the Account Number column on the second tab. Where I have a match I need to count how many unique Account Ship Location Numbers correspond.
View 9 Replies
View Related
Feb 14, 2013
In the attached, sheet 2 has a formula that pulls the Item (column B) from Sheet 1 based on Instocks (column F) being less than the value in N2. Would it be possible to add a second criteria to the formula in sheet 2? In short, can I pull the item from column B of sheet 1 into sheet 2, where in sheet 1, column F values are less than N2 AND where column G values are greater than O2?
I'd love to just vlookup the fill rate value and then filter it down, I know at some point I will be asked to weed it down a bit via a formula instead.
View 1 Replies
View Related
