A
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.
I am thinking now that maybe a custom funtion might work faster. Please let
me know if I am right. And if I am right, how to convert this formula to a
vba code.
=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.
Thanks
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.
I am thinking now that maybe a custom funtion might work faster. Please let
me know if I am right. And if I am right, how to convert this formula to a
vba code.
=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.
Thanks