Return Data Based On Two Conditions
Nov 7, 2006
I have very limited vb experience and I believe it’s the only way I can do what I need to get done. I am working on creating an invoice template. I want the price to populate based on two conditions. The two conditions are invoice line quantity and month. Let me provide data ranges. The user will enter a quantity for each invoice line moving down B17, B18, B19, B20 etc up to 24 lines. (through B40). The user will enter a current date in F13. Away from the main body of the invoice:
Range T5 thru T16 will be pre-populated with each month of year 2007. So T5 = 1/1/2007, T6 = 2/1/2007, T7 = 3/1/2007 etc.
Range U5 – U16 will be pre-populated with the line price for each month if the line quantity is less than 10
Range V5 – V16 will be pre-populated with the line price for each month if the line quantity is 10 or more
The price should autopopulate in G17 - G40 where a quantity is placed in the corresponding column range B17 - B40. The number of lines varies per invoice so I'm setting the template up with sufficient lines. As an example, the price should populate for each invoice line beginning in G17 to correspond to the month entered in F13 and quantity entered in B17 based on the corresponding month in T5-T16 and the price in either U5-U16 or V5-V16 given the quantity. So if the Month in F13 was February and the quantity in B17 was 25, the price that populates G17 would come from V6. If the Month in F13 is March and quantity in B17 is 5, the price that populates G17 would be from U7. I just don’t have enough experience to write this myself.
View 7 Replies
ADVERTISEMENT
Jul 26, 2014
Want to be able to select data from Column C (3 data validations/drop down menus) and have the corresponding values display in the appropriate fields in column H. Not sure if this is possible?
View 4 Replies
View Related
Aug 22, 2013
how to return values based on some conditions.
So for the sake of this example i have two cells lets call them J1 & J2 , one with the client name and another with State (matches the table name)
How can i get it so when i type in say Client 1 & Table 3 it will return the information in client 1s column into table 1s rate column. Obviously i want to be able to type in any clients name / table and have the same thing happen.
the red is what i want returned into table 1s rate column
Table 1
Table 1
Table 2
Table 2
[Code].....
View 1 Replies
View Related
Oct 16, 2006
This is to manage which departments (approxiamately 30) within a business need which compulsary training (approximately 11 courses)
Spreadsheet currently reads list of new employees and I want to be able to have "YES" or "No" values under the different courses
Is there a formula/function that i can use (like the IF Formula) to complete the following information;
EG: =IF(OR(A3=H2, A3=H5 etc... ), "YES", "NO"
Column H lists all departments
Column A lists deaprtments
A3 representing the 1st Department needing training
View 8 Replies
View Related
Apr 23, 2008
I am trying to return a cell address range based on values in different columns. Attached is an example.
In Column A1 I have "Range" and below that a set of numbers from 1 to 31.
In Column B1 the name "Test" is used as an identifier, below that are the letters "a" though to "e". Once the letter "e" is reached the identifier "Test" is moved to column C "C6" and the alphabet continues from "f" down to "j". This process is repeated through the rest of the alphabet and the identifier moved to the next column after every 5th letter.
What I want to do is as follows:-
I would like to know the cell addresses of all instances of "test" from Column A numbers 6 throught to 24. The result should be C7:C12, D14:18 & D20:24. The results should ignore all instances of the identifier "test" and only return the cells which contain the letters of the alphabet.
I have tried using Vlookup which works well if the letters are all in the same column and I have incorporated the Address and Match formulas to return the range if all the alphabets are in the same column, but I do not know how to do this if the identifier "test" and letters move to a differnt column.
View 9 Replies
View Related
Jun 1, 2008
I am trying to query a names sheet where each row may contain more than one occurence of a member ID. There are no duplicate rows ( records), because the dates are different for all rows.
1. I want to retrieve all of the rows from the names sheet that match a unique member ID. So far I only get one row.
2. And if mistype the number, it gives me a wrong record .I would like to get an error message that if I don't get a match, that it appears in the somewhere like dialog box or even entry in one of the cells that no name exits.
View 4 Replies
View Related
Sep 25, 2012
I created a master data sheet to enter data manually to analyze. It contains 20 columns, and rows(continue change, because we enter data all of the days). I need to extract data to another sheet, when the user enters data in the master data sheet, but with some conditions, when the column 6 contain the word ASQ, and then extract all of the data, but the new sheet do not contain 20 columns, because the data that contains the word ASQ have only 16 columns.
View 6 Replies
View Related
Sep 28, 2012
I need to create a macro to extract data from sheet1 to sheet2.
The sheet1 contains 15 columns, and the data starts at row 5.
The sheet2 contains 15 columns, and the data starts at row 5.
We enter data all of the days, so we have like 500 rows in the sheet1.
Only 2 conditions to extract data from sheet1 to sheet2:
if the column9 of the sheet1 contains the word "NO" and column11 of the sheet1 contains the word "ASQ".
View 4 Replies
View Related
Jun 23, 2009
method to extract certain data from a column of values.
The attached workbook has two sheets. From the worksheet named "Data" I would like to be able to extract any values >0.00% and then display the corresponding "model" (C:C) on the other sheet called "Results". I need to be able to achieve this for each day in the month and disregard all other values so I am left with a concise list.
I have manually entered the info for the 1st June as an example.
View 6 Replies
View Related
Jul 7, 2014
I am moving along in my masters project and I am having a problem organizing some data I got dumped with.
What I have is a 196 x 196 matrix of the worlds countries and the distance between each one. So each Row has a title header of a country and each column has a country and the cells between the two are the distance of said countries.
On another sheet I have about 10 000 different combinations of dates/years/months ect that I need to populate in order for Stata to work properly.
Rather than manually enter thse in one by one or copy paste what I would like to do is write a code that would do something like this.
If on sheet 2 (the combination sheet) A3 = Afghanistain and C3 = China than F3 = The China/Afghanistain intersection (In this case AN3 on the Distance Sheet)
I am having problems because I can't see to figure out a way to have a program lookup the intersection and give me the China/Afghanistan number based on all the conditions. I am assuming I need to use a matrix lookup of some sort. This way when I do some like Canada/Denmark on the Combination sheet, it will automatically look up and populate the distance from the distance sheet (That being AZ33).
View 10 Replies
View Related
Nov 14, 2008
I work in Local Government and I am recording the contact that we receive for each transaction that are avoidable or unavoidable. This is broken down into service areas.
The data is imported into excel from a database and therefore is constantly growing.
What I want to do it a very basic summary page so that I can group those that are and those that aren't avoidable for each service.
Because the data is ever expanding I would like this on a separate sheet from the actual data.
I attach a sample of the worksheet and would be grateful if someone could advise what formula to use on sheet 2 in cells c4, c5, d4 and d5 to make it do it automatically.
View 5 Replies
View Related
Oct 10, 2011
Currently trying to solve a niggling issue with removing duplicate row data from competition entry databases using the following code...
Code:
Sub remove_duplicates()
Dim lastrow As Long
Dim i As Long, j As Long
On Error GoTo errhandler
Columns("A:G").Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2") _
[Code] ......
errhandler:
'MsgBox Err.Description
End Sub
What I need to happen is for duplicates to be based on both the name and email matching (columns A + B) and then remove all duplicate rows, whilst ignoring but preserving the content in columns C,D,E and F on those rows that remain. Column G contains either "Yes" or "No" depending on a users membership status. Sometimes duplicated data will contain identical name and email address but have different membership status in column G where someone was logged in when submitting a form the first time, and then logged out the next (potentially a mixture of the two over multiple times). Therefore I need any duplicate users who have different values in the G column throughout the database to be detected by the macro and we must ensure that the single row retained in the final non-duplicated list displays "Yes" in column G for that user.
Duplicate users who are always "yes" or always "no" work fine, its only the ones with mixed values for column G that need to be analysed and their return forced as Yes in the final list.
The final list should preserve the original column structure and thus be only one row per unique user.
The above code appeared to work the first time with a sample file, but when running the macro on one of my full size lists (3k - 5k rows) it returns 2 rows for each user who started off as a mixture of Yes/No... ?
Both rows will say yes in column G (so that part is correct!) but I have to re-run the macro again on the same dataset for it to remove the duplicate yes rows to get the list as it finally should be!
So I can achieve the result by running it twice, but this is a bit of a cheating way to do it and also the next stage after this involves something similar across multiple worksheets so I need something rock solid to build upon!
View 5 Replies
View Related
Oct 13, 2009
What formula do I use to populate certain cells (E5:E10 and J5:10) based on match with condition (E3) with cells from and in the ranges C2:C73 and D2:D73 without creating milelong IF formulas? I am almost at the goal... past 10pm here in Thailand and still at the office
View 2 Replies
View Related
Mar 24, 2014
I have some problem with the extraction, I would like to extract the data to another sheet based on variable conditions but I can not do it. My code in just case working if the all conditions are specified. The target is that if want the code could extract the data based on one condition even, or two or three or more.
View 7 Replies
View Related
Sep 15, 2009
I’ve created a spreadsheet to record all transactions ( Sheet 1) at a train auction for members only (Sheet 2). Currently, the workbook I created will ‘manually’ create receipts for any buyers/sellers by filling in Member's name in Sheet 1 and then selecting ‘View ‘Member’ Receipt’ button.
Ideally, I would like a macro that will automatically generate a worksheet for any member that has sold or bought an item at the auction and insert worksheets between ‘Start’ & ‘End’ tabs (with option to refresh data) in location order (A-D). Because members come from near and far we would like to generate the members that need to travel the farthest first. Is it possible to create a receipt for only the members that have transactions?
View 4 Replies
View Related
Apr 23, 2009
refer to attached worksheet.
I need a way (Non-Macro please) - where if a condition is met, then fill a range with a particular value. The attached spreadsheet has a sample with explanation.
View 9 Replies
View Related
Apr 26, 2014
I'm trying to auto populate a calender style sheet in Excel 2010 based on data from a Work Schedule sheet. The work schedule sheet contains a list of jobs, with each row representing a different job. There is a column for the start date (e.g. 25/04/14) and a column for the end date (e.g. 26/04/14). There are other columns which select resources such as people and vehicles. Each resource may appear on any one of several columns for each row, e.g. Site Operative 1, Site Operative 2 etc.
On the calendar sheet, in which one cell represents one day (e.g. 25/04/14), all the dates are shown along the top going right and all the resources are shown on the left going down.
On the calender sheet, in every cell I want a formula that will look at the Work Schedule sheet and see if that particular resource is being used on that particular date. If it does, the cell can display information from another cell such as the job number or job name to which the resource is assigned; if it doesn't, the resource isn't being used so it can display "Free" or "Available".
View 2 Replies
View Related
May 8, 2007
I have a multiple column spreadsheet (Call it- "Money") whereby I need the data identified based on a list of account numbers and return this data to a new sheet.
In "Money" I have:
IE; columns B, C respectively have cust #A100 & 20.00
columns E, F respectively have cust #B100 & 40.00
columns G,H respectively have cust #C100 & 60.00
Etc.
(above for illustration-there are 100 lines of data in these columns with varied account numbers and respective dollars)
So what I have now is a new sheet I have named "Control". I have listed all my account numbers like A100,B100 etc. in column A. These are the account numbers for ident purposes.
I need the data entered in "Money" identified by those columns B,E,G with respective amounts from C,F,H and based on the list I have in "Control" whereby in "Control" if A100 is listed in column A then the figure to be returned in column B is all the data bits found in all columns C,F,H from the entire sheet "Money". Tough to explain but ie below....
View 17 Replies
View Related
Mar 27, 2007
I need a formula that will return a value if two conditions are meet. This is probably pretty easy but i am drawing a blank.
I have two columns B & C and in those colums there are numbers from 1-5. And in column A there is a number.
I need the formula to look in column B&C and if the a condition is meet it will return the number that is in column a
A B C
1 2 2
2 1 3
3 3 3
4 2 3
For Example:
I need the formula to look in column B&C where the value is 1 for column B and 3 for column C and return the value in column A which would be (2)
View 9 Replies
View Related
Sep 9, 2009
I have 3 cells,
A1 = " a name"
A2 = " a time"
A3 = " a day ie Thu"
I am trying to get a formula to look in B1:B100 for the cells that match A1, then in those cells look in C1:C100 for cells thant match A2 then in the cells that meet both those look in D1:D100 for the ones that match A3. Then if there are any cells that meet all 3 matches return the value "a" if not return a blank.
View 9 Replies
View Related
Aug 9, 2012
Formula that will return the value in the fourth column. It will find all instances of that person on a particular date and average them together.
Date
Name
Value
Average of that person per day?
7/31/12
Billy
5
8
[Code] ..........
View 4 Replies
View Related
Mar 24, 2009
I'm trying to create a formula that that returns an average value if two conditions are met. No luck here.
I need to be able to get to - Brand = Blue; Value = 2; X Count = average - from the following information. The average is 17.5
BrandValueX CountY CountZ CountGreen1213152Green1405595Blue3107686Red1164965Green21098108Green33077107Blue21796113Blue13970109Red3144963Blue1107787Red31096106Red24663109Blue2165773Red21888106Green31910
View 9 Replies
View Related
Dec 19, 2012
generating a formula that takes into account a range of values (an entire row) and from this row, I would like the formula to select, for example anything greater than 80%. After the formula selects anything greater than 8, I would like for it to select cells that are above or below the cells that have values greater than 8.
1
2 JLKNSTTP
3 85934942
4
5
For example, in the above datas, I would like a formula to select anything greater than 8 in row three and select cells above it. In this example it would be j, k, and t.
=if((3:3>8),offset(A3,-1,1,1))
View 1 Replies
View Related
Dec 8, 2009
How to return all possible values based on a single lookup ( or another condition / macro )
I have a table in B5:E100
In A1 I have a value
I need to look in B5:B100 for the value in A1 then place the contents of B:E for those cells in G5:J5 downwards
View 9 Replies
View Related
Dec 22, 2011
if color(white),pattern(6" tile),finish(gloss),width(>36") & height(>98")then return item ordering# different worksheet datadase.
WALL SELECTIONColor NamePatternFinishExisting
Wall Ht.Wall System stops at:
Wall Repair[SELECT COLOR][SELECT PATTERN][FINISH][SELECT HEIGHT][SELECT HEIGHT][REPAIR]Plum
Wall SpecsSoap Dish Wall SpecsBack Wall SpecsExtra
Wall Specs (if needed)Finished Size:[WIDTH]X[HEIGHT][WIDTH]X[HEIGHT][WIDTH]X[HEIGHT][WIDTH]X[HEIGHT]
Quanity:[QTY][QTY][QTY][QTY]Item Ordering #:
View 2 Replies
View Related
Apr 30, 2009
I have a cell with a %. I want to return text in another cell based on that data i.e. between 90 - 100 = excellent, 75 - 90 = good, or something like that.
View 3 Replies
View Related
Jul 28, 2006
Sheet 1
Column A = Dates
Row 1 = Filenames
I enter a Y in the intersecting cells of the Filenames & Dates to show which files were downloaded.
I need is a formula that will rearrange this layout onto Sheet 2 so that each ‘Y’ (downloaded) filename is inserted in a cell corresponding to the Date.
(see attached sample)
View 9 Replies
View Related
Aug 31, 2006
I have an array in excel, 4 columns: Month, Day, Stock, Price. I dont know the most efficient way to handle arrays. I know i can loop through an entire array to find a specific value, but if i have a large array, this doesnt seem efficient.
For example, I would like to find the price in an Array (or Range), when i know the value of two columns. If this were in a database, I would write sql like this: " select Price from TABLE where month = 1 and Day = 3". The result would be 40.21. How can i do this with an array? I have attached a sample spreadsheet with the example mentioned above..
View 3 Replies
View Related
Feb 12, 2008
My Main data in my workbook is copied from another workbook. From there it is sorted into separate worksheets for each customer via a macro. I want to be able to set something up so that I can list the customers and then list job descriptions, products and pricing from the correct sheet and return the selected data back to its original form in sheet1.
I am also trying to run the macro that sorts the data in this workbook from another workbook.
I have attached the file so you can see what i am trying to explain.
View 9 Replies
View Related
Nov 16, 2009
I have several rows with numbers that is either 0, a balance or a text like n/a. I need a formula that return true if all are 0 or contain n/a. If there is one balance shall it be false. Any suggestion? See example:
View 6 Replies
View Related