How to get id's that appear in all x number of lists

[kloner]

hi all.

I'll try my best at explaining this problem. I have 5 seperate queries (which will not be merged for specific reasons). Each list returns a list of ID's (if any are available).

A min of 1 list and up to 5 lists can be returned.

list1 = "1,2,3,4,5,6,7,8,19"
list2 = "1,5,6,7,8,19"
list3 = "1,5,6,7,19"
list4 = "1,3,4,7,15,17,19"
list5 = "1,7,18,19"

What I need to do is somehow retrieve the id's that appear in all lists. For the example above I would require a new list with :

new_list = "1,7,19"

Note : The number of lists could actually be 1,2,3,4 or 5 of them.

Has anyone actually done anything like this before??

I have thought of an idea, maybe this :

1. Count all list with values in it (eg 5 times).
2. Make a big list of all id's from all lists
3. For each id in the smallest list (eg list5) check if that value exists in the new big list 5 times.
4. if it does append to a new unquie id list.
5. and so forth ....

Does anyone out there have an suggestions about this??

Any ideas would be much appreciated.


kloner

 

[JohnYingling]

Put them all into two arrays parallel arrays, ID and list #, sort the arrays by ID and List# then go through the arrays looking for IDs that have successive list #s from 1 thru 5 and put "winners" in a new array whose initial size is Ubound(All) \ 5.

http://www.VBCompare.com

Compare Code (Text)

http://www.VBSortGen.com

Generate Sort in VB or VBScript

 

[kloner]

Do you mind write a quick piece of pesudocode so I can get the logic correct. I really appreciate your help.

Regards,
kloner

 

[JohnYingling]

arrayID="1,2,3,4,5,6,7,8,19,1,5,6,7,8,19,1,5,6,7,19
arrayLN="1,1,1,1,1,1,1,1, 1,2,2,2,2,2, 2,3,3,3,3, 3
Sort
arrayID="1,1,1,2,3,4,5,5,5,6,6,6,7,7,7,8,8,19,19,19"
arrayLN="1,2,3,1,1,1,1,2,3,1,2,3,1,2,3,1,2, 1, 2, 3"
             X           X     X     X            X   
Output
arrayEQ="1,5,6,7,19"

http://www.VBCompare.com

Compare Code (Text)

http://www.VBSortGen.com

Generate Sort in VB or VBScript