Array formula to code
 

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

Thanks

Array formula to code
 

The easiest way to do this is to record a macro and "enter" the function
again. You really just need to commit the function again. End the macro
recorder and see what you have.
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"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.
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(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.

Thanks

Array formula to code
 

When I record, it just records the same formula i entered. I need a custom
function for this.

"Barb Reinhardt" wrote:

The easiest way to do this is to record a macro and "enter" the function
again. You really just need to commit the function again. End the macro
recorder and see what you have.
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"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.
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(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.

Thanks

Array formula to code
 

Do you want to add it to a series of cells programmatically, or do you want
to actually have a UDF to do it? FWIW, UDF's can sometimes be tricky to get
to recalculate.

Barb Reinhardt

"art" wrote:

When I record, it just records the same formula i entered. I need a custom
function for this.

"Barb Reinhardt" wrote:

The easiest way to do this is to record a macro and "enter" the function
again. You really just need to commit the function again. End the macro
recorder and see what you have.
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"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.
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(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.

Thanks

Array formula to code
 

I want to try UDF.

"Barb Reinhardt" wrote:

Do you want to add it to a series of cells programmatically, or do you want
to actually have a UDF to do it? FWIW, UDF's can sometimes be tricky to get
to recalculate.

Barb Reinhardt

"art" wrote:

When I record, it just records the same formula i entered. I need a custom
function for this.

"Barb Reinhardt" wrote:

The easiest way to do this is to record a macro and "enter" the function
again. You really just need to commit the function again. End the macro
recorder and see what you have.
--
HTH,
Barb Reinhardt

If this post was helpful to you, please click YES below.



"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.
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(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.

Thanks

Array formula to code
 

Hi Art,

Try this UDF, enter as a single 2-column by n-rows array formula, where n is
the max number of 0 items.
it will be a lot faster than your array formula for a large number of rows:
on my system for 250 rows of data it takes 0.004 seconds as opposed to over
10 seconds for the array formula.

Option Explicit

Function ArtList(theFirst As Range, theSecond As Range) As Variant
Dim vFirst As Variant
Dim vSecond As Variant
Dim oRng As Range
Dim j As Long
Dim k As Long
Dim vArr() As Variant
Dim nRows As Long
'
' get the data, allowing for whole-column refs
'
Set oRng = Intersect(theFirst.Parent.UsedRange, theFirst)
vFirst = oRng.Value2
Set oRng = Intersect(theSecond.Parent.UsedRange, theSecond)
vSecond = oRng.Value2
'
' get number of rows in the array formula
'
nRows = Application.Caller.Rows.Count
'
' setup result array
'
ReDim vArr(1 To nRows, 1 To 2)
'
' look for numeric0 in First
'
k = 0
For j = 1 To UBound(vFirst)
If IsNumeric(vFirst(j, 1)) Then
If vFirst(j, 1) 0 Then
k = k + 1
If k UBound(vArr) Then Exit For
vArr(k, 1) = vFirst(j, 1)
vArr(k, 2) = vSecond(j, 1)
End If
End If
Next j
'
' output the result as a 2-column n Row array
'
ArtList = vArr
End Function

regards
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"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.
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(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.

Thanks

Array formula to code
 

Sorry for the late reply. Thanks for this function, but it does not seem to
work. I entered the function in amodule, and then in the excel sheet the
function, selecting in the first variable "first" and in the second variable
"second". but a "1" is the result without the item, and when I drag it to the
whole column, all of them are the same. Please help me finish this, I need it
desperately. Thanks.




"Charles Williams" wrote:

Hi Art,

Try this UDF, enter as a single 2-column by n-rows array formula, where n is
the max number of 0 items.
it will be a lot faster than your array formula for a large number of rows:
on my system for 250 rows of data it takes 0.004 seconds as opposed to over
10 seconds for the array formula.

Option Explicit

Function ArtList(theFirst As Range, theSecond As Range) As Variant
Dim vFirst As Variant
Dim vSecond As Variant
Dim oRng As Range
Dim j As Long
Dim k As Long
Dim vArr() As Variant
Dim nRows As Long
'
' get the data, allowing for whole-column refs
'
Set oRng = Intersect(theFirst.Parent.UsedRange, theFirst)
vFirst = oRng.Value2
Set oRng = Intersect(theSecond.Parent.UsedRange, theSecond)
vSecond = oRng.Value2
'
' get number of rows in the array formula
'
nRows = Application.Caller.Rows.Count
'
' setup result array
'
ReDim vArr(1 To nRows, 1 To 2)
'
' look for numeric0 in First
'
k = 0
For j = 1 To UBound(vFirst)
If IsNumeric(vFirst(j, 1)) Then
If vFirst(j, 1) 0 Then
k = k + 1
If k UBound(vArr) Then Exit For
vArr(k, 1) = vFirst(j, 1)
vArr(k, 2) = vSecond(j, 1)
End If
End If
Next j
'
' output the result as a 2-column n Row array
'
ArtList = vArr
End Function

regards
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"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.
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(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.

Thanks

Array formula to code
 

It is a multi-cell array formula, so you have to enter it into ALL the cells
at once

Go to the Visual Basic Editor (F11)
Insert Module
type in the function
go back to Excel and then

First - Select 2 columns wide and n rows deep
Second - type =ArtList(First,Second) in the formula box
Third - press Control-Shift-Enter
Fourth- if necessary press F9 to recalculate

regards
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"art" wrote in message
...
Sorry for the late reply. Thanks for this function, but it does not seem
to
work. I entered the function in amodule, and then in the excel sheet the
function, selecting in the first variable "first" and in the second
variable
"second". but a "1" is the result without the item, and when I drag it to
the
whole column, all of them are the same. Please help me finish this, I need
it
desperately. Thanks.




"Charles Williams" wrote:

Hi Art,

Try this UDF, enter as a single 2-column by n-rows array formula, where n
is
the max number of 0 items.
it will be a lot faster than your array formula for a large number of
rows:
on my system for 250 rows of data it takes 0.004 seconds as opposed to
over
10 seconds for the array formula.

Option Explicit

Function ArtList(theFirst As Range, theSecond As Range) As Variant
Dim vFirst As Variant
Dim vSecond As Variant
Dim oRng As Range
Dim j As Long
Dim k As Long
Dim vArr() As Variant
Dim nRows As Long
'
' get the data, allowing for whole-column refs
'
Set oRng = Intersect(theFirst.Parent.UsedRange, theFirst)
vFirst = oRng.Value2
Set oRng = Intersect(theSecond.Parent.UsedRange, theSecond)
vSecond = oRng.Value2
'
' get number of rows in the array formula
'
nRows = Application.Caller.Rows.Count
'
' setup result array
'
ReDim vArr(1 To nRows, 1 To 2)
'
' look for numeric0 in First
'
k = 0
For j = 1 To UBound(vFirst)
If IsNumeric(vFirst(j, 1)) Then
If vFirst(j, 1) 0 Then
k = k + 1
If k UBound(vArr) Then Exit For
vArr(k, 1) = vFirst(j, 1)
vArr(k, 2) = vSecond(j, 1)
End If
End If
Next j
'
' output the result as a 2-column n Row array
'
ArtList = vArr
End Function

regards
Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com

"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.
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(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.

Thanks