Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array formula: how to join 2 ranges together to form one array? | Excel Worksheet Functions | |||
Find specific value in array of array formula | Excel Worksheet Functions | |||
Evalute Array Formula in VBA code | Excel Programming | |||
Tricky array formula issue - Using array formula on one cell, then autofilling down a range | Excel Programming | |||
Code for Array formula | Excel Programming |