Array formula

[art]

Hello all:

I have the following formula to extract data from a long list. The list has
two columns, the first one with amounts, the second one with items. I use
this Array formula to give me in a list onlt the items that have an amount
next to it. I sense that this array is causing my workbook to work slow.
Please let m know if I can use a different formula, database formula, pivot
tables, sort... that will shorten the calcaulation time.

=IF(ROWS(Sheet1!$1:1)<=COUNTIF(First,">0"),INDEX(First,SMALL(IF((ISNUMBER(First)*(First>0)),ROW(First)-ROW(Sheet1!A$1)+1),ROWS(Sheet1!$1:1)))&"

"&INDEX(Second,SMALL(IF((ISNUMBER(First)*(First>0)),ROW(First)-ROW(Sheet1!A$1)+1),ROWS(Sheet1!$1:1))),"")

"First" is a name range for the column with the amounts.
"Second" is the second column with the items in it.

Please help.

Thanks.

 

[T. Valko]

You could use AutoFilter and filter on the amount column based on either
nonblanks or use Custom>is greater than>0.

Then copy/paste the filtered data to where ever you want.

 

[ShaneDevenshire]

Hi,

You could use a PivotTable for this. The advantage is: 1. Very Fast
updates, 2. No Formulas, 3. Easy to understand, 4. Only need to set up once.

If this approach will work please post back.

 

[Max]

Assume the amounts, if any, are input in B2 down
while corresponding items are listed in C2 down
(Items in col C are assumed unique)

In D2: =IF(B2="","",IF(NOT(ISNUMBER(B2)),"",ROW()))
Copy D2 down to cover the max expected extent of data in col B, eg down by
200 rows. Leave D1 empty.

Then place
In E2:
=IF(ROWS($1:1)>COUNT(D),"",INDEX(C:C,SMALL(D,ROWS($1:1))))

In F2:
=IF(E2="","",INDEX(B:B,MATCH(E2,C:C,0)))
Select E2:F2, copy down just enough to cover the max expected returns per
any single instance, eg down by 30 rows. Cols E & F will return only the
items with amounts, neatly packed at the top.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,000 Files:362 Subscribers:62
xdemechanik

 

[Max]

The earlier non-array formulas set-up will provide the same dynamic
functionalities as the array but at much higher recalc speeds & much smaller
resultant file sizes, besides an easier understanding of what's happening &
easier cross application to handle other scenarios. The only price you pay
for all of that is to set aside a single col for use as the criteria col
(col D).
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,000, Files:362, Subscribers:62
xdemechanik
---