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.
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
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.
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.
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.
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 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 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.
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")
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 automate creation of pivot table, where the first three fields will go into the RowLabel field, and the rest of them will go into the values as "Sum of ____".
Is it possible? Because I could do it one by one; however, those fields are different every time (there are different number of those fields, and their values are also different).
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?
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.
I 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.
