populate worksheet conditional from other worksheet in Excel

[TomYC]

I did a search on this here, and from a thread dated 2004 I ended up with something like this:
=IF(AND(NOT(ISERROR(SEARCH($A$1,DATA!$A2,1))),DATA!B2<>""),DATA!B2,"").
Copied both across and down in a new worksheet, this will successfully populate said new worksheet with any rows of data in which the contents of cell A1 in DATA! exist in col. A, otherwise the row remains blank. The problem with that is ending up with hundreds of blank rows and needing to sort, delete, etc., rows. Much better would be some code to do the conditional filtering on the way in. Is that possible? (N.B. I have B2 here because my DATA worksheet contains a calculation column A whose contents are not to be populated, only used for the conditional.)

 

[SkipVought]

Hi,

Please explain the business case for what you are trying to accomplish, rather than addressing this formula.

You want to populate Sheet1 from DATA based on WHAT criteria?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TomYC]

OK--
Let's say I have rows in a worksheet DATA such as this:
Insulin|     NSLIJ-80340 12/27/2010 3
Dilaudid|     NSLIJ-80381 12/21/2010 2
I want to populate another worksheet, named Insulin, only with the records/rows where in column A there is the text string "insulin", a string I have placed in cell A1 in my worksheet named Insulin. So the formula I posted earlier looks for the text string in that cell, and the plan is to conditionally populate the second worksheet (Insulin) with all the rows from the worksheet DATA wherein col. A contains the string that is in cell A1 of my destination worksheet (Insulin). I plan on using this as a template, and have numerous worksheets (e.g. Dilaudid, etc.). And of course I'm hoping to automate this such that if I paste hundreds of rows into the DATA workshseet, I can get all the other worksheets to populate with only the rows relevant to what is in each worksheet's cell A1.

 

[SkipVought]

You do not need a separate sheet for each unique data element value. Each sheet in the process to load and maintain is an expense.

Smart spreadsheet users use feature like PivotTable for selecting and summarizing data. This could be simply accomplished using the PivotTable feature, in about 15 seconds.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}