Query Worksheet & Return Rows Based On Conditions/Criteria

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


Return Multiple Values Across Columns And Rows Based On Criteria

Jun 20, 2008

I have a table with column headings of product ID Numbers (eg.1111) and row headings of Store number (Eg.1) with data showing the time each product was last sold at that store, I need something to consolidate for each store which Product ID's were sold prior to 5pm and what time they were sold.


Store 1 1111 16:40
2222 13:00
Store 2 1111 15:05
3333 16:50

View 9 Replies View Related

Moving Rows To Anoter Worksheet Based On Certain Criteria..

Feb 11, 2009

I have an excel workbook with 5 sheets, the first called 'data drop sheet' & the others called 'Louise', 'Thor', 'Peter' & 'Steve'. In the data drop sheet, the first row is header data and underneath (A2 onwards) are rows of data that I have cut & pasted in. The first column (A) of this data will be the person's name, e.g. Louise Wilson.

I want to be able to move the rows for each person to their respective sheets, starting from row 2 as the first is a header. So, for example, if 'data drop sheet' cell A2 says Steve Brady, I want it to copy the whole line to sheet 'Steve' row 2 etc.

View 5 Replies View Related

VBA Code To Copy Rows To A New Worksheet Based On Criteria

Dec 17, 2008

I need a macro that will search data from a range of cells in one column for multiple criterias and them copy the entire rows to a new worksheet.

Example I have a list of group names

Help Desk

I only want to choose all Network and Telcom rows copy to another worksheet.

View 9 Replies View Related

Copy Rows To Another Worksheet Based Upon Multiple Criteria

Apr 8, 2009

I have seen all kinds of copying routines, but haven't been able to get what I want.

1. Copy Row contents from activeworksheet

2. The activeworksheet will have various row counts

3. The activeworksheet has row 1 as header.

4. The criteria to use is in the current worksheet columns P through Z

5. The criteria to look for is the word inspect. only the word inspect, not words that contain inspect (i.e. "inspection") should not be found.

6. The word could be in column P and in S, but needs to be only copied once, becuase it has meet the criteria, but it also could only be in c

7. Copying the row into a Sheet named "Inspection"

As a bonus would be cool to be able to copy certain cells or the entire row.

View 9 Replies View Related

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 View Related

Return Values From Tables Based On Conditions?

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


View 1 Replies View Related

Return Data From Table Based On Multiple Conditions

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

Return Formula Value/Text Based On Many Cell Conditions

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

Return Range Address Across Different Columns Based On Conditions

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

Conditions / Criteria Based On Arrays With Different Formulas?

Dec 16, 2013

I'm having trouble with doing a similar thing with conditions/criteria based on arrays for different formulas (e.g. IF, COUNTIFS, SUMIFS).

Here's what i'm trying to condition and than use different formulas:

column A - Region
1. Europe
2. Europe
3. Asia
4. Asia
5. America
6. Asia
7. America
8. America
9. Europe
10. America

[Code] ....


1. (IF, AND ?)
For all cases listed in column A that have "university" in the column B, If there is at least one case having "Europe" write 1, if there isn't write 0.

The conditions are "Europe" in column A and "university" in column B.
If there are cases like that, I want formula to produce 1.
If there are no cases like that, I want formula to produce 0.

* In this example the formula would produce 1. for "Europe" and "university".
* If the formula was looking for "Oceania" in the column A with "university" in column B it would produce 0.

2. Using COUNTIF with multiple criteria.
Count all of the cases that have "Europe" in column A and "university" in the column B
The conditions are "Europe" in column A and "university" in column B.
If there are cases like that, I want formula to do count.
* In this example the formula would produce 2 for "Europe" and "university".
* If the formula was counting "Oceania" in the column A with "university" in column B it would produce 0.

3. Using COUNTIFS with multiple criteria.
Count all of the cases that have "Europe" in column A, "university" in the column B and "GER" in column C

The conditions are "Europe" in column A, "university" in column B and "GER" in column C.
* In this example the formula would produce 2 for the 3 conditions.
* If the formula was counting "Oceania" with "university" and "GER" it would produce 0.

I've spent a lot of time searching the Internet for this (e.g.how to use IF with an array, about different functions and formulas...) and found nothing that works.

For COUNTIF(S) they just didn't work, event with ctrl+shift+enter.

View 7 Replies View Related

Summing Ranges Based On Two (or More) Conditions/criteria

Jul 7, 2009

I have a cell that I need to make a calculation if certain other cells =Y, or to show "0" if the other cells =N. I can do the calculation for the Y part but how do I add the N part of it? Here is what I have:


View 13 Replies View Related

Hide Rows Based On Two Conditions?

Apr 3, 2013

I'm trying to hide rows in an excel sheet based on two stipulations. I want to hide the row if it finds a particular value in column B and a different particular value in column K, otherwise i want it to do continue looping until it has hidden all rows that meet both stipulations.

Sub hide_loop()
Dim bl As Excel.Range
Dim blrange As Excel.Range


Currently I have tried different approaches, like a Do Loop, but I could not make that work, and this seems closer. The problem I'm having right now is that with this loop it hides everything found in column B regardless of what is in column K. I suspect this is because of the code following the if statement.''

View 9 Replies View Related

Deleting Rows Based On Set Of Conditions?

Dec 30, 2011

I have the following data

Ticket NumberOriginSectorDescFB DataPax NameClassPointO CCRegionDestin

I need to delete rows based on following conditions, If,

Ticket number, origin, FB Data, Desc, pax name is same i need to delete all rows except the Row which has Destin filled in. In the above example I need rows only with NBO

Result should be as follows,

Ticket NumberOriginSectorDescFB DataPax NameClassPointO CCRegionDestin

I need one more criteria to be tested the above result. IF,

origin, FB Data, Desc, pax name is same and ticket no is A1+1 i need to delete the second row

Final result should be

Ticket NumberOriginSectorDescFB DataPax NameClassPointO CCRegionDestin

View 2 Replies View Related

Delete Rows Based On Two Conditions

Mar 29, 2012

I need a macro that loops through all rows (With the exception of the first row) and looks for two conditions. If those two conditions are met, I'd like for it to delete the row.

The first condition is if Column O does not contain a certain phrase "VTX" VTX isn't the complete value of the column, it might just be contained within a few other notes in that column.

The second condition is if Col S equals "COMPLETE/FOLLOW-UP IMAGING".

So if these two conditions are met, Col O does not contain the word "VTX" and Col S equals "COMPLETE/FOLLOW-UP IMAGING", then that row should be deleted.

View 9 Replies View Related

Vba To Delete Rows Based On Certain Conditions In Column A

Mar 2, 2006

I have a report in which I need to delete the entire row for each cell in Column A that has the name "Defacto" in a certain location in that cell. I am trying to use VBA code in conjuction with the "MID" function [i.e., Mid(Cell.Value, 8, 7) = "Defacto"]. This is the code I came up with (but, obviously, it doesn't work):

Sub DeleteRowOnCondition()
Range(ActiveCell, ActiveCell.End(xlDown)).Select
For Each Cell In Selection
If Mid(Cell.Value, 8 , 7) = "Defacto" Then Rows.Delete
Next Cell
End Sub

As well, there is another worksheet in the same report in which I need to delete all the rows that do NOT meet that condition (while retaining the ones that do).

View 9 Replies View Related

Highlight Non Matching Rows Based On Conditions

Aug 7, 2007

I am having a problem finding or creating a macro for what i am needing to do. I have a workbook with 2 worksheets (2 months july and august) to figure out a billing process.

I need to be able to First of all search each value in Column A on sheet 2 (august) to Column A on sheet1 (July).

My sheet is set up like this where ID is a buyer and Products 1,2,3...are if they have purchased that product from us

1 ID Product_1 Product_2 Product_3
2 100 0 0 1
3 200 1 1 0
4 300 1 0 0
5 400 0 1 0

Better Picture representation attached

Both worksheets are very similar but August may have some changes from July such as new ID's and changed prduct purchases. What I need to do is have each ID in Column A be compared to Column A in the July sheet, but it may not always be the same cell (because of new ID's added they are moved around)..so it need to search for the same ID first then compare. Once it finds the ID, I then need it to compare the rest of the row (the product info) for that ID to see if any products were added or changed to determine billing. And for any changes that are found in the comparison, I need them to be highlighted.

Now in the case that a new ID was added...it wont be in the July sheet, so the whole row would need to be highlighted to notify of a new ID and new billing needs to be added.

This way the billing person can look at the sheet, see the highlited changes and see what is new or needs to be added to a previous ID's billing. Currnelty they are looking at it manually to see if there are changes.

View 9 Replies View Related

Identifying Rows To Delete Based On Multiple Conditions?

Jun 5, 2014

Maybe this has been taken care of in another thread, but still:

1 222222 0 0
2 222222 0 0
4 333333 0 0
5 333333 0 0
6 333333 0 0
8 444455 1 1
9 444455 1 1

I have a situation like above, but what I want to do is:


(create name for set?)



THEN DELETE ROWS(within set) >=2

Or more simply:

If there are rows with multiple Accident ID numbers, name that set,
and if OCC_K and/or OCC_K values are BOTH >= 0,
then delete all duplicate rows?

Yes I figure there needs to be some way to distinguish Row 1 from Row 2, (flagging one of the two?) so that ROW 2 can be identified, and similarly with Row 4 from Row 5 from Row 6, but the general flow of commands is what I'm trying to get at.

Is it also possible to use AND and OR in the same line? Or BOTH?

(I have other variations on this that I also like to ask about, such as multiple rows within a set that meet certain conditions that are identified in a different field, but for now this is a simpler version.)

I would like to think I'm not going to need a macro, and that I can do this in a Formula.

View 10 Replies View Related

Toggle To Hide & Show Rows Based On Conditions

Mar 4, 2008

First, I realize there are plenty of hide cells threads but I have yet to find one pertaining to my situation and I apologize in advance if I this solution has already been posted.

The Problem: I am looking for macro code to a toggle button that will hide various rows that have no value between multiple sets of rows. The toggle should refresh the format of the rows as the information that was blank could later on have value.

The Setup: For each set of rows, the first row will have the label T and the last will have the label S. In between T and S there can be data. If the cells between T and S are all empty then the rows including T and S should be hidden other wise only the non blank cells between T and S should be visible.

The Reason: I have a master database worksheet, there are four copies of the master database worksheet each entitled phase1, ""2, ""3, & ""4 respectively. These phase sheets are linked to the master sheet and show the entire row's data based upon the beginning cell of each row showing either phase1, ""2, & so on.. The data is broken up into many sub databases and traditional auto filters or advanced auto filters will not be applicable as the title of the data and the empty rows in between need to be hidden if the data is empty.

View 9 Replies View Related

Auto Copy Part Of Row In A Worksheet To Another Worksheet Based On Criteria

Dec 15, 2009

I'm a novice Excel 2007 user and appreciate all the help I can get. I have a workbook with monthly worksheets in it. When a certain data Type is selected from a drop down menu in that monthly worksheet than I would like to have it automatically enter specific data (Name, Date, Eval, Type) copied to another worksheet (CC) in the same workbook. I have been manually entering the data so far. Another thing, some of the data will be entered into the Monthly worksheets and some will only be manually entered into the CC worksheet so it would need to accomodate both methods of data entry. Please let me know if I need to clarify. I have attached the workbood, too.

View 11 Replies View Related

Excel 2003 :: Hiding Rows Based On Conditions On Two Worksheets?

Jan 4, 2013

I have two worksheets (Sheet1 and Sheet2). Based on meeting a condition on Sheet1 AND Sheet2, I'd like to use macros to automatically hide rows on Sheet2.

Sheet1 sample:


The conditions are: If on Sheet1, a Type has the Selection "Include", then any rows on Sheet2 which have that same Type are shown (ie. not hidden).

If on Sheet1, a Type has the Selection "Exclude",then any rows on Sheet2 which have that same Type are hidden.

So in the example tables above, if ONLY Type A = Include, then on Sheet2, the rows for Sample URL1 and Sample URL2 are shown, and the others are hidden.

Also in the example tables above, if Type A = Include AND Type B = Include (and all other Types are Exclude), then on Sheet2, the rows for Sample URL1, Sample URL2 AND Sample URL3 are shown, and the others are hidden.

I'm fine with the concept of If... And... on the same worksheet, it's trying to get it to look at two separate worksheets I'm struggling with!!

View 3 Replies View Related

Excel 2003 :: VBA / Hiding Rows Based On Three Conditions On Two Different Worksheets

Jan 14, 2013

I have a workbook with two worksheets, Sheet1 and Sheet 2. Based on the data entered in the 2nd column on Sheet1, I'd like a macro which hides rows for Events which do not meet the criteria entered on Sheet1.

So in the example below, on Sheet2, I only want to see rows where the Country=Scotland, AND the Location=Glasgow, AND the Start Date is greater than (or equal) the Start Date on Sheet1.

Sample of Sheet1


Start Date (dd/mm/yyyy)


So in this scenario, only the FIRST row meets the 3 criteria (Scotland, Glasgow, date after 30th April), thus all the other rows should be hidden.

View 6 Replies View Related

Updating Contents Of One Worksheet Based On Criteria In Another Worksheet (2)

Mar 10, 2009

OK, two files attached in the zipfile, pricelist-half.xls and pricelist-full.xls

The background is that i exported a file from our warehouse system so we could update prices and re-import it. That is the pricelist-half.xls

After our staff had spent a few days working on it, it came to light that (as the name suggests!) its only about half the products that should have been exported that are on the list.

Hence the second file, pricelist-full.xls which as its name suggests is the full
product list.

What I need to happen, to make this as painless as possible, is somehow for the items/rows that are on the full list, but not on the half list to be inserted to the half list but also have a yellow background for their rows so they stand out clearly.

View 10 Replies View Related

Populating One Worksheet With Data From Other Worksheet Based On Criteria?

May 22, 2013

I have two sheets in one workbook; one has a list of names in column A (136 of them) and another is a master list with all the names of people who work for the company (over 2000). Sheet 1 and Sheet 2 respectively. I need to populate columns B-E in Sheet 1 with the data in columns B-E in Sheet 2, but only for the names that match in column A. I've tried applying filters but can't get to the data I need for some reason. The names are formatted the same way in both sheets (Last, First). In Sheet 2 the names are links but in Sheet 1 they aren't; not sure if that's important.

View 7 Replies View Related

Return A Value Based On Criteria

Jul 3, 2014

In range B1:C5, I want to return the value in column B to column A if column C is empty as shown in the following table.

Coml A Coml B Coml C
S3S2 89
S5 67

View 2 Replies View Related

Return Rows That Fit Criteria Via Index / Match?

Nov 6, 2013

I have a dataset of a few hundred rows, but will only provide a small sample of rows for this example.

I need INDEX/MATCH to return the correct values for the subset below.

Column A (Forecast Status) - Column B (Account Name)
Commit - Account01 (Row 1)
Commit - Account02 (Row 2)
Won - Account03 (Row 3)
Won - Account04 (Row 4)
Won - Account05 (Row 5)
Upside - Account06 (Row 6)
Lost - Account07 (Row 7)
Upside - Account08 (Row 8)
Won - Account09 (Row 9)
Commit - Account10 (Row 10)

In a new tab, I am creating a 'dashboard' view that will group each of the following:

Commit ONLY Accounts in cell A1 (Formula to be copied down to retrieve Rows 1,2,10)
Won ONLY Accounts in B1. (Formula to be copied down to retrieve Rows 3,4,5,9)
Upside ONLY Accounts in C1. (Formula to be copied down to retrieve Rows 6,8)
Lost ONLY Accounts in D1. (Formula to be copied down to retrieve Rows 7)

View 1 Replies View Related

Match Two Criteria And Return Multiple Rows

Oct 26, 2011

I'd like to return multiple rows with two matches on criteria (ID and First Term). I'm able to return all grades for a given ID but do not understand how to get the second criteria, First Term, in my statement.

=INDEX($F$2:$F$12, SMALL(IF($A16=$G$2:$G$12, ROW($G$2:$G$12)-MIN(ROW($G$2:$G$12))+1, ""), COLUMN(A1)))

Code reflects the ALL GRADES data below.

1 Biology 301 12345 4 B 123456
2 Biology 302 23456 4 B 123456
3 Biology 303 34567 4 A 123456
3 Biology 338 65432 4 C+ 234567
3 Biology 338 54321 4 A 345678
1 Biology 336 43210 5 B+ 456789
1 Biology 337 43210 2 A 456789
1 Biology 338 65432 4 B+ 456789
1 Biology 301 12345 4 A 567890
2 Biology 302 23456 4 A 567890
3 Biology 303 34567 4 A 567890

Row Labels Min of TERM Count of GRADE First grade All grades
123456 1 3 B B B A #NUM! 234567 3 1 C+ C+ #NUM!

345678 3 1 A A #NUM!

456789 1 3 B+ B+ A B+ #NUM! 567890 1 3 A A A A #NUM! Grand Total 1 11

* Need all grades where TERM = Min of Term

View 7 Replies View Related

Excel Summation Query From IF Conditions

Jun 30, 2014

I was trying to use IF and AND conditions to calculate values for a Col. but when I attempt to sum.

It appears that the value is always "0". Below is my test data.

Col. H Col. J
Qty Result
Row 713 0 <-Excel Formula: =IF(AND(H7>20,H7<=50),"1","0")
Row 825 1 <-Excel Formula: =IF(AND(H8>20,H8<=50),"1","0")
Row 960 0 <-Excel Formula: =IF(AND(H9>20,H9<=50),"1","0")
Row 10Total 0 <-Excel Formula: =SUBTOTAL(9,I7:I9) / =SUM(I7:I9)

View 3 Replies View Related

Function To Return Value Based On Two Criteria?

Aug 10, 2014

I'm working on a spreadsheet that I need to return a value to "Unit Price" field in worksheet "Master Inventory" based on matching the "Product" field and the "Construction" field from the "Unit Pricing" worksheet.

In essence, I would like the "Unit Price" field to match the "Product" field from the "Master Inventory" sheet to the "New Product Description" field on the "Unit Pricing" sheet, then match the "Construction" field on the "Master Inventory" sheet to the column headers on the "Unit Pricing" sheet and return the value that corresponds to both criteria.

Ex: On the "Master Inventory" sheet, I would like the "Unit Price" field to match the "Product" (Book Browser) to the "New Product Description" (Book Browser) on the "Unit Pricing" sheet and then return the value where the "Construction" (Laminate) matches the column header (Laminate) on the "Unit Pricing" sheet which would return the value of "$240.00".

I've tried using a vlookup function, vlookup/match function, index/match function and an index/match/match function. I've attached a sample workbook.

View 3 Replies View Related

Return Data Based On Criteria?

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.

3 85934942

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.


View 1 Replies View Related

Copyrights 2005-15 www.BigResource.com, All rights reserved