User Defined Function
 

Is there anyway I can write a user defined function that will do th
following:

Column A has a list of part descriptions
Column B has a list of Manufacturers

I want to write a FUNCTION that will look in col B for a certai
manufacturer and return the part description. I was thinking abou
using something like a =small() function. That is, look in col A fo
the first, second, third,... time the manufacturers name appears an
return the description.

When imputing the function in my worksheet it would read something lik
this:

=Part(Range,Manufacturer's Name, number it appears)

for example:
=Part(A1:A100,"Widget Inc.",5)

would return to me the 5th part number that comes up for the "Widge
Inc." company.

I realize that I can use Macros, Pivot Tables, and Filters to get thi
same information, but it would be a lot easier to write a funtion on
time and not have to worry about updates, etc.

Thanks for any help

P.S. Because of my inexperience in this field, I'm not sure if it ca
even be done

--
Message posted from http://www.ExcelForum.com

User Defined Function
 

Hi
why not use a worksheet function. Try something like the following
array formula (entered with CTRL+SHIFT+ENTER):
=INDEX(B1:B1000,SMALL(IF(A1:A1000="manufacturer",R OW(A1:A1000)),number_
occurence))

--
Regards
Frank Kabel
Frankfurt, Germany

"Rutgers_Excels "
schrieb im Newsbeitrag
...
Is there anyway I can write a user defined function that will do the
following:

Column A has a list of part descriptions
Column B has a list of Manufacturers

I want to write a FUNCTION that will look in col B for a certain
manufacturer and return the part description. I was thinking about
using something like a =small() function. That is, look in col A for
the first, second, third,... time the manufacturers name appears and
return the description.

When imputing the function in my worksheet it would read something
like
this:

=Part(Range,Manufacturer's Name, number it appears)

for example:
=Part(A1:A100,"Widget Inc.",5)

would return to me the 5th part number that comes up for the "Widget
Inc." company.

I realize that I can use Macros, Pivot Tables, and Filters to get
this
same information, but it would be a lot easier to write a funtion one
time and not have to worry about updates, etc.

Thanks for any help

P.S. Because of my inexperience in this field, I'm not sure if it can
even be done.


---
Message posted from http://www.ExcelForum.com/

User Defined Function
 

It can be done with existing functions.

Assume the manufacturer's name is "M1p1"

=OFFSET(A1,SMALL(IF($B$1:$B$20="M1p1",ROW($B$1:$B$ 20)),5)-1,0)

Enter with Ctrl+Shift+Enter rather than just enter since this is an array
formula.

--
Regards,
Tom Ogilvy


"Rutgers_Excels " wrote in
message ...
Is there anyway I can write a user defined function that will do the
following:

Column A has a list of part descriptions
Column B has a list of Manufacturers

I want to write a FUNCTION that will look in col B for a certain
manufacturer and return the part description. I was thinking about
using something like a =small() function. That is, look in col A for
the first, second, third,... time the manufacturers name appears and
return the description.

When imputing the function in my worksheet it would read something like
this:

=Part(Range,Manufacturer's Name, number it appears)

for example:
=Part(A1:A100,"Widget Inc.",5)

would return to me the 5th part number that comes up for the "Widget
Inc." company.

I realize that I can use Macros, Pivot Tables, and Filters to get this
same information, but it would be a lot easier to write a funtion one
time and not have to worry about updates, etc.

Thanks for any help

P.S. Because of my inexperience in this field, I'm not sure if it can
even be done.


---
Message posted from http://www.ExcelForum.com/

User Defined Function
 

Thanks for the tips guys. This was my thought orginally, however,
don't think it is going to be in my best interest to use an arra
funtion. There are several reasons:

1. I read somewhere once that said not too use too many arra
functions in a workbook and this workbook is going to contain a lot o
array funtions if I go this route (This is do too the massive number o
line items in my material matrix. It would wind up being severa
thousand array funtions.)

2. (And most importantly) This sheet is going to be used by people wh
don't know that much about the many aspects of Excel. I thought if
could come up with a function it would be easier than explaining arra
functions to all of them.

If there is a way to do this, I'd appreciate any help. If not, I than
you very much for the ideas

--
Message posted from http://www.ExcelForum.com

User Defined Function
 

Hi
1. if you want to use this kind of function several 1000 times you're
even better with using an array formula as a UDF will probably quite
slower than a worksheet function (due to the additional overhead). So
if this will slow down your spreadsheet an UDf will slow it down even
further

2. Using a UDF will also create problems:
- if the user disables macros (your spreadsheet will show only errors)


--
Regards
Frank Kabel
Frankfurt, Germany

"Rutgers_Excels "
schrieb im Newsbeitrag
...
Thanks for the tips guys. This was my thought orginally, however, I
don't think it is going to be in my best interest to use an array
funtion. There are several reasons:

1. I read somewhere once that said not too use too many array
functions in a workbook and this workbook is going to contain a lot
of
array funtions if I go this route (This is do too the massive number
of
line items in my material matrix. It would wind up being several
thousand array funtions.)

2. (And most importantly) This sheet is going to be used by people
who
don't know that much about the many aspects of Excel. I thought if I
could come up with a function it would be easier than explaining
array
functions to all of them.

If there is a way to do this, I'd appreciate any help. If not, I
thank
you very much for the ideas.


---
Message posted from http://www.ExcelForum.com/

User Defined Function
 

Thanks for the input. I guess I was just trying to cut a fat hog in th
ass. I've decided to try to write arrays and protect the cells wit
hidden formulas. This way they would have to go out of their way t
mess it up

--
Message posted from http://www.ExcelForum.com