I have previously dealt with similar situations.
In one case, I have an application that has over 164,000 rows and 74
columns.Frequently, queries and reports retrieve records based on multiple
columns.
VLOOKUP became much too time consuming, but due to the volatile nature of the
data, utilizing a database like Access or Oracle wasn't practical.(although I
do upload the data to an Oracle database for other purposes).
I like to utilize the VBA Dictionary Object.I loop through the data one time
and load multiple Dictionaries.sometimes using multiple field values to define
the "index" for the dictionary.(which is how I accomplish matching multiple
columns for a vlookup)
By declaring the object as "Public", the Dictionary is available for the
duration of the Excel session and doesn't have to be re-loaded.
It goes something like this:Public Dict_Material
Sub Load_Dict dim nRow, nRows Set Dict_Material =
CreateObject("Scripting.Dictionary") nRows =
application.worksheetfunction.counta(Sheets("Data").Range("A:A")) for nRow
= 2 to nRows if (not
Dict_Material.exists(Sheets("Data").Cells(nRow,"F").Value)) then
dict_Material.Add Sheets("Data").Cells(nRow,"F").Value, nRow else
dict_Material.item(Sheets("Data").Cells(nRow,"F").Value) = _
dict_Material.item(Sheets("Data").Cells(nRow,"F").Value) & "," & nRow end
if next nRowEnd Sub
Now, Dict_Material contains a comma-separated list of all rows that contain
each value from column "F"so, to look up all records for a specific value, I
use something like:
SearchMatl = "12345"if (dict_Material.exists(SearchMatl)) then dArray =
split(dict_Material.item(SearchMatl),",") FirstRow = darray(0)else msgbox
"Material " & SearchMatl & " Not Found"end
ifPaul-----------------------------------------
“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 Sunday, June 11, 2017 4:41 AM, karthik N <n.karthi...@gmail.com> wrote:
Hi Friends,
We have more than 3 lacks data in our excel,so put the vlookup formula it will
take huge time.
Please help as the fastest vlookup formula or VBA
RegardsKarthik.N
| | Virus-free. www.avast.com |
--
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 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.
