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 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." <[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
>
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
> >
>
>
>
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
> > >
> >
> >
> >
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
> > > >
> > >
> > >
> > >
>
>
>
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.
>
>
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...
>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.
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.
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.
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
Do you realise that the thread you have posted in is 12 years old?
Pete
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.
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
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks