The speed of the worksheet is due to the fact that this UDF would execute for
EACH row/cell where you've placed it.and update each time a sheet
calculates(which occurs whenever you change a value)
This function, run 50,000 times would indeed be slow!
I'd suggest creating an alternative.
Can I get a sample file (sent directly to me)?
Even if you need to change some names to remove personal data.
I routinely have macros load Public "dictionary" objects that are used to
contain keywords that can then be located quickly.
a couple of my workbooks contain over 100,000 rows of 70+ columns.So I often
have to manage large amounts of data.
If you can give me a sample, I'd be glad to take a look at it.
Paul-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
From: Abhishek Jain <abhishek....@gmail.com>
To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com>
Cc: Paul Schreiner <schreiner_p...@att.net>
Sent: Tuesday, November 3, 2015 2:56 AM
Subject: $$Excel-Macros$$ Help with Custom Vlookup
Hi All,
I intend to use this piece of UDF I found on internet for my requirement -
Function MYVLOOKUP(lookupval, lookuprange As Range, indexcol As Long)Dim r As
RangeDim result As Stringresult = ""For Each r In lookuprange If r =
lookupval Then result = result & " " & r.Offset(0, indexcol - 1) End
IfNext rMYVLOOKUP = resultEnd Function
Formula =MYVLOOKUP(lookup_value,lookup_range,return_column_no.)
It does join all the matches of the lookup in one cell (results separated by a
space) and this fulfils my purpose.
The problem is, it is terribly slow. I couldn't manage to get results for a
50000 rows data with around 11000 lookup values despite waiting of 2-3 hours
and eventually had to raise hands in despair.
Any ideas to (considerably) speed it up??
PS: My data is in Table format and changing its format doesn't change much. I
can provide a sample file, if need be.
Many thanks in advance.
Best regards,
Abhishek--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
