Hi Paul, Kindly post the answer for this Query So That we can learn that trick too.
On Thursday, November 5, 2015 at 10:33:47 PM UTC+5:30, Abhi wrote: > > Paul, > > You are a real genius. > > For around 42000 rows and 15000 look up values,* it took less than a > minute.* > > Can't be better than that! > > Thank you so much. > > Best regards, > > Abhishek > > > On Thu, Nov 5, 2015 at 9:53 PM, Paul Schreiner <schrein...@att.net > <javascript:>> wrote: > >> Here's my solution. >> >> It'll be interesting to see how it runs on a large data set! >> >> Here's the technique: >> >> If the Dictionary object is not defined, or has less than 10 entries, it >> loads the table into the dictionaries. >> >> So, for the FIRST iteration, the data is loaded. >> >> For each subsequent lookup, the dictionaries are used. >> >> let me know how it works for you! >> >> *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 <abhish...@gmail.com <javascript:>> >> *To:* Paul Schreiner <schrein...@att.net <javascript:>> >> *Sent:* Thursday, November 5, 2015 7:25 AM >> *Subject:* Re: $$Excel-Macros$$ Help with Custom Vlookup >> >> Thanks Paul for your generous reply. >> >> I have attached herewith a sample file. >> >> Best regards, >> >> Abhishek >> >> >> >> On Thu, Nov 5, 2015 at 5:44 PM, Paul Schreiner <schrein...@att.net >> <javascript:>> wrote: >> >> 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 <abhish...@gmail.com <javascript:>> >> *To:* MS EXCEL AND VBA MACROS <excel-...@googlegroups.com <javascript:>> >> *Cc:* Paul Schreiner <schrein...@att.net <javascript:>> >> *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 Range >> Dim result As String >> result = "" >> For Each r In lookuprange >> If r = lookupval Then >> result = result & " " & r.Offset(0, indexcol - 1) >> End If >> Next r >> MYVLOOKUP = result >> End 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...@googlegroups.com <javascript:>. >> To post to this group, send email to excel-...@googlegroups.com >> <javascript:>. >> 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...@googlegroups.com <javascript:>. >> To post to this group, send email to excel-...@googlegroups.com >> <javascript:>. >> 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.
