Kinda involved? I Love it, but need time to digest. Thanks.

"T. Valko" wrote:

Here's a small sample file that demnstrates this:

xExtractConcat.xls 15kb

http://www.fr?eefilehosting.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.