I have the following 'numerical values'
2510000.011
3010000.011
3510000.011.3
2518000.032.5
3018000.043
3518000.043.7
2530000.125.5
3030000.145.8
3530000.176.2
With the first two values as criterias, I need to find the 3rd and 4th value
Example: If I have the criteria as 30 and 1800 , I should get the result as 0.04and 3
I am attempting to count from a spreadsheet the reference number of a customer (numbers and text) based on two criteria.
1, If column G= Requested
2, Column I = Meeting
Count Row E
I thought a sumproduct was best and have started using it for the first time, I thought this should work but I keep getting a #NUM! error.
I have tried with numbers and it works but the non numeric aspect is difficult.
[Code].....
Have spent ages on this to no avail. Need the attached sheet to verfiy the values in the "Reconciliation" are correct when referencing the "Prices" sheet. I need column F (Reconciliation sheet) to lookup the "Code" in column B in the "Prices" sheet and then further look up the "Payment Frequency" (column D in the Reconciliation sheet) in the "Prices" sheet to verify that the price is correct.
Due to slight decimal point issues, if the value has a greater than 0.02 difference then display "ERROR" otherwise 0.
I have encountered a situation where I need to essentially accomplish a reverse Vlookup (using index match) and return multiple values.
View 1 Replies View RelatedI know this is an oft repeated title, I can't get the syntax correct.
In the attached file (only 41 rows) I've tried the following:
in Col AJ4:AJ83303 (I did not attach the whole sheet, formula reflects bottom row of data)
=IF(AI6>0,1(X6=$AJ$1,1(VLOOKUP(P6,$AR$21:$AS$41,2))
returns #VALUE! (for col X as text????)
=INDEX($AR$21:$AS$41,MATCH(1,AI4:AI83303>0)*(X4:X83303=$AJ$1),0)
returns #REF!
COL AI IS NET AMOUNT
AJ1 IS "NR" TO MATCH IN COL X
In all cells in Col AJ I want the amount in col 2 of the index to be added to the net amount in Col AI.
Criteria 1 Where net amount is >0 on any cell in Col AJ
Criteria 2 Where Col X = N1 (which has NR entered)
Col P is where the lookup value for the Index is, how do I reference that in the index formula?
I have a requirement where I need to look up a resource hierarchy with the region and the product line and then decide the value from the mapping table.
May be I can explain more clear with the workbook example attached.
If a user belongs to Dev & QA * hierarchy, check the region, and correspondingly check the product line, on meeting all these select the value from the mapping table to fetch the expense value from Approval process column of mapping sheet.
I am trying to modify an existing nested vlookup formula to include one more condition. I attached the excel data file. There are two tabs:
Tab #1 - Performance
Column B (Email Send Date): can be a repetitive date, something like
Row 2. 4/25/2014
Row 3. 4/25/2014
Row 4. 4/25/2014
Row 5. 4/25/2014
Row 6. 5/2/2014
Row 7. 5/2/2014
Row 8. 5/2/2014
Row 9. 5/9/2014
Row 10. 5/9/2014
Row 11. 5/9/2014
Column F (Product ID): can be same product for different Email Send Date. For instance, Row 2 & Row 9 have the same product ID - 128 and Row 5 & Row 10 have same product ID - 131.
Row 2. 128
Row 3. 129
Row 4. 130
Row 5. 131
Row 6. 567
Row 7. 897
Row 8. 987
Row 9. 128
Row 10. 131
Row 11. 234
Column R: Units Sold - need to retrieve the units sold value from Column D - Units Sold in UnitsSoldOnlineVlookup tab.
The formula needs to lookup the Units Sold from a table in a different tab, named UnitsSoldOnlineVlookup. This table contains the following columns:
Tab #2 - UnitsSoldOnlineVlookup
Column A - Email Send Date
Column B - Product Description
Column C - Product ID
Column D - Units Sold
Before Product IDs were different for each Email Send Date and I successfully used this formula:
=IF(ISERROR(VLOOKUP(F2,UnitsSoldOnlineVlookup!$C$2:$D$31000,2,FALSE)),0,
VLOOKUP(F2,UnitsSoldOnlineVlookup!$C$2:$D$31000,2,FALSE))
Now I need to embed one more condition to this formula - lookup Units Sold for the Product ID as well as the email date:
lookup Units Sold for a Product ID for a corresponding Email Send Date in UnitsSoldOnlineVlookup table and return Units Sold into the corresponding cell in the Performance tab.
I thought to use MATCH function in addition to IF and ISERROR functions but I it doesn't work - I know it is wrong.
=IF(MATCH(B2,UnitsSoldOnlineVlookup!$A$2:$D$31000,0),
ISERROR(VLOOKUP(F2,UnitsSoldOnlineVlookup!$C$2:$D$31000,2,FALSE)),0,
VLOOKUP(F2,UnitsSoldOnlineVlookup!$C$2:$D$31000,2,FALSE))
I have a single page of consolidated data that I need to pull from. The data is formatted like the following
Column A Column B
Category
Subcategory 1 Data
Subcategory 2 Data
Subcategory 3 Data
Subcategory 4 Data
All in the same column. I need a formula that will look for a certain category and then a certain subcategory and then provide the number to the right. The Categories and subcategories are in Column A and the data I want pulled over is in Column B
Im just going to start with the tables that will help show what i am trying to do...
TABLE 1
File #Dateid
215?
211?
27?
26?
TABLE 2
File #StartStopid
29131
214162
2183
Ok so say i have these two tables...i want to be able to look at the file # of line 1 in table 1, go to table 2 and return the id where the date from table 1 is between the start and stop in table 2. ie...in that example it should return id "2".
I would like to take a precise value from one table which corresponds to two different criteria. See the example attached with this message.
View 3 Replies View RelatedI have a problem with vlookup, some of the items in the lookup array are not unique and vlookup gets confused with multiple matches, however if I can use a combination of 1st and second columns as the criterion then it would result in a unique match.
Problem is I don't know how to acheive this in vba (but I can with the worksheet formulas). (attached is a sample worksheet)
In column I, there is a list of dates - month and day only
In column J there is a list of dates, month,day, and year.
--I----------------J
1/14----------1/14/2009
1/14----------1/15/2009
1/14----------1/16/2009
1/15----------1/17/2009
in column K, I am trying to setup a Vlookup statement that states if the month and day in column I = the month and day in column J then return the value in column J.
I have the following formula, but it just returns N/A.
I'm trying to create an Excel based dashboard for my company. One part of the dashboard is a Top 20 Accounts based on territory, region, or district. Each of these three areas has source data in a seperate table. I need the top 20 vlookup to pull from the territory table if I enter a territory name in cell B20, pull from the region table if I enter a region name, and so forth. There are 10 different regions, 2 different districts, and 51 different territories.
I'm thinking that the answer somehow involves an IF statement. e.g. If cell B20 equals a region name, then perform the vlookup on the region table. If it contains a district name, pull from the district table. Otherwise pull from the territory table. I'm also thinking I may want to list all of the territories, regions, and districts in their own seperate column so the formula can say something like "If cell B20 is found in column M, pull from the territory table, if it's in column N, pull from the region table.
I have one spreadsheet that contains employee overtime info per pay period which is bi-weekly, another spread that has their monthly production numbers. So...
Employee OT PayEndDate
John Smith 5 01/13/12
John Smith 7 01/27/12
Jack Jones 8 01/13/12
Jack Jones 9 01/27/12
John Smith 6 02/10/12
John Smith 3 02/24/12
Jack Jones 8 02/10/12
Jack Jones 10 02/24/12
And so on
On the other sheet
Employee Production Month
Jonh Smith 53 Jan
Jack Jones 75 Jan
John Smith 45 Feb
Jack Jones 80 Feb
And so on,
What I need to do is see the overtime hours per month, and the production numbers for the same month. Something like...
Employee OT Production Month
Jonh Smith 12 53 Jan
Jack Jones 17 75 Jan
John Smith 9 45 Feb
Jack Jones 18 80 Feb
I have a pivot table the sums up the OT by PayEnd Date but need to change PayEndDate to month, which I know how to do, but then add in the production any thoughts on how I could do this?
I am looking to pull out an outcome based on the user inputting two criteria, below is an example:
A
B
C
D
E
F
G
H
I
J
[code].....
So if the user inputted P1 into cell H2 and inputted 9 in I2, J2 would show Pass, but if they inputted P5 into cell H2 and inputted 9 in I2, J2 would show Fail.
I have a workbook with two sheets
sheet1(Invoices) is the master and sheet2 (GR) contains information that i need to put onto sheet1.
I need a formula that will allow me to lookup sheet2 based on the criteria from 3 columns on sheet1... (PO no, Part No, Qty)
At the moment I am using 3 different vlookups to achieve what I need
If there are records on sheet2 which matches sheet1 then I would like to have the word MATCH if there is a match and CHECK if there is not?
Getting a formula that will generate the corresponding rating attached to a row value and columns header as per below example:
EnglishSS1 EnglishSS2 EnglishSS3 Rating
10 20 30 3
20 30 40 2
30 40 50 1
Where my variables would be based on whether a person is under EnglishSS1 and getting a score of <=10, the resulting rating would be 3. I know this is doable by doing a vlookup with a range lookup value of false. However I have roughly 70 column headers and it will be a bit taxing to combine an If and Vlookup statement to address it.
I have attached a sample sheet for reference : Book1.xlsx‎
two formulas for one data set. The data is attached in the spreadsheet: "Product IDs". The data is a set of Master Product IDs (parent) and the Linked to them Products (children). I need to create a relationship between unique parents (Master Product IDs) and their children (Linked Products)
I need to create two formulas:
1. From the Data Set table, need to vlookup the unique value in column A (Master Product ID) and return comma delimited (concatenated) corresponding values from column B (Linked Products). So, the result will be as shown in Table 2.
2. From the Data Set table, need to vlookup the unique (de-duplicated) parent/children relationship in column A (Master Product ID) and return comma delimited (concatenated) corresponding values from column B (Linked Products). There are total 3 parent/children relationships in Table 1. So, the result will be as shown in Table 3.
Would like to be able to use a vlookup formula that incorporates multiple search criteria. In C10, I have a specific rank listed. In C11, I have the supervisor listed. The formula needs to list the name of the agent in C12 based on the criteria in C10 and C11.
agent rank score supervisor
A B C D
1 leo 1 99 jim
2 bob 3 68 jim
3 sam 1 73 ann
4 tim 2 84 adam
5 alf 2 55 ann
6 dan 3 42 adam
7 will 2 79 jim
8 sara 1 91 adam
9
10 Rank: 1
11 Supervisor: adam
12 Agent: " "
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].....
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.
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.
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.
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.
I am trying to create a simple user interface type thing so that someone is able to select from drop down lists someones information, such as whether they are male or female, aged between 19-35 or 35-67, whether they are studying in a business area, legal or construction etc (there are 6 variables in total), This will then give the probability of success of the person passing this course based on probabilities which I have already worked out. I have worked out how to do the first stage of creating a drop down list showing alternative choices with Sex, Age etc in the data validation options, however:
There are 517 possible combinations, as in Male aged 19 to 35 studying Business (with other variables) or Male aged 19 to 35 studying Law (+ other variables) etc etc etc each with their own probability of success. Due to the long nature of writing out Male1935BusinessNorthWestWhiteBritishCollegeBrown I have rewritten it so it appears in the excel file as M1935BNWWBCB, which obviously wouldn't make any sense to someone if they had to select M 1935 B NW WB CB from drop down lists.
Along side the M1935BNWWBCB there is the probability of success specific to that type of person. So for example I could would have:
M 60%
M1935 64%
M1935B 35%....
I am trying to simplify a complex array formula using a UDF. I am trying to return multiple corresponding values horizontally. Can this be translated to VBA?
=INDEX($B$2:$B$8, SMALL(IF($A$11=$A$2:$A$8, ROW($A$2:$A$8)-ROW($A$2)+1), COLUMN(A1)))
A11 is the lookup value
A2:A8 is the range where A11 is located
B2:B8 is the value I want to return
I've sorted the second row by price on the second sheet, but how do I get the corresponding category and quantity numbers? A normal Vlookup formula would return '8' twice on (a) and (b), but I need both 8 and 6... So only one value should be returned at both (a) and (b), as long as all different quantities are covered.
View 5 Replies View RelatedI have made a spreadsheet where I want to input data and transport it to a rent card information.
I want to use vlookup to find the apartment number in a different spread sheet and fill in the information on a different spreadsheet (rent, fees, utilities).
I get how to use vlookup to get one value but I need 10 values to return.
Is there a way where i can vlookup a column and return all matches if there are multiple values?
View 5 Replies View Relatedvlook up.xlsx
View 14 Replies View Related