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?

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

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


--

Regards,

Peo Sjoblom



"Josh O." wrote in message
...




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

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." wrote in message
...




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

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." wrote in message
...
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." wrote in message
...




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

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." wrote in message
...
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." wrote in message
...




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

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

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

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.

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.

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.

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.

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." wrote in message
...
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.

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.