help with List !!!!!
Posted by Paul Johnson on February 03, 2002 9:17 AM
I have three lists that I need to compare.
List 1 is the master list
List 2 is a subset of List 1
List 3 is a subset of one and may be contained in List 2
With VBA (list is too big for VLookups), I would like to select a cell in List 1, search List 2 to find the value from List 1, and search List 3 to find the value from List 1
If found the value is found in List 2 - copy the data in List 2 to the right of the data in List 1.
If not found - return value "No data"
If found the value is found in List 3 copy the data in List 3 to the right of the data in List 2.
If not found - return value "No data"
I know this would be some type of loop procedure, but my experience with loops is minimal. Your help is appreciated.
Posted by Jack in the UK on February 03, 2002 10:16 AM
Hi Paul--
Think you have posted this a few times? Ill check your reponces.
Confused. list is too big for VLookups .. Why Excel will allow 65536 OK if good vlookup formula. I ODBC every day and get by ok on 60,000 plus .. intresting that, please explain.
Whats going wrong?
Rdgs
Posted by paul on February 03, 2002 11:18 AM
Unfortunately, I had no responses
There is so much data the Vlookup takes hours to calc on a 800mhz pc. I was hoping for VBA to shorten the time.
Posted by Proteus on February 03, 2002 3:44 PM
A macro to do what you want depends upon how your data is set up, and you haven't described it very well.
Check whether this does it (I'm just guessing re your worksheet format) :-
Sub FindIt()
Dim cell As Range, Find2 As Range, Find3 As Range
Dim LR2 As Long, LR3 As Long
LR2 = Range("List2")(1, 1).End(xlDown).Row
LR3 = Range("List3")(1, 1).End(xlDown).Row
For Each cell In Range("List1")
Set Find2 = Range("List2").Find(What:=cell.Value, After:=Cells(LR2, Range("List2").Column), LookIn:=xlValues, LookAt:=xlWhole)
If Find2 Is Nothing Then
cell.Offset(0, 1).Value = "No Data"
Else
cell.Offset(0, 1).Value = Find2
End If
Set Find3 = Range("List3").Find(What:=cell.Value, After:=Cells(LR2, Range("List3").Column), LookIn:=xlValues, LookAt:=xlWhole)
If Find3 Is Nothing Then
Cells(cell.Row, Range("List2").Column + 1).Value = "No Data"
Else
Cells(cell.Row, Range("List2").Column + 1).Value = Find3
End If
Next
End Sub
You said "If found the value is found in List 3 copy the data in List 3 to the right of the data in List 2".
Should this be on the same row as the List1 value, or the same row as the List3 value ?
I have assumed it should be on the same row as the List1 value.
