Well put. THanks. I will look some more tomorrow.

"T. Valko" wrote:

hoping you could explain the beginning of the conditional....
=IF(ROWS(I$2:I2)<=I$1

That's a "pseudo" error trap.

I$1 holds the formula that returns the number of rows that meet the
condition. In the sample file there are 3 rows where D="approved" and F=1.
This means that we need to copy the array formula to *at least* 3 cells to
get all the results.

Since this is *dynamic* and the number of rows that will meet the conditions
will change over time we need to copy the array formula to more cells to
allow for future addition of data. That's why in the sample file I've copied
the array formula to many more than just the 3 cells needed.

ROWS(I$2:I2) compares the number of cells the formula is copied to against
the value in I$1. If the formula is copied to more rows than the value of
I$1 then the formula returns a blank cell. This is a more efficient means of
error trapping. Without some means of error trapping the formula would
return #NUM! errors after the 3rd cell.

Knowing how many cells to copy the array formula to is something only you
will know. Since this is a complex array formula it's kind of calculation
intensive and for that reason you don't want to copy to more cells than is
necessary. For example, if your table has 100 rows and it's possible that
all 100 rows will meet the conditions then you'd have to copy the array
formula to 100 rows. However, if it's only possible that at most 10 rows
will meet the conditions then you have to copy the formula to 10 rows. So,
you're the only one that knows how many rows the formula needs to be copied
to taking into consideration future data addition.

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
Hi Biff,

All these functions are new to me, I think I am getting it but was just
hoping you could explain the beginning of the conditional....

=IF(ROWS(I$2:I2)<=I$1,

Thanks.

"T. Valko" wrote:

Here's a small sample file that demnstrates this:

xExtractConcat.xls 15kb

http://www.freefilehosting.net/download/3g58a

I put everything on the same sheet so it'd be easy to see what's going
on.

The formula in the yellow cell counts how many rows meet the criteria.

The green cells contain the array formula** that extracts the data that
meets the criteria.

I'd use dynamic named ranges for this since it sounds like the amount of
data is added to. This will automatically update the ranges used in the
formulas.

This is kind of "involved" but it should get you started in the right
direction.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
Right now 105 rows which will slowly grow over time

"T. Valko" wrote:

I have a worksheet w/ many ... rows.

How many rows? 100? 500? 10,000?

How big is this list (in rows) after you've created it and removed all
the
blanks?

This could be done with an array formula but it would be calculation
intensive if there are more than a few hundred rows involved.

--
Biff
Microsoft Excel MVP


"PAL" wrote in message
...
I have a worksheet w/ many columns and rows. On a separate
worksheet, I
am
trying to create a concatenated list based on 2 criteria. To make
the
list,,
I use the formula and drag down the number of rows. This results in
a
list
with blanks throughout the list and I end up manually moving the
blanks.
Any
way to remove the blanks automatically. This will be a pain
everytime
I
refresh the list.

=IF(AND(Data!D2="Approved",Data!F2=1),CONCATENATE( Data!A2,"/",Data!G2,"(",Data!E2,")"),"")

Thanks.