Stripping or choosing rows from one list to populate another

[Gunther Maplethorpe]

Attached is a sample of a worksheet where I've got one list with all sorts of data. I'd like to populate a second list with information where the first list is either a number, or a particular string (in this case, "Reagent blank").

Currently, I'm using a combination of HLOOKUP and SMALL to determine row numbers of one, or the other (either ISNUMBER or ="Reagent Blank"), but I can't figure out a way to combine them both.

I know that what I'm working with so far is likely to be needlessly complex, but I'm hopeful to find anything that works.

Note: due to constraints beyond my control, macros and pivot tables should not be used.

[NBVC]

Try this:

In P4 of Raw Data enter:

=IF(OR(J4="Reagent blank",ISNUMBER(J4+0)),COUNT(P$3:P3)+1,"")


copied down.

Then in Q4: = MAX(P:P)

Then in A3 of the Process Data sheet enter:

=IF(ROWS($A$1:$A1)>'Raw Data'!$Q$4,"",INDEX('Raw Data'!J:J,MATCH(ROWS($A$1:$A1),'Raw Data'!$P:$P)))

copied down and across.

[Gunther Maplethorpe]

You, sir, are a god among men. Not only is your solution NOT needlessly complex, but it's easily edited to perform other similar feats. Thanks so very, very much!