User Defined Function

[Rutgers_Excels]

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

 

[Frank Kabel]

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",ROW(A1:A1000)),number_
occurence))

--
Regards
Frank Kabel
Frankfurt, Germany

"Rutgers_Excels >" <<[email protected]>
schrieb im Newsbeitrag
news:[email protected]...

 

[Tom Ogilvy]

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.

 

[Rutgers_Excels]

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

 

[Frank Kabel]

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 >" <<[email protected]>
schrieb im Newsbeitrag
news:[email protected]...

 

[Rutgers_Excels]

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