VLookup, if condition exist insert row!!! 1

[Davefeet]

I have two worksheets, in the second worksheet I want it to lookup a value in one column of all the records, and then if the value I'm looking for exist in any of the rows I'm looking in, I want it to insert that whole row into a row in my second worksheet.


Example
Worksheet one

Name SSN Info
Dave 3369 Bad
Matt 5596 Bad
Mike 5589 Good



Worksheet two

Name SSN Info
(only the rows that have a "Bad" in the "Info" column inserted here)

But the rows in worksheet one are about 1000 strong, and I want the function to look through all those for my criteria, if it matches insert that row into my second worksheet.

Thanks
Dave

SLC: Olympics 2002, Great skiing, Great Symphony, Great Weather, Great Community, was rated as one of the best places to raise kids.

 

[Davefeet]

Or does this have nothing to do with vlookup, and there is another reccomendation.

Dave SLC: Olympics 2002, Great skiing, Great Symphony, Great Weather, Great Community, was rated as one of the best places to raise kids.

 

[DaleWatson123321]

Thanks for your email. As you'll see in the file I'm sending you, Excel's "Data - Extract" function is an extremely powerful option.

I've also included a variety of other example files that show different uses of Excel's "Data Extract" function, and database formulas.

I hope this helps. Please advise as to how it works out.

Regards, ...Dale Watson

HOME: nd.watson@shaw.ca
WORK: dwatson@bsi.gov.mb.ca

 

[xlbo]

Functions CANNOT change worksheets. You cannot have a function that will insert a row for instance. This must be done through code Rgds
~Geoff~

 

[DaleWatson123321]

For those who are not aware, I should be clear about my reference to the term "function".

Geoff is correct in that "mathematical" functions CANNOT change worksheets.

HOWEVER, my reference to the "Data-Extract" function refers to the function of extracting data - from one sheet to another. While this can be done via Excel's menu, using Data - Filter - Advanced Filter, it is preferable to use VBA. The primary reason: There is a BUG in Excel that surfaces when you use the menu method. It tells you that you CANNOT extract data to a SEPARATE sheet. Ironically, it is the ERROR message itself that prevents this from being possible via the menu. Via the VBA route, it IS possible.

Another BUG exists in the form of a message that tells you it's NOT possible to place your criteria (for the type of data you want to extract) on a SEPARATE sheet. Again, this IS possible, and not only possible but "preferable". This is because having your criteria on the same sheet will cause Excel to develop some "difficulties" in relating your criteria with the field names in your database. When placed on a SEPARATE sheet, the criteria do not have this same problem.

There are still some "quirks" about how the criteria needs to be entered. However, once one gets used to the manner in which the criteria needs to be set up, one quickly realizes that "the sky is the limit" in terms of being able to write VERY COMPLEX criteria - for use both by the "Data-Extract" function, and by "database formulas" - which can include (mathematical) functions such as: =DSUM, =DCOUNTA, =DMIN, =DMAX, etc.

While on this topic, I want to express my concern for Microsoft not including FOUR "database functions" that existed in the pre-Windows version of Lotus 123.

When Microsoft copied this "database functionality" from Lotus 123, they ONLY copied the "Data-Extract" function, and did NOT copy the following:
a) Data - Find
b) Data - Modify
c) Data - Append
d) Data - Delete

These customized "data manipulation functions" are EXTREMELY powerful - and FAST - because of the customization and because they are written in the more powerful "C" language.

Without them, Excel users have had to go to great lengths to develop VBA routines that "loop" through database lists. The development takes time, and the execution of the code takes CONSIDERABLY longer than these customized (data manipulation) functions.

I hope I've managed to clarify this situation. If anyone has any further questions, please don't hesitate to ask.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[xlbo]

Thanx Dale - obviously, I wasn't inferring that your method wouldn't work. Was just posting to inform Dave that his original request was impossible Rgds
~Geoff~

 

[DaleWatson123321]

Hi Geoff,

Thanks for your confidence in my method. But I do feel that your reference to the term "function" was indeed well-timed and appropriate.

I've often felt a bit concerned about referring to "Data-Extract" as a "function" - because of potential "conflict" with the "formula" functions.

This gave me an opportunity to point out the distinction between the two.

Thanks. ...Dale