Array formula
 

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(F irst,SMALL(IF((ISNUMBER(First)*(First0)),ROW(Firs t)-ROW(Sheet1!A$1)+1),ROWS(Sheet1!$1:1)))&"

"&INDEX(Second,SMALL(IF((ISNUMBER(First)*(First0) ),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.

Array formula
 

You could use AutoFilter and filter on the amount column based on either
nonblanks or use Customis greater than0.

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

--
Biff
Microsoft Excel MVP


"art" wrote in message
...
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(F irst,SMALL(IF((ISNUMBER(First)*(First0)),ROW(Firs t)-ROW(Sheet1!A$1)+1),ROWS(Sheet1!$1:1)))&"

"&INDEX(Second,SMALL(IF((ISNUMBER(First)*(First0) ),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.

Array formula
 

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.

--
Thanks,
Shane Devenshire


"art" wrote:

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(F irst,SMALL(IF((ISNUMBER(First)*(First0)),ROW(Firs t)-ROW(Sheet1!A$1)+1),ROWS(Sheet1!$1:1)))&"

"&INDEX(Second,SMALL(IF((ISNUMBER(First)*(First0) ),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.

Array formula
 

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:D),"",INDEX(C:C,SMALL(D:D,R OWS($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
---
"art" wrote:
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(F irst,SMALL(IF((ISNUMBER(First)*(First0)),ROW(Firs t)-ROW(Sheet1!A$1)+1),ROWS(Sheet1!$1:1)))&"

"&INDEX(Second,SMALL(IF((ISNUMBER(First)*(First0) ),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.

Array formula
 

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
---