+ Reply to Thread
Results 1 to 19 of 19

Array Functions from Alan Beban

  1. #1
    Josh O.
    Guest

    Array Functions from Alan Beban

    I need so quick assistance. I downloaded the functions in the freely
    downloadable file at http://home.pacbell.net. But I don't know how to make
    them available to use. Any help?


  2. #2
    Josh O.
    Guest

    RE: Array Functions from Alan Beban





    I need some quick assistance. I downloaded the functions in the freely
    downloadable file at http://home.pacbell.net/beban referred to in another
    post, but I don't know how to make them available to use. Any help? I
    believe they could be macros, but I don't know. Alan had made reference to
    making them available in Personal.xls. Again I am not familar with this
    aspect of excel


  3. #3
    Peo Sjoblom
    Guest

    Re: Array Functions from Alan Beban

    http://www.mvps.org/dmcritchie/excel/install.htm


    --

    Regards,

    Peo Sjoblom



    "Josh O." <[email protected]> wrote in message
    news:[email protected]...
    >
    >
    >
    >
    > I need some quick assistance. I downloaded the functions in the freely
    > downloadable file at http://home.pacbell.net/beban referred to in another
    > post, but I don't know how to make them available to use. Any help? I
    > believe they could be macros, but I don't know. Alan had made reference

    to
    > making them available in Personal.xls. Again I am not familar with this
    > aspect of excel
    >




  4. #4
    Josh O.
    Guest

    Re: Array Functions from Alan Beban

    I appreciate the link. Unfortunately, I don't know enough about macros to
    know what I am looking at. The file I download is a spreadsheet with
    explanation of functions that are included in the download, but I don't know
    how to access them or make them available.

    "Peo Sjoblom" wrote:

    > http://www.mvps.org/dmcritchie/excel/install.htm
    >
    >
    > --
    >
    > Regards,
    >
    > Peo Sjoblom
    >
    >
    >
    > "Josh O." <[email protected]> wrote in message
    > news:[email protected]...
    > >
    > >
    > >
    > >
    > > I need some quick assistance. I downloaded the functions in the freely
    > > downloadable file at http://home.pacbell.net/beban referred to in another
    > > post, but I don't know how to make them available to use. Any help? I
    > > believe they could be macros, but I don't know. Alan had made reference

    > to
    > > making them available in Personal.xls. Again I am not familar with this
    > > aspect of excel
    > >

    >
    >
    >


  5. #5
    Dave R.
    Guest

    Re: Array Functions from Alan Beban

    press ALT-F11 within that spreadsheet. Select all the code in there (which
    are user defined functions) and copy it. Now go to the link provided and
    proceed from there.


    "Josh O." <[email protected]> wrote in message
    news:[email protected]...
    > I appreciate the link. Unfortunately, I don't know enough about macros to
    > know what I am looking at. The file I download is a spreadsheet with
    > explanation of functions that are included in the download, but I don't

    know
    > how to access them or make them available.
    >
    > "Peo Sjoblom" wrote:
    >
    > > http://www.mvps.org/dmcritchie/excel/install.htm
    > >
    > >
    > > --
    > >
    > > Regards,
    > >
    > > Peo Sjoblom
    > >
    > >
    > >
    > > "Josh O." <[email protected]> wrote in message
    > > news:[email protected]...
    > > >
    > > >
    > > >
    > > >
    > > > I need some quick assistance. I downloaded the functions in the

    freely
    > > > downloadable file at http://home.pacbell.net/beban referred to in

    another
    > > > post, but I don't know how to make them available to use. Any help?

    I
    > > > believe they could be macros, but I don't know. Alan had made

    reference
    > > to
    > > > making them available in Personal.xls. Again I am not familar with

    this
    > > > aspect of excel
    > > >

    > >
    > >
    > >




  6. #6
    Josh O.
    Guest

    Re: Array Functions from Alan Beban

    I have the functions installed. One of the functions is "vlookups," it is
    supposed to do the same as vlookup, but return multiple values from the same
    lookup value. For example, if customer 1 appeared twice in a list, the
    fuction should return the both of the result values.

    However when I copy the formula, it displays the same value as the first
    occurance. Has anyone had any experience using this type of function.

    "Dave R." wrote:

    > press ALT-F11 within that spreadsheet. Select all the code in there (which
    > are user defined functions) and copy it. Now go to the link provided and
    > proceed from there.
    >
    >
    > "Josh O." <[email protected]> wrote in message
    > news:[email protected]...
    > > I appreciate the link. Unfortunately, I don't know enough about macros to
    > > know what I am looking at. The file I download is a spreadsheet with
    > > explanation of functions that are included in the download, but I don't

    > know
    > > how to access them or make them available.
    > >
    > > "Peo Sjoblom" wrote:
    > >
    > > > http://www.mvps.org/dmcritchie/excel/install.htm
    > > >
    > > >
    > > > --
    > > >
    > > > Regards,
    > > >
    > > > Peo Sjoblom
    > > >
    > > >
    > > >
    > > > "Josh O." <[email protected]> wrote in message
    > > > news:[email protected]...
    > > > >
    > > > >
    > > > >
    > > > >
    > > > > I need some quick assistance. I downloaded the functions in the

    > freely
    > > > > downloadable file at http://home.pacbell.net/beban referred to in

    > another
    > > > > post, but I don't know how to make them available to use. Any help?

    > I
    > > > > believe they could be macros, but I don't know. Alan had made

    > reference
    > > > to
    > > > > making them available in Personal.xls. Again I am not familar with

    > this
    > > > > aspect of excel
    > > > >
    > > >
    > > >
    > > >

    >
    >
    >


  7. #7
    Harlan Grove
    Guest

    Re: Array Functions from Alan Beban

    Josh O. wrote...
    >I have the functions installed. One of the functions is "vlookups,"

    it is
    >supposed to do the same as vlookup, but return multiple values from

    the same
    >lookup value. For example, if customer 1 appeared twice in a list,

    the
    >fuction should return the both of the result values.
    >
    >However when I copy the formula, it displays the same value as the

    first
    >occurance. Has anyone had any experience using this type of function.

    ....

    If you're going to use Alan Beban's array function library, you're
    going to have to read Excel's online help topics on array formulas.
    Generally, you don't enter array formulas in one cell at a time and
    copy them to other cells. You select several cells to store your
    result, then 'array enter' the formula. That means holding down [Ctrl]
    and [Shift] keys before pressing [Enter].


  8. #8
    Josh O.
    Guest

    Re: Array Functions from Alan Beban

    Thanks Harlan. I figured it was something easy.

    Is there a way to pull a list of numbers from another worksheet. What I
    need to do is pull all the customer numbers from a csv file and then lookup
    the remaining info from the csv file. But the customer numbers that appear
    in the csv file constanly change. Can I pull that data from the csv file?

    "Harlan Grove" wrote:

    > Josh O. wrote...
    > >I have the functions installed. One of the functions is "vlookups,"

    > it is
    > >supposed to do the same as vlookup, but return multiple values from

    > the same
    > >lookup value. For example, if customer 1 appeared twice in a list,

    > the
    > >fuction should return the both of the result values.
    > >
    > >However when I copy the formula, it displays the same value as the

    > first
    > >occurance. Has anyone had any experience using this type of function.

    > ....
    >
    > If you're going to use Alan Beban's array function library, you're
    > going to have to read Excel's online help topics on array formulas.
    > Generally, you don't enter array formulas in one cell at a time and
    > copy them to other cells. You select several cells to store your
    > result, then 'array enter' the formula. That means holding down [Ctrl]
    > and [Shift] keys before pressing [Enter].
    >
    >


  9. #9
    Harlan Grove
    Guest

    Re: Array Functions from Alan Beban

    Josh O. wrote...
    >Is there a way to pull a list of numbers from another worksheet. What

    I
    >need to do is pull all the customer numbers from a csv file and then

    lookup
    >the remaining info from the csv file. But the customer numbers that

    appear
    >in the csv file constanly change. Can I pull that data from the csv

    file?
    ....

    Meaning you need to pull customer numbers from different versions of
    the CSV file periodically? If so, are the CSV files always in the same
    layout except perhaps for the number of rows? Are you trying to pull
    distinct customer numbers then all detail information for each customer
    number? If so, then you'd be MUCH better off just sorting the CSV by
    customer number, then adding the following formulas in the column
    immediately to the right of your data (I'm assuming customer number is
    in column A, and the first column to the right of your data is column
    Y).

    Y2:
    =A2=A1

    With cell Y2 active, double click on the little square box at the
    bottom right corner of the border around cell Y2 (the little square box
    is called the fill handle). This should fill your formula down in col Y
    into all rows with data in col X. These formulas will evaluate FALSE
    for all rows in which the customer number already appears.

    Select the entire data range including the added column (Y in my
    example) and run Data > Filter > AutoFilter. This should put drop-down
    arrows in each cell in row 1 in each column of the selected range.
    Click on the drop-down arrow In column Y (the column with the formulas)
    and select FALSE from the drop-down list. This should filter only the
    rows in which the customer number has already appeared in preceding
    rows. With this filter active, move to A2, hold down [Ctrl] and [Shift]
    keys and press the down arrow. This should select all cells in col A
    that contain second and subsequent instances of each customer number.
    Press [Delete] to clear the customer numbers from these cells. Then run
    Data > Filter > AutoFilter again to clear the filter. Clear col Y.

    This should have left single instances of each customer number in col A
    with all detail data for that customer in the subsequent columns on and
    below the row containing the customer number in col A. Save as an XLS
    file.

    If you're doing something more complicated than this with your data
    extract, provide details. There's almost certainly a better way to do
    what you need to do than using a great many VLOOKUPS calls.


  10. #10
    Josh O.
    Guest

    Re: Array Functions from Alan Beban

    Kind of. I have a csv file that is pulled from a database. The final goal
    of all of this is to create a usable report. The original CSV is not set up
    to be used in a practical manner. I am trying to pull the customer number,
    invoice number, invoice date, and invoice amount from the CSV file to my
    spreadsheet. I just can't figure a simpler way to do it. One other problem
    is that the invoice amount is in one of 7 different columns (based on the age
    of the invoice). The csv file is updated every time I export it from the
    system.

    I just don't know the best way to accomplish that. I would appreciate any
    ideas that might be simpler, because even at this point the array formula
    cause a few other minor issues.

    "Harlan Grove" wrote:

    > Josh O. wrote...
    > >Is there a way to pull a list of numbers from another worksheet. What

    > I
    > >need to do is pull all the customer numbers from a csv file and then

    > lookup
    > >the remaining info from the csv file. But the customer numbers that

    > appear
    > >in the csv file constanly change. Can I pull that data from the csv

    > file?
    > ....
    >
    > Meaning you need to pull customer numbers from different versions of
    > the CSV file periodically? If so, are the CSV files always in the same
    > layout except perhaps for the number of rows? Are you trying to pull
    > distinct customer numbers then all detail information for each customer
    > number? If so, then you'd be MUCH better off just sorting the CSV by
    > customer number, then adding the following formulas in the column
    > immediately to the right of your data (I'm assuming customer number is
    > in column A, and the first column to the right of your data is column
    > Y).
    >
    > Y2:
    > =A2=A1
    >
    > With cell Y2 active, double click on the little square box at the
    > bottom right corner of the border around cell Y2 (the little square box
    > is called the fill handle). This should fill your formula down in col Y
    > into all rows with data in col X. These formulas will evaluate FALSE
    > for all rows in which the customer number already appears.
    >
    > Select the entire data range including the added column (Y in my
    > example) and run Data > Filter > AutoFilter. This should put drop-down
    > arrows in each cell in row 1 in each column of the selected range.
    > Click on the drop-down arrow In column Y (the column with the formulas)
    > and select FALSE from the drop-down list. This should filter only the
    > rows in which the customer number has already appeared in preceding
    > rows. With this filter active, move to A2, hold down [Ctrl] and [Shift]
    > keys and press the down arrow. This should select all cells in col A
    > that contain second and subsequent instances of each customer number.
    > Press [Delete] to clear the customer numbers from these cells. Then run
    > Data > Filter > AutoFilter again to clear the filter. Clear col Y.
    >
    > This should have left single instances of each customer number in col A
    > with all detail data for that customer in the subsequent columns on and
    > below the row containing the customer number in col A. Save as an XLS
    > file.
    >
    > If you're doing something more complicated than this with your data
    > extract, provide details. There's almost certainly a better way to do
    > what you need to do than using a great many VLOOKUPS calls.
    >
    >


  11. #11
    Josh O.
    Guest

    Re: Array Functions from Alan Beban

    Unfortunately, I use the term database loosely. I have no control over the
    database or the reports that come out of it. It is a very limited system.
    The need is to take the data from the csv file (which I can't alter) and pull
    only the data that I need, as is to my spreadsheet. Getting another csv file
    is not possible.

    The vlookup and the vlookups (from alan) both are close to what I need. The
    vlookup fuction entered as an array will lookup the customer number in each
    row and return the data I need, except when a customer has 2 or more open
    invoices. The vlookups (from alan) function will list each invoice, but will
    only lookup one value (or customer). Manually entering that formula for each
    customer number is too time consuming. I know there is some way to do this,
    I just can't figure out the most efficient way to handle.



  12. #12
    Harlan Grove
    Guest

    Re: Array Functions from Alan Beban

    Josh O. wrote...
    >Kind of. I have a csv file that is pulled from a database. The final

    goal
    >of all of this is to create a usable report. The original CSV is not

    set up
    >to be used in a practical manner. I am trying to pull the customer

    number,
    >invoice number, invoice date, and invoice amount from the CSV file to

    my
    >spreadsheet. I just can't figure a simpler way to do it. One other

    problem
    >is that the invoice amount is in one of 7 different columns (based on

    the age
    >of the invoice). The csv file is updated every time I export it from

    the
    >system.
    >
    >I just don't know the best way to accomplish that. I would appreciate

    any
    >ideas that might be simpler, because even at this point the array

    formula
    >cause a few other minor issues.

    ....

    If this data is in a database and if this report would be a recurring
    task, then the BEST approach would be using the database to generate
    the report and not using Excel AT ALL. Alternatively, if the CSV file
    you're getting isn't easily usable, get a different CSV file.

    Is the desired end result a listing of customers, invoice numbers and
    amounts from invoices outstanding? If you want to display the invoice
    amounts in separate columns depending on age, just select the entire
    original CSV data range and run Data > Subtotals. I think that'd give
    you most of what you need.


  13. #13
    RagDyer
    Guest

    Re: Array Functions from Alan Beban

    There are resident XL functions which can lookup and return multiple values
    for the same criteria.
    If you would care to describe fully, the XL datalist that you end up with
    after importing the CSV values, maybe what you're looking for might be
    attainable, without you having to learn additional XL intricacies.

    If you can describe your sheet and explain what you need as explicitly as
    possible, lets see what can be done.
    --


    Regards,

    RD
    --------------------------------------------------------------------
    Please keep all correspondence within the Group, so all may benefit!
    -------------------------------------------------------------------

    "Josh O." <[email protected]> wrote in message
    news:[email protected]...
    Unfortunately, I use the term database loosely. I have no control over the
    database or the reports that come out of it. It is a very limited system.
    The need is to take the data from the csv file (which I can't alter) and
    pull
    only the data that I need, as is to my spreadsheet. Getting another csv
    file
    is not possible.

    The vlookup and the vlookups (from alan) both are close to what I need. The
    vlookup fuction entered as an array will lookup the customer number in each
    row and return the data I need, except when a customer has 2 or more open
    invoices. The vlookups (from alan) function will list each invoice, but
    will
    only lookup one value (or customer). Manually entering that formula for
    each
    customer number is too time consuming. I know there is some way to do this,
    I just can't figure out the most efficient way to handle.



  14. #14
    Harlan Grove
    Guest

    Re: Array Functions from Alan Beban

    Josh O. wrote...
    ....
    >The vlookup and the vlookups (from alan) both are close to what I

    need. The
    >vlookup fuction entered as an array will lookup the customer number in

    each
    >row and return the data I need, except when a customer has 2 or more

    open
    >invoices. The vlookups (from alan) function will list each invoice,

    but will
    >only lookup one value (or customer). Manually entering that formula

    for each
    >customer number is too time consuming. I know there is some way to do

    this,
    >I just can't figure out the most efficient way to handle.


    I kinda figured this was the case.

    If the CSV file is basically tabular, possibly with blank cells but
    with well-defined columns/fields, then there's no good reason to use
    formulas to extract information. Sort on the customer number column
    then on the invoice number column. Then delete any cruft sorted to the
    top or bottom, then select the remaining data and use Data > Subtotals
    - even if you don't want subtotals. You can then use an autofilter to
    filter blank cells in some column in the subtotal rows and then clear
    those rows, thus creating blank rows between customers. If you don't
    wan multiple instances of the customer number to appear beside each
    invoice record, follow my previous instructions for how to remove
    second and subsequent instances using filters.

    If your data isn't basically tabular, then you need to provide a lot
    more details about what it does look like. VLOOKUPS won't be of much
    help to you in this case. Neither would VLOOKUP, for that matter.


  15. #15
    Registered User
    Join Date
    05-01-2013
    Location
    India
    MS-Off Ver
    Excel 2003, Excel 2010, Excel 2013
    Posts
    3

    Re: Array Functions from Alan Beban

    Hi Experts,

    Alan Beban's site is not accessible at the moment. Kindly provide the link to the new site or the ArrayFunctions file itself please.

    Thanks

  16. #16
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Array Functions from Alan Beban

    Do you realise that the thread you have posted in is 12 years old?

    Pete

  17. #17
    Registered User
    Join Date
    05-01-2013
    Location
    India
    MS-Off Ver
    Excel 2003, Excel 2010, Excel 2013
    Posts
    3

    Re: Array Functions from Alan Beban

    Sure Sir !
    But I had to start from some where. I have also posted in other blogs and forums wherever I could find a reference to above file.

  18. #18
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Array Functions from Alan Beban

    If you have posted the same question on other sites (called cross-posting), you must let us know what they are and im pretty sure you will find you need to let the other sites know where else you have asked this.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Read this to understand why we ask you to do this, and then please edit your post to include links to any and all cross-posts in any other forums (not just this site).
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  19. #19
    Registered User
    Join Date
    05-01-2013
    Location
    India
    MS-Off Ver
    Excel 2003, Excel 2010, Excel 2013
    Posts
    3

    Re: Array Functions from Alan Beban

    Thanks Sir,

    I believe that was harmless. But I understand and apologies if I have broken the rules.

    I have posted the same question at
    http://dailydoseofexcel.com/archives...ray-functions/

    Regads
    Kanwaljit

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1