I DID post it, within the attachment, as well as describing the technique.
Each case is unique, the functions themselves don't do much without the
workbook to provide context.
But I can explain the technique in more detail:
In this case for example, he wanted to collect all "Activity codes" related to
a specific key field.He also wanted to collect the "Activity Descriptions" and
"Amounts"
I created three variables to represent Dictionary Objects:
Public Dict_Activity
Public Dict_Desc
Public Dict_Amt
By making them "public", they remain in memory while the workbook is open (or
unless cleared manually, or an error is encountered)
I created a function: Function Load_Dict_Lookup()to define and load the
Dictionary Objects and within this function, defined the Dictionary Objects:
Set Dict_Activity = CreateObject("Scripting.Dictionary")
I then looped through the entire data set ONCE and loaded the Dictionary
objects.
I use these Dictionary objects a LOT with my large data sets.
Basically, from a Database perspective, what you are doing is creating a "key"
for the data set.
I can loop through 100,000 records quickly and store the keys.
Once stored, I can search for a key almost IMMEDIATELY.(less than one second)
In one application, I once loaded the Dictionary object with the keys and a
delimited list of Excel ROWS that contained the keys.
Then, I could immediately extract the list of rows and pull the necessary
information from those rows.
In my scripting, prior to doing a lookup, I check to see if the Dict. Obj. has
records.If it does not, or insufficient numbers, I reload the Dict. Obj.If it
does, I look up the Key and return the values.
Note:Loading the Dictionary Object is the equivalent of storing the lookup
values of EVERY unique record/Document number.Then, the Lookup function is
simply returning a single record Number.
If instead, he had wanted to create a "summary" of ALL document numbers, he
would simply have to create an array of the Dictionary "Keys" kArray =
Dict_Activity.keys
and loop through the array and report each document/record.
I hope this helps.
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: Mandeep Baluja <[email protected]>
To: MS EXCEL AND VBA MACROS <[email protected]>
Cc: [email protected]; [email protected]
Sent: Friday, November 6, 2015 2:23 AM
Subject: Re: $$Excel-Macros$$ Help with Custom Vlookup
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 <[email protected]> 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 <[email protected]>
To: Paul Schreiner <[email protected]>
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 <[email protected]> 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 <[email protected]>
To: MS EXCEL AND VBA MACROS <[email protected] >
Cc: Paul Schreiner <[email protected]>
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...@ googlegroups.com.
To post to this group, send email to [email protected].
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.
To post to this group, send email to [email protected].
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 [email protected].
To post to this group, send email to [email protected].
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
