CommonValues ( ListA ; ListB ; Match )

[JamesBand]

Name & Parameters: [color:red][big] CommonValues ( ListA ; ListB ; Match ) [/big]

Description: CommonValues will take 2 value lists and return a list of values that they have in common.

Sample Input:

VetList = "birds¶cats¶dogs¶pigs"

ClientList = "cats¶horses¶pigs"

Results:

CommonValues(VetList;ClientList;"") = "cats¶pigs"

*** the empty quotes are necessary and should always be empty ***

*** you should use strict validation and value lists for reliable results ***

Recursive: Yes

Formula:

/* If ListA has at least 1 member and ListB has at least 1 member */

If( ValueCount(ListA) ≥ 1 and ValueCount(ListB) ≥ 1;

	/* Then - Loop back through passing ListA, ListB Without the Last Value, Current Matches along with the Last Value of ListB (if it matches) */

	CommonValues( ListA; LeftValues ( ListB; ValueCount(ListB) - 1 ); Match & If(PatternCount("¶"&ListA&"¶";"¶"&RightValues(ListB;1))≠0;RightValues(ListB;1);"") );

	/* Else Return the Match*/

	Left(Match;Length(Match)-1)

) 

Required Functions:

Author(s) jamesband

Date: 06/21/07

Credits:

Disclaimer:

FM Forums does not endorse or warrantee these files are fit for any particular purpose. Do not post or distribute files without written approval from the copyright owner. All files are deemed public domain unless otherwise indictated. Please backup every file that you intend to modify.

Edited June 21, 2007 by Guest

[Raybaudi]

Hi

FilterValues ( ListA ; ListB ) returns the same result ;)

[JamesBand]

Nice... I'm so sad now... ;)

[Raybaudi]

You do not !

Try to search what those lists have NOT in common

[fabriceN]

And here comes FilterValuesByTest ;)

[JamesBand]

So, I guess that:

CommonValues( ListA; LeftValues ( ListB; ValueCount(ListB) - 1 ); Match & If(PatternCount("¶"&ListA&"¶";"¶"&RightValues(ListB;1))≠0;RightValues(ListB;1);"") );

would become:

CommonValues( ListA; LeftValues ( ListB; ValueCount(ListB) - 1 ); Match & If(PatternCount("¶"&ListA&"¶";"¶"&RightValues(ListB;1))≠0;"";RightValues(ListB;1)) );

[Lee Smith]

Hi James,

Before you go through a lot of work, check out the different CFs at Brian Dunnings site. There are several that compare one list against another.

HTH

Lee

[comment]

You may find this thread interesting:

http://www.fmforums.com/forum/showtopic.php?tid/187248/

[fabriceN]

Maybe a little more explanation on FilterValuesByTest it allows you to filter a list by including or excluding items based on... what you want.

In Daniele's case, you want to keep the ones that are not in list B.

so you write :

FilterValuesByTest ( listA ; "value" ; "isempty ( filterValues ( listB ; value ))" ; 1 )

where "isempty ( filterValues ( listB ; value ))" is the criteria you want to test.

[JamesBand]

Hi

FilterValues ( ListA ; ListB ) returns the same result :

Actually... I just tried the FilterValues on my Lists and it didn't work... The List Returned was not only inaccurate, but often Missing parts of the beginnings or ends of entries. Where as my Custom Calc works.

For the moment I need a Calc that will take 2 Value Lists and return the Entries they have in Common and my Calc does that and doesn't seem sluggish, unless I use it in portal with more than a 20 or so records.

:(

[comment]

Makes sure you are patched up to the latest version. If the problem still persists, post a file showing this. There's nothing that can be said until we see what you actually did.