Hi Paul,
Thank you so much for your input.
It worked really well.
You made my day.
Your code is just awesome.
Thanks again.
Regards,
Karthikumar S
On Mon, May 9, 2016 at 8:34 PM, Paul Schreiner <schreiner_p...@att.net>
wrote:
> In Excel2007, Microsoft introduced a "feature" in which, if
> the CPU Processing exceeds some level, the Windows "priority" is reduced.
> presumably to allow the user to continue working without Excel grabbing
> all of the CPU cycles.
>
> What that DOES though, is make Excel use a reduced number of CPU cycles.
>
> Secondly, each time you add a VLookup() function, Excel refreshes all
> PREVIOUS functions on the worksheet.
> That means that when you add the VLookup() function to row 10,001, it
> evaluates row 10,001 and the previous 10,000 rows!
> Which also means that by the time you put the 10,001st value, the first
> row was evaluated 10,001 times!
>
> What I've done in the past, is utilize a Dictionary object (you can look
> it up in VBA help).
> Using this Dictionary, I loop through the Source sheet store the key value
> and lookup value.
> In your example, you're looking up the value in column A, and returning
> the value from B.
>
> So, I'd look through the source file and store the data in the dictionary:
>
> For nRow = 2 To SourceLastRow
> If (Not Dict_Source.exists(sourceSheet.Cells(nRow, "A").Value))
> Then
> Dict_Source.Add sourceSheet.Cells(nRow, "A").Value,
> sourceSheet.Cells(nRow, "B").Value
> End If
> Next nRow
>
> Then, I'd look through the output file and retrieve the matching data from
> the Dictionary:
>
> With outputSheet
> For nRow = 2 To OutputLastRow
> If (Dict_Source.exists(.Cells(nRow, "A").Value)) Then
> .Cells(nRow, "B").Value = Dict_Source.Item(.Cells(nRow,
> "A").Value)
> End If
> Next nRow
> End With
>
> I've done this with a worksheet containing 160,000 rows and 72 columns.
> Storing values into 34 separate dictionaries, then use these Dictionary
> objects to generate a summary report.
>
> The summary report takes about 90 seconds to process all 160,000 records.
>
> *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*
> -----------------------------------------
>
>
> On Thursday, May 5, 2016 4:18 AM, karthik kumar <karthikumar2...@gmail.com>
> wrote:
>
>
>
> Dear All,
>
> I need to reconcile the two sheets having more than 1,50,000 rows. I used
> following VBA code. however it is taking more than 30 minutes to give
> output. Sometime it gets stuck when macro running.
>
> Sub MakeFormulas()
> Dim SourceLastRow As Long
> Dim OutputLastRow As Long
> Dim sourceSheet As Worksheet
> Dim outputSheet As Worksheet
>
> 'What are the names of our worksheets?
> Set sourceSheet = Worksheets("Sheet1")
> Set outputSheet = Worksheets("Sheet2")
>
> 'Determine last row of source
> With sourceSheet
> SourceLastRow = .Cells(.Rows.Count, "A").End(xlUp).row
> End With
> With outputSheet
> 'Determine last row in col P
> OutputLastRow = .Cells(.Rows.Count, "A").End(xlUp).row
> 'Apply our formula
> .Range("B2:B" & OutputLastRow).Formula = _
> "=VLOOKUP(A2,'" & sourceSheet.Name & "'!$A$2:$B$" & SourceLastRow
> & ",2,0)"
> .Range("B2:B" & OutputLastRow).Copy
> .Range("B2").PasteSpecial xlPasteValues
> Application.CutCopyMode = False
> End With
> End Sub
>
>
> It will be so helpful if you provide your inputs to cherish the above code.
>
> Please let me know if you need further details.
>
> Thank you,
>
> Regards,
> --
> 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 https://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 a topic in the
> Google Groups "MS EXCEL AND VBA MACROS" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/excel-macros/4XSz-hxIyMU/unsubscribe.
> To unsubscribe from this group and all its topics, 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 https://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 https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
