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

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

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

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

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 numeric>0 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" <(E-Mail Removed)> wrote in message
news:30025831-9C7E-42A9-AED9-(E-Mail Removed)...
> 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
>

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 numeric>0 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" <(E-Mail Removed)> wrote in message
> news:30025831-9C7E-42A9-AED9-(E-Mail Removed)...
> > 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
> >
>
>
>

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" <(E-Mail Removed)> wrote in message
news:BECA8A0F-84CE-4B15-AB7C-(E-Mail Removed)...
> 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 numeric>0 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" <(E-Mail Removed)> wrote in message
>> news:30025831-9C7E-42A9-AED9-(E-Mail Removed)...
>> > 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
>> >
>>
>>
>>