Creating one list out of three (just the values present on all three lists)

[indesignkat]

I have 3 lists of 6 digit numbers. The lists range in length from ~50,000 to about ~80,000. I want to make one list that includes only the 6 digit numbers that are present in all three lists. I don't want to use VBA unless it's a considerable speed advantage as I need to be able to explain how to do it to people that aren't very familiar with excel.


I'm doing it now by putting the three lists on one excel sheet (A1:C80000), then generating a list of just the unique values, then using countif to see how many times each unique value appears in that range. The problem with this is that it takes a very long time for excel to do the counting. It'll work for me today, but I'll need to do this again periodically so I'm looking for a faster method.


any ideas?

 

[Montrey]

You could use advanced filter. It would work perfectly for your situation.


Anything else I'm afraid will take a very long time to process

 

[Krishnakumar]

Hi


Try this code.

[pre]

Sub kTest()

Dim d1  As Object, d2   As Object
Dim i   As Long, k, c   As Long

k = Range("a2:c80000").Value2   'adjust the range

Set d1 = CreateObject("scripting.dictionary")
Set d2 = CreateObject("scripting.dictionary")

For c = 1 To UBound(k, 2)
Select Case c
Case 1
For i = 1 To UBound(k, 1)
If Len(k(i, c)) = 6 Then d1.Item(k(i, c)) = Empty
Next
Case 2
For i = 1 To UBound(k, 1)
If d1.exists(k(i, c)) Then d2.Item(k(i, c)) = Empty
Next
Case 3
d1.RemoveAll
For i = 1 To UBound(k, 1)
If d2.exists(k(i, c)) Then d1.Item(k(i, c)) = Empty
Next
End Select
Next

With Range("e2")    'adjust the range
.Resize(d1.Count) = Application.Transpose(d1.keys)
End With

End Sub

[/pre]

Kris

 

[indesignkat]

Kris,


That worked brilliantly. Thank you!