UDF to avoid huge VLOOKUP erea

**yoniman** · 01-10-2011, 07:48 PM

Hi everyone
long time since visited but I know that one of u have a solution for me. it has always been so.

I have a list of docs (up to 2500) which are distributed to some of 250 people. I need to follow-up on that.
trying to make it "easy" I have first sheet of distribution list numbered 1-250.

A_____ B
# _____ name
1 _____ Joe D
2 _____ Arik D
3 ______ Max C

the second sheet is the issue:
this is the input sheet: a list of the docs name in column A and the user will enter numbers (1-250) in the different cell in the proper rows, each representing a person (thus to eliminate typos). I wish to present the person's name correlating the number.

A_________B ______C
Doc-001 __ 1 _______3
________ Joe D __ Max C
Doc-002

to translate the Num into the name I tried Vlookup, Index, Match, Drop lists etc. on such a 2500X250 array I get a >60MB file

It needs to show the name online - running a MACRO at the end doesn't have the effect I need.

My idea (which I have no clue how to execute) is to define a user function in the EVEN B ranges, simply converting the number from the ODD B ranges to a name only if it is NOT ISEMPTY and then writing the same function to the EVEN C cell and so on. .
this way only required matching function will be used.

Please Login or Register  to view this content.

ideas?!?

**MarvinP** · 01-10-2011, 09:25 PM

Hi yoniman,

Why didn't vlookup work? See the attached and tell me why this isn't a good way to do it?

**yoniman** · 01-11-2011, 03:16 AM

Hi MarvinP
thx for taking interest so quickly.

if I fill the 2500*250 array with Vlookup commands the file turns so heavy it is no longer practical - over 80 MB empty (i.e without the input data) and that it even before I finish all the rest of the functionsI wish to add it.

**romperstomper** · 01-11-2011, 03:51 AM

Can't you provide a dropdown list of names rather than using numbers? Having that many formulas of any sort will increase file size and calc time (though a straight INDEX formula sounds better)

**yoniman** · 01-11-2011, 06:18 AM

tried that. for a much smaller array (I took 500X200 just for an idea) I get to 20MB.
will not work. also - drop down of 250 name is far from effective or convenient.

**watersev** · 01-11-2011, 06:20 AM

can you post a sample workbook with data before and after?

**romperstomper** · 01-11-2011, 06:21 AM

Nor is a 60MB file that calculates like a snail though!
If you used a userform for the entry, you could have the name list match as the user typed.

**yoniman** · 01-11-2011, 08:15 AM

gladly - pls find below.
Naturally it will be much more complexed later on (including auto index and such) but this is the general idea. in my current version (not attached) I also hold 'Employees list' copy on the 'Input' sheet to make it a little faster.
I return to my original idea - instead of having 625,000 formula filled cells of which some 10,000 cells will be in use I wish to create a user defined formula resulting in the required+2500 cells only. I think it can be a beautiful thing if possible (sorry for being humble and recall I have no idea how to do this

)

**romperstomper** · 01-11-2011, 08:42 AM

I still don't understand what you want. If you want a formula to cover all 625,000 cells, you will have to enter it into all those cells, whether it's a native formula or a UDF, and that is going to increase the size of your workbook and slow your calculations. (as a general rule of thumb VBA will be slower than native functions)

**snb** · 01-11-2011, 09:05 AM

You would be better off using a validation list.

**yoniman** · 01-11-2011, 10:10 AM

true! I'll try to explain:
let us take a single row as an example.
The cell for data entry is B2 and I wish the name I show on B3.
what I would like is to have the UDF in cell B3 only (i.e. cells C3-->IU3 are empty).

once data is recorded into cell B2 2 things will happen: the appropriate name from the list will appear in B3 and the formula will "duplicate itself" into cell C3.
I wish to find a way to enter functions into destination cell without using a MACRO.

something like:

=if(ISEMPTY(B2)=false, AND( MATCH(B2, , ), C3="=if(ISEMPTY(C2)=false, AND( MATCH(C2, , ), D3="=....")),"")

the only way I can think of is UDF because writing this formula is an endless loop

**romperstomper** · 01-11-2011, 10:12 AM

You can't do that. A UDF cannot modify other cells, it can merely return a value to the cell it is in.

**snb** · 01-11-2011, 10:28 AM

cfr the attachment

**yoniman** · 01-11-2011, 10:39 AM

this is r actually the first time I find something I cannot find a way to do in Excel

**yoniman** · 01-11-2011, 10:43 AM

tnx - but my actual list is 250 people and 2500 docs.
too heavy and inefficient (select from a 250 name list is not easy)
I think I'll try this with an auto complete function....

UDF to avoid huge VLOOKUP erea

LinkBack

Thread Tools

Rate This Thread

Display

UDF to avoid huge VLOOKUP erea

Re: UDF to avoid huge VLOOKUP erea

Re: UDF to avoid huge VLOOKUP erea

Re: UDF to avoid huge VLOOKUP erea

Re: UDF to avoid huge VLOOKUP erea

Re: UDF to avoid huge VLOOKUP erea

Re: UDF to avoid huge VLOOKUP erea

Re: UDF to avoid huge VLOOKUP erea

Re: UDF to avoid huge VLOOKUP erea

Re: UDF to avoid huge VLOOKUP erea

Re: UDF to avoid huge VLOOKUP erea

Re: UDF to avoid huge VLOOKUP erea

Re: UDF to avoid huge VLOOKUP erea

Re: UDF to avoid huge VLOOKUP erea

Re: UDF to avoid huge VLOOKUP erea

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions