9 Replies Latest reply on Oct 31, 2016 11:21 AM by lymetyme24

# Remove Value from List (Custom Function)?

Hoping to find a custom function that will search a list for a value and remove a set number of occurrences of that value from the list (not all the occurrences of it).

For example, if current list is:

Apple

Banana

Apple

Apple

Orange

Let's say I want to remove one occurrence of Apple, I would want to be able to diminish the list to just:

Apple

Banana

Apple

Orange

In my mind, the final custom function would look something like:  ListOccurrences ( list ; value ; count )

I started trying to accomplish this through a script and it started to get really long, my approach was to:

- determine how many times the value occurrs in the list (in my example above, "Apple" occurs 3x)

- remove ALL occurrences of the value from the list

- put the value back into the list the desired amount of times (count)

Seems like somewhere between the Substitute function and the custom function AddRemoveListItems (http://www.briandunning.com/cf/1178) there has gotta be a way to do this via Custom Function maybe?  Perhaps you all (smarter than me) can help?

• ###### 1. Re: Remove Value from List (Custom Function)?

ListOccurrences ( TheList ; TheValue ; Occurrences )

Let ( [ SubList = Substitute ( 10^Occurrences - 1 ; "9" ; TheValue  & ¶ ) ;

CleanList = Substitute ( ¶ & TheList & ¶ ; ¶ & TheValue & ¶ ; ¶ )

]

SubList & Middle ( CleanList ; 2 ; Length ( CleanList ) - 2 )

) // Let

I haven't tested this, but it combines two techniques that I've used successfully in the past so it should work unless I missed a detail somewhere.

• ###### 2. Re: Remove Value from List (Custom Function)?

@philmodjunk super close, amazing!  Upon testing, this gets me 90% of the way there - thank you!  two issues (one minor, one major)

(minor)

when the Occurrences value gets down to zero (0) -- this puts the literal value "0" at the beginning of the list, like below

(major)

the occurrence count is off, maybe because all the "Apple" values are not together, they appear in-between other values in the list?

TheList =

Apple

Banana

Apple

Apple

Orange

When evaluated as ListOccurrences ( TheList ; "Apple" ; 0 )

The result is:

0Banana

Apple

Orange

When evaluated as ListOccurrences ( TheList ; "Apple" ; 1 )

The result is:

Apple

Banana

Apple

Orange

Any suggestions?

• ###### 3. Re: Remove Value from List (Custom Function)?

First, I'd check to see if Apple is really "Apple" and not "Apple " or something. that one part of the calculation is intended to strip out all instances of the specified term so I don't see an obvious way that one instance would be left in the lower part of the list.

If I had the time, I'd pull this into a data viewer and test, but I'm literally about to run out my front door for something.

• ###### 4. Re: Remove Value from List (Custom Function)?

You looks deleting last occurrence of "Apple" in first post, do you need keeping order in source list?

Add: How many values are in your list? The technique used is applicable less than (about) 400, since FM can calculate number max of 10^400.

And he seems using occurrence parameter as number of remaining, not removing as you wrote first. So the calculation works in

0 < occurrence <= 400

• ###### 5. Re: Remove Value from List (Custom Function)?

The function as written actually removes all instances of the specified value and then puts in the specified number. I agree that this isn't what you originally specified now that I reread your original post. But my testing shows that for values > 0, it does work as I intended it to. I can't get the results that you show where one instance of the value remains in the original list of values. So I still think you have an extra space or something there.

A simple alteration handles values less than zero.

ListOccurrences ( TheList ; TheValue ; Occurrences )

Let ( [ SubList = If ( Occurrences > 0 ; Substitute ( 10^Occurrences - 1 ; "9" ; TheValue  & ¶ ) ) ;

CleanList = Substitute ( ¶ & TheList & ¶ ; ¶ & TheValue & ¶ ; ¶ )

];

SubList & Middle ( CleanList ; 2 ; Length ( CleanList ) - 2 )

) // Let

• ###### 6. Re: Remove Value from List (Custom Function)?

Here's my approach as a recursive function.

In each recursion it evaluates the list to see if it has too many occurrences of TheValue in TheList.

If there are too many, it removes the bottommost one then calls itself on the result.

This function preserves the original list otherwise.  Any leading, trailing, or extra interstitial carriage returns are preserved.

I originally split out the feature of removing an item from the list as a separate custom function, but I incorporated it here to for posting to simplify implementing this function.

-Tom

/* ListOccurrences( TheList; TheValue; Occurrences */

/* Recursive */

/* */

/* Only keep the first Occurrences of TheValue. */

/* Preserves any leading or trailing ¶ and won't add a trailing ¶ if missing from the original list. */

/* */

Case(

IsEmpty(TheValue); TheList;

IsEmpty(TheList); TheList;

GetAsNumber(Occurrences) < 0; TheList;

ValueCount( FilterValues(TheList; TheValue) ) ≤ GetAsNumber(Occurrences) ; TheList;

ListOccurrences(

Let(

[

searchList = "¶" & TheList & "¶";

startPosSearchList = Position(searchList ; "¶" & TheValue & "¶"; Length(theList); -1);

endPosSearchList = Position(searchList; "¶"; startPosSearchList+1; 1);

startPosTheList = startPosSearchList -1;

endPosTheList = endPosSearchList - 1

];

Case(

startPosSearchList = 0; TheList;

startPosSearchList = 1; Right(TheList; Length(TheList) - endPosTheList );

Left(theList; startPosTheList - 1) & Right(TheList; Length(TheList) - endPosTheList + 1)

)

)

;TheValue; Occurrences)

)

• ###### 7. Re: Remove Value from List (Custom Function)?

It would be able without recursion,

calculate number to remain

get the position of nth value which should remain

after the position +1 (or length of value), remove all matching values

• ###### 8. Re: Remove Value from List (Custom Function)?

Yes, you are correct that it can be done without recursion.

In order to use Position()'s ability to find the nth occurrence, you have to bracket the value with distinct markers for it to properly find repetitions in the list.

Here is an example of a non-recursive version of the function.

-Tom

/* ListOccurrences( TheList; TheValue; Occurrences */

/* Non recursive*/

/* Only keep the first Occurrences of TheValue. */

/* Preserves any leading or trailing ¶ and won't add a trailing ¶ if missing from the original list. */

/* */

Let(

[

leftBracket = Char(2); /* A character that does not appear in the input text. */

rightBracket = Char(3) /* Another character different from leftBracket */

];

Case(

IsEmpty(TheValue); TheList;

IsEmpty(TheList); TheList;

GetAsNumber(Occurrences) < 0; TheList;

ValueCount( FilterValues(TheList; TheValue) ) ≤ GetAsNumber(Occurrences) ; TheList;

Let(

[

searchList = leftBracket & Substitute(TheList; "¶"; rightBracket & leftBracket) & rightBracket;

bracketedValue = leftBracket & TheValue & rightBracket;

startPosSearchList = Position(searchList ; bracketedValue; 1 ; Occurrences+1 );

headList = Left(searchList; startPosSearchList -1);

tailList = Right(searchList; Length(searchList) - startPosSearchList + 1);

tailListCleaned = Substitute(tailList; bracketedValue; "")

];

[rightBracket & leftBracket; "¶"];

[leftBracket; ""];

[rightBracket; ""]

)

)

)

)

1 of 1 people found this helpful
• ###### 9. Re: Remove Value from List (Custom Function)?

This did it!!!  Awesome -- thanks so much!