Returning Values With Multiple Criteria?
Apr 11, 2012
I'm trying to get a value to return based upon three criteria, sales rep, compensation plan and calculation method.
For each sales rep (there are 20 reps), there is an option for one of five comp plans and for every comp plan, there are six calculation methods (these six methods are the same for every one of the five comp plans).
I know I need to use the Indes(Match()) set-up, but I'm having the darndest time figuring out how to set up the source matrix to do this right.
View 1 Replies
ADVERTISEMENT
Jun 10, 2013
I have a spreadsheet of prices (raw data), and I need to pull out both the most recent price for each item for a specific customer code for each item, as well as the second most recent price, (essentially the old price), for that customer code for each item.
I have columns for ITEM (code), PTC (Customer Code), PRICE, and EFF (Effective Date). For each item code, there are multiple prices for multiple customers, such as in the linked workbook.
I am currently using the formula, =INDEX(PRICE,MATCH(1,(F2=ITEM)*(G2=PTC),0)) to pull out the last price. This works because the last price is on top based on sorting. What I need is a formula that pulls out the next oldest price for that item/ptc combo. I tried using the SMALL function to no success.
[URL]
View 2 Replies
View Related
May 17, 2014
I'm using Excel 2007
I have the below data in sheet 1
In a separate column on sheet 2 i want to return the value of the data in ACC1 if the data in DEB/CRED is LC or SC and the value of the data in ACC2 if the data in DEB/CRED is SD
DEB/CRED
ACC1
ACC2
[code]....
View 7 Replies
View Related
May 14, 2014
Attached worksheet which is an sales incentive plan. I need cell B69 to represent the amount in b68 unless neither b30 or b99 are > 0. In other words if even 1 of the cells b30 or b99 come back positive, you get the amount in b68. If it comes back 0 in both cells b30 and b99, then they get 0.
View 4 Replies
View Related
Feb 5, 2013
I have two workbooks at the moment, one which holds a whole host of customer and accounts details and another which holds only the customer details. What I'm trying to do is cross reference the two to determine if a customers details appear on both and if they do highlight this, ultimately I'd like it to pull the customers account number back but even if it highlights a match that would be sufficient.
The only unique criteria I have to do this are customer surname and customer postcode.
In the Customer details spreadsheet I'm attempting to enter a formula which will look at the specific surname & postcode held on the row in which it is entered. It will then look at all of the rows on the other spreadsheet and determine whether any of them meet both criteria.
If they do I'd like it to display "match" if they don't I'd like it to display "no match"
View 2 Replies
View Related
Mar 21, 2014
Index across multiple sheets returning multiple values
I am building a payroll workbook and I need to build a sheet that will allow me to choose an employee name and the formula will return all the data related to this employee. I found this forumla to do this: =IF(ROWS(...)<=...;INDEX(INDIRECT(...);SMALL(IF(...=...;ROW(...)-ROW(...)+1);ROWS(...)));"")
But in my workbook, the data is on multiple sheets . How do I adapt this formula to look into 12 sheets?
that data would be returned in order of the date of the pay.
file: Test formula on payroll_v3.xlsx
View 4 Replies
View Related
Jun 8, 2014
I am attempting to place a formula in cell b3 in the attached spreadsheet on the "sheet to pull data into" tab 1 (cell is highlighted yellow) to retrieve value in the yellow highlighted cell in the "Sheet that contains data" which is on a seperate worksheet within the same workbook.
The cells highlighted red in the "sheet to pull into" tab (cells: a1, a3, b1, and b3) are the criteria that needs to be matched to those cells on "sheet that contains data" tab in order to ensure the value returned is correct.Sample of problem.xlsx <----see the attached sample spreadsheet.
View 2 Replies
View Related
Aug 16, 2014
How the following problem gets sorted out
ColumnAColumnB ColumnEColumnF
33rohan55
44tata55
55dev44
55rony33
As you can see from above i want to lookup all the values in column E in column A and return the corresponding value in column F.
View 4 Replies
View Related
Dec 15, 2013
Trying to pull data from one workbook to another using Vlookup.
The worksheet has 7 columns (date, day of week, time or arrival, package, departure time, patron count and group name) I would like to pull all the data for a specific date in a seperate workbook.
I know I require a nested formula but cannot seem to get the variables correct:
View 2 Replies
View Related
Apr 18, 2014
I have attached a file with an example of what i am looking for. The data in columns A and B are my original Data. There are multiple instances of e-mail address on the left but I need to bring in each data horizontally. I would like a formula to enter into E2:J6 to pull the information in.
Book2.xlsx
View 9 Replies
View Related
Apr 24, 2013
Actually I plan to create an eRequisition Form for our branch sites. My focus would be the consumable items of Lexmark printers. I want to create cascading drop-down lists based on data table (worksheet : All) as per attached. From lots of examples I've seen in this or other similar forums, I notice that in order to allow this cascading to work is by creating column with header name identical with the input in the 1st List. But I'm thinking, if I need to do that, then there gonna be hundreds or maybe thousands columns to create as the number of branches are expanding, so I think tht should not be practical.
I found examples that shows how lookup 1 value can return multiple values but I totally cannot think of a way to allow those multiple values to be returned as dropdown list rather than displaying all in rows of the worksheet.
What I need is that, in 1st list, upon selecting the branch, it'll populate all items related to the branch and when selecting the item, it'll populate brand/model list for the particular item only. The input in the dropdown list should not have duplicates. Been trying using pivot to do this with macro that enables auto refresh once any changes made in the table.
create this cascading drop-down lists just based on the table as per attached? I really want to avoid having to create extra tables to allow this cascading to work.
[URL]
View 13 Replies
View Related
May 27, 2014
I have been trying to find a formula for this but I have yet to find one that works for me.
Data;
English - United Kingdom, Czech, Spanish
UK - United Kingdom
Swedish
English - US
French
US
France
German
Portuguese
+8000 more various values
I am looking for a formula that can search for multiple values e.g. english, us, uk and united and then return the value "Yes".
I was looking for something that includes wildcard searches or contains so that it is not affected by upper or lower case but combining this with multiple searches was proving difficult.
View 7 Replies
View Related
Jul 11, 2014
From the following example:
A1 TAG B1 mileage
G10-1622 15246
G10-1622 15246
G10-1583 15246
G10-1622 28659
G10-1195 28659
G10-1622 28659
desired results
Enter tag in cell
G10-1662
Mileage
15246
15246
28659
28659
I would like to enter a particular TAG # in I5 and return all the mutiple associated data
See attached : tire2.0.xls
View 9 Replies
View Related
Apr 20, 2013
I am using the following formula:
=IFERROR(INDEX(drange,SMALL(IF(AND(qrange="SH",trange>30),ROW()-6),ROW(A1))),"")
to return the name (drange) of a person who was visited by "SH" (qrange), more than 30 days ago (trange).
There are faults in my formula, and if I'm honest I don't entirely understand it !
I need to return all of the names of people who were visited by "SH" more than 30 days ago. So I need the next value, and the next which is also over 30, by copying the formula down to the next cell and the next. My problem is that I get the first value (which is correct) and then the next ones are blank.
To make matters worse, the first value I get is only correct if i DON'T enter it as an array. If I do enter it as an array, I get the first row of the spreadsheet.
Using Excel 2003
View 9 Replies
View Related
May 23, 2007
I have attached a sample spreadsheet which describes my problem.
In C2 and C3 under the first business coalitions column, I have written in text what I would like to be displayed automatically with a formula. The trouble is that, as you can see, Abbott Laboratories is a member of 3 business coalitions. Therefore, a simple vlookup doesn't work because that would only return 1 result.
View 5 Replies
View Related
Aug 4, 2012
how I can keep a running total of information added to a different worksheetS. They would not always be the same peson ID so I would like to return a match on any unique ID number found on multiple worksheets and add their values on a master front sheet. i.e this becomes a running total of items purchased per ID number.
View 4 Replies
View Related
Jul 31, 2014
I'm basically working from 3 sheets for this so I'll start with an example of the data I'm using:
1st sheet:
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
[Code]....
I have 180 rows of data like this one in the first sheet
Second sheet(named sheet 1) is not used for this
Third sheet(named sheet 2):
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
[Code]....
Basically I'm trying to find column #3 value in my third sheet and return the second column value. Problem is that the data is located more than once in the third sheet so I need the value of each one of them. So, with this example, Q5942X is located twice in the third sheet and each time, it has a quantity of 2. I would need either to return the value 2 twice horizontally or even better, add the two together. The first sheet, the example is row #45.
This formula should be in the column following "majoration".
I am using excel 2007 and windows 7.
View 3 Replies
View Related
Nov 6, 2009
This is a floowup to the issue that was originally posted as "Returning MAX/MIN values from multiple rows in a named range ". I marked that post as solved since I have worked through part of the issue and since have a different one.
View 2 Replies
View Related
Mar 18, 2013
I maintain the data flow at my work. We send and receive the data using excel files with specific formatting that I then upload to the database. Each time I send or receive the excel file I must log them, this is what my code question refers to.
I use RDBMerge to merge all the contents of the 100 plus excel files into one worksheet. The first part of the macro cleans up the merge data for use in the log (i have attached an example of the clean data and finished log).
The blue shaded area of the "Raw_Data" is what the clean data looks like, the yellow column is what current macro records for each record.
As you can see by the example the Raw_Data is only two files LL_LLL_BOB_ToLLLLL_20121228_01 & LL_LLL_BOB_ToLLLLL_20121230_01, each with more that one record.
The log code in column "H" Is based on this criteria:
First Letter of the Unique ID in column "E" - O, M, or L
Program Type in Column "F" - U or R
1. O-U = U
2. O-R = RU
3. M-U = U2
4. M-R = R2U
5. L-R = R
You will note that Columns G-R of the "Log Sheet" correspond to the "Record Type" found in Column "G" of the "Raw_Data" sheet.
This is the area where my skill at using scripting dictionaries fails.
The results for the log list each file only once, but the log code for each corresponding "Record Type" in columns G-R of the "Log Sheet" must contain each unique instance of the code. In other words
if LL_LLL_BOB_ToLLLLL_20121228_01 contains an O-U with an "A" Record Type and an M-R with an "A" Record Type; then, on the log sheet there needs to be the codes "U/R2U" in the cell intersection of the LL_LLL_BOB_ToLLLLL_20121228_01 record row and "A" column (which is column "G")
So, If the File contains one of each code for each Record Type the corresponding cell must house one of each code separated by a "/" without any spaces. This means the cell value could no code, or one code and all the variations in between to all five codes. Also, for ease of human reading the log codes should be concatenated in the 1-5 order that I listed them in (U/RU/U2/R2U/R)
Here is my code so far.
VB:
Option Explicit
Sub test()
Dim dic As Object, a, i As Long, rng As Range, e, w, n As Long
Set dic = CreateObject("Scripting.Dictionary")
[Code].....
View 1 Replies
View Related
Feb 9, 2014
I have saved this on a 2010 workbook as I am at home but this will be used on a 2003 workbook.
I have several projects on one spreadsheet which multiple users will be working and I am trying to create a summary sheet of the work carried out.
Each user is expected to carry out a task on each row of the data held in each worksheet (research, call, update etc) and each task (Option 1-5) is assigned a value. Each user is expected to meet a certain level of points per day to calculate productivity.
I am looking for a sumproduct along the lines of the summary sheet attached but mine just takes one sheet into consideration and I need one for all sheets.
View 12 Replies
View Related
Aug 10, 2009
I am trying to find a formula to count rows that meet multiple criteria, but one of the criteria can be multiple values. I have a list of people with a list of clients that they are responsible for. Each person is responsible for 10-20 clients. Every day I run a report that shows the project worksheets submitted for each client and if money has been awarded or not.
I'm wondering if there is a way to count, for each person, the number of project worksheets that show "awarded" in column K. That would mean that I would have to look for, for each person, any of their multiple clients in column B and "awarded" in column K.
I am trying to put the formula in D2:D9, as I use A2:D9 for a chart. O1:P79 contain the names of the people and the applicants that they are responsible for. A17:D158 contains the list of project worksheets (updated daily). I used =SUMPRODUCT(COUNTIF(B17:B999,P1:P14)) to count the actual number of project worksheets for each person, but I can't figure out a way to modify that to add in the "awarded" criteria also.
View 2 Replies
View Related
May 22, 2009
I am trying to search for multiple values in a cell with Multiple criteria.
E.g: Please find attached the sample excel data.
I tried using VLOOKUP which is not working as I am having multiple values in column A.
View 9 Replies
View Related
Mar 11, 2013
Possible to look up a value from one excel sheet in another sheet and then return multiple values in the first sheet
For example the following is a sample of sheet 1
NSV Code
Item
12
OLANZAPINE
54
ROPINIROLE
And the following a sample of sheet 2 :
Nsvcode
Item
Division
Speciality
Qty
12
OLANZAPINE
CLINICAL
CS
10
[Code] .....
I want to lookUp the NSVCode from sheet 1 in sheet 2 and return the speciality and qty values in sheet 1 as sheet 2 contains a lot of info I do not require and sheet one also contains various pricing calculations I also require.
View 7 Replies
View Related
Jan 31, 2013
I'm trying to sum YTD values which are in columns based on multiple criteria in order to get the data that I need. Here's a sample of my data:
A
B
C
D
1
Country
Month
Brand
Volume
2
A
Jan
Yellow
100
[Code] .....
Country YTD Brand Volume
A Mar Yellow ?
I'd like to get the Mar YTD Volume for Yellow in Country A and tried to use SUM and INDEX but can't seem to get it right.
View 3 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 12, 2003
Sumif formula. I need to somehow put a loop on the criteria or something...
The formula is sumif(range, criteria, sum range). The problem I am having is that I have about 20 criteria to select, and sumif is designed for one criteria. I know that I can type the exact same formaula in and have something like this...
=SUMIF(D25:D107,Variables!A6,F25:F108) + SUMIF(D25:D107,Variables!A7,F25:F108) + SUMIF(D25:D107,Variables!A8,F25:F108) + SUMIF(D25:D107,Variables!A9,F25:F108)+ SUMIF(D26:D107,Variables!A10,F26:F108)+ SUMIF(D26:D107,Variables!A11,F26:F108)
This is OK for a few variables but some formulas have multiple criteria in excess of 15-20. the other thing is that the criteria list will grow over time as more variable are added, and instead of changing the formulas throughout the spreadsheet, I would like to have a big range that I can slowly fill up as I go along
The ideal situation would be if I could put a range in ( ie A6:A30) and then when I get a new criteria I just add it to this range. This will make administration eaaseier by giving me one location to update data.
I will show you an example.. I want to calculate the amount of petrol I use based on three criteria - BP, Shell, Caltex ( the criteria being petrol stations that come up on my bill).
Column
A/ B / C/ D
Row / description / Amount/Variables
1 / Shell Petrol / $10/Shell
2 / Cat Food/$13/BP
3 / BP Store/$24 / Caltex
4 / Dog Food/$23
5/Shell Petrol / $98
6/Caltex Petrol/$31
7/BP stuff/$30
8/Shell Store/$70
What I have been doing is this...
=sumif(B1:B8,D1,c1:c8) + sumif(B1:B8,D2,c1:c8) + sumif(B1:B8,D3,c1:c8)
If I could somehow get the formula to choose the whole column D as the criteria range and get the 'sum if' to somehow loop so it goes down the d column and does the same calculation, but just changes the criteria variable each time, then instead of changing the formula, I could just add to the D column each time I had a new criteria I wanted to check.
I looked into DSUM and Pivot Tables. DSUM seems to work well with numbers, not variables and Pivot tables did my head in, especially seeing I know that there is a way to do this in a formula based way..
View 9 Replies
View Related
Oct 7, 2009
I have successfully been able to rank items using two criteria but I'm unsure of the syntax for adding a third or fourth set. I'm new to the use of Sumprodoct.
I have attached a file. Where column C and E have the same value, I want Excel to look at column F and assign a rank (formula is in column K) in ascending order.
ACEFK
ItemLife LeftTot CostBalRank
signs1 250 250 1
paint curb1 500 500 2
tree work1 2,500 2,500 3
landscape2 9,000 7,200 4
siding2 9,000 7,500 4
Here is my formula:
=IF(ISERROR(RANK(C8,C$5:C$105,1)+ SUMPRODUCT(--(C8=C$5:C$105),--(E8>E$5:E$105))),0,RANK(C8,C$5:C$105,1)+SUMPRODUCT(--(C8=C$5:C$105),--(E8>E$5:E$105)))
I've attached a file.
View 7 Replies
View Related
Nov 15, 2006
I want to lookup values from one rawdata worksheet and have values appear on another sheet, but using 3 lookup values as the criteria. I cannot do this function with Vlookup as it only takes one criteria. I have looked for similar threads in this forum and came across an Index/Match function, but I cannot get it to work. I am attaching file. The data is in the 'rawdata' worksheet. I want values to appear in 'Input' worksheet, grey area using the 3 criterias (Dept number, GL number, and Seg code number).
View 4 Replies
View Related
Apr 6, 2014
Spreadsheet 'Raw Table' has many, many rows, and a dozen columns of raw input data. Each row has date, number, and text fields.
Spreadsheet 'Dashboard' is a somewhat simple one page summary of one day's worth of data entry from Raw Table. Dashboard isn't a simple table, but more form-like in appearance (not sure if that matters). Essentially, the values from Raw Table are spread out in different locations in Dashboard, and not just in a row.
The Dashboard fields gets their values from Raw Table based multiple criteria, the most important being a manually inputted date entry at the top of the Dashboard. The other criteria are simple numbers (ie, 1, 2, 3, 4....) built into the formula. So a set of criteria for one particular cell in the dashboard could be: 4/6/2014, 1, 3. Those 3 criteria are unique identifiers: there is only one match, ever. If those criteria are met, then a value from a specific spot in that row from Raw Data is placed in the dashboard field.
Here is one formula example for one such field in Dashboard:
=INDEX(('RAW DATA'!$D$1:$D$20000),SUMPRODUCT(('RAW DATA'!$A$1:$A$20000=$D$1)*('RAW DATA'!$B$1:$B$20000=1)*('RAW DATA'!$C$1:$C$20000=3)*ROW('RAW DATA'!$D$1:$D$20000)))
The first sumproduct criteria is a match for the date in Raw Data's column A with the manually inputted date in Dashboard located at D1.
The second criteria is match for the value in column B with a value of 1. The third criteria is match for the value in column C with a value of 3. (Again, this is an example. The 1,3 set can be any specific combo from 1,1 to 8,8. Thus, a day's data can have no more than 64 records of a dozen date, number and text fields.)
The Index function is obviously array,row,column. So this example index function results in the contents of column D in the row that the sumproduct function generates.
This dashboard spreadsheet works perfectly everywhere except four fields (out of 100+ instances of the same formula). In the faulty spots, the value that the index function is returning in Dashboard is basically the contents of column D in Raw Table in the row that corresponds to the place the formula is located in Dashboard. It's almost like there's an error in the formula result, and the index is defaulting to the row number in Dashboard. But I've tested this 8 ways til Sunday. I can even straight-up copy these four faulty fields to another part of the dashboard, and the results are magically correct. And I've checked to see that the actual sumproduct function result is returning the correct row for these fields. I don't have an answer as to why it's defaulting to the formula row number in just these four locations, and not the row value that the sumproduct function is correctly producing.
I've thought maybe it was a data type problem in Raw Data, but that route produced a dead end. It has to be something with the Dashboard. I'm going to start over with a new Dashboard spreadsheet, and see what happens. One thing I did was, early in development of the dashboard, use ctrl-shift-enter in the first faulty field, then went back and changed the formula wholesale to something different, and copied that cell to (at least I think) the three other faulty spots. But if I went back and completely changed the formulas and did away with the brackets, the cells should be fresh, no?
View 6 Replies
View Related
Jun 9, 2014
I'd like to extract the data from Sheet 2 (Data) that falls within the selected date range but the formula I've entered in F$9 (see below) is giving me an error
=IF(ROWS(A$9:A9)>$A$5,"",INDEX(Data!A$2:A$387,SMALL(IF((Data!$A$2:$A$320>=$A$2)*(Data!$A$2:$A$320<=$B$2)*(Data!$B$2:$B$320=$C$2),ROW(Data!$A$2:$A$334)-ROW(Data!$A$2)+1),ROWS(A$9:A9))))
test1.xlsx
View 3 Replies
View Related
