List filtering without SELECT() or FILTER()

[Kirk_Masden]

I have what I think is a somewhat unusual question.

Let’s say I have a list as follows:

{1, 3, 5}

What I want to do is to removed all values from the list that are above or below a certain value. So, if I removed every element of the list that is less than 2, my list would be as follows:

{3, 5}

I know how to do this if I have a column with with various values. However, that is not the case I am faced with today. Today, I have column in which each cell might have multiple values as follows:

1, 3, 5

I’d like to use SPLIT() to make each cell into a list and then compare the values in the list with a value in another cell to get rid of those values that are less than it. Can that be done? If there’s no better way, I think I might be able to combine SORT() and INDEX() in a convoluted expression that can handle a finite number of list items but I’m wondering if there’s a simpler way.

[Aleksi]

If you have cell with multi values like “1 , 2 , 3”, you sould be able to convert your list like SPLIT(CONCATENATE(Table[Column])," , "). Does this help you enough?

[Kirk_Masden]

Thanks @Aleksi! I know about SPLIT() and I think I may be able to combine it with INDEX() to go through one by one (let’s say, up to 6 or whatever) to test each value, but I was wondering if there might be an easier way to get rid of the values in such a list that are above or below a certain value.

[Steve]

UNTESTED

To get all values less than split-value:

(
  SPLIT(
    INDEX(
      SPLIT(
        SORT(
          [list-column]   // existing list
          + {split-value} // list containing only split-value
          + LIST("")      // minimum value to ensure item before split-value
          + {beyond-max}  // maximum value to ensure item after split-value
          - LIST()        // remove duplicates
        ),
        concatenate(
          " , ",          // guaranteed by blank added above
          min-value,
          " , "           // guaranteed by beyond-max added above
        )
      ),
      1                   // 1 for values less than split-value, 2 for more than
    ),
    " , "
  )
  - LIST("", beyond-max)  // remove blank, beyond-max added above
)

Assumptions:

• [list-column] contains no blank values or blank removal is okay.

• [list-column] contains no duplicate values or duplicate removal is okay.

beyond-max is a value greater than might ever occur in in [list-column].

Remove all // comments to use the expression.

[Koichi_Tsuji]

Hello @Kirk_Masden san

Can you always predict the max number (count) of the list items? 6 is forseeable max? Or you cant predict what is the max count of list items?

If you can predict or restrict max count, then I have an idea.

[Aleksi]

How about…
INTERSECT(
SPLIT(CONCATENATE(Table[EnumListColumn])," , “),
SPLIT(CONCATENATE(LIST(6,7,8,9,10)),” , "))

[Kirk_Masden]

Thanks so much everyone!! I need to study these solutions. I deeply appreciate the extremely helpful feedback and would like to report back after I learn to use them.

@tsuji_koichi I think the total number of list items may be six or so. So, I think that using index() I could go through each one up to that number. I need to study how @Steve has used INDEX() but it looks like his approach is different from what I had imagined. I don’t understand @Aleksi’s use of INTERSECT() yet but I think that’s probably just my own failure to wrap my brain around it. I’ll work on it.

[Koichi_Tsuji]

As far as you can place the max number of count for the enumlist items, then it could work.
Not sure your exact use case in terms of how to generate the enumlist, which comprise of the numeric number.

See screenshot.

Is this kinda of what you like to achieve?

[Kirk_Masden]

Thanks a lot! What is the expression in the background?

[Koichi_Tsuji]

To get started with, I just unhided the hidden fileds for the explanation.

Base Number is number type where the app user enter the arbtray number for the subscruction calc, i.e. N - each enum list item.

Count is just count the number of items for the list.

Base list. This is a bit of trick.
I made independent table looks like this.

Base list filed is ref type field, which refer to this table based on “Count” Fields. By doing so, we are able to generate the list such as

{1}
{1 , 1}
{1 , 1, 1} … based on the count of list items dynamically.

Then Base number dynamic list.

For this quick sample, I retrcted the max number of count = 3. The user can not select 4 and more enum list items.

App formula for this field

IFS(
[Count]=1,
list(number(index([Base List],1))*[Base Number]),
[Count]=2,
list(number(index([Base List],1))*number([Base Number]),number(index([Base List],2))*number([Base Number])),
[Count]=3,
list(number(index([Base List],1))*number([Base Number]),number(index([Base List],2))*number([Base Number]),,number(index([Base List],3))*number([Base Number]))
)

Looks complex, but not so. I think this expression self explanatory. Based on count of list and the arbitray number for the substruct calc, it will generate the dymamic list.

Lastly the result.

IFS(
[Count]=1,
list(
IF(
number(index([Base Number Dymamic List],1))-number(index([Enum List],1))>0,
number(index([Base Number Dymamic List],1))-number(index([Enum List],1)),
" "
)
)-list(" ")
,
[Count]=2,
list(
IF(
number(index([Base Number Dymamic List],1))-number(index([Enum List],1))>0,
number(index([Base Number Dymamic List],1))-number(index([Enum List],1)),
" "
),
IF(
number(index([Base Number Dymamic List],2))-number(index([Enum List],2))>0,
number(index([Base Number Dymamic List],2))-number(index([Enum List],2)),
" "
)
)-list(" "),
[Count]=3,
list(
IF(
number(index([Base Number Dymamic List],1))-number(index([Enum List],1))>0,
number(index([Base Number Dymamic List],1))-number(index([Enum List],1)),
" "
),
IF(
number(index([Base Number Dymamic List],2))-number(index([Enum List],2))>0,
number(index([Base Number Dymamic List],2))-number(index([Enum List],2)),
" "
),
IF(
number(index([Base Number Dymamic List],3))-number(index([Enum List],3))>0,
number(index([Base Number Dymamic List],3))-number(index([Enum List],3)),
" "
)
)-list(" ")

)

If the permissible “MAX” count of the list is 6, then amend the calc accordingly.

In case we are not able to put a bar for max permissible count for the list, I suppose we need to employ the Steve’s “Looping Actions” technique. But by the number of the count of list foreseeable always, we can push all the possible different case within the calculation, which make it possible to run the calc dynamically.

Hope a bit of help for you.

[Aleksi]

I think about like this…
Your values are from 1 to 10 and you need to check what numbers are more than 5 (what numbers belong to intersect 6,7,8,9,10). Sample… if the column contains 1,3,5,7,9… result is 7,9. Another idea with this formula is that it doesn’t need to be below or above.

[hru]

I have tested with a few numbers as below picture, based on my understanding on this. As comparison I have also indicates of using google sheet formula:

1. Expression:

TOP(
	SORT([EnumList],TRUE),
		IFS(
		INDEX(SORT([EnumList],TRUE),6)>=[MinValue],6,
		INDEX(SORT([EnumList],TRUE),5)>=[MinValue],5,
		INDEX(SORT([EnumList],TRUE),4)>=[MinValue],4,
		INDEX(SORT([EnumList],TRUE),3)>=[MinValue],3,
		INDEX(SORT([EnumList],TRUE),2)>=[MinValue],2,
		INDEX(SORT([EnumList],TRUE),1)>=[MinValue],1,		
		)1
)

1. Google Sheet Formula

TEXTJOIN(", ", 1, FILTER((TRANSPOSE(SPLIT(A1,","))), (TRANSPOSE(SPLIT(A2, ",")))>=B2))

[Kirk_Masden]

Thank you so much, @Heru! I’ve been a bit busy and have not yet had enough time to study your answer and the other answers carefully enough. I’ll study and experiment some more and get back to this thread.

[Kirk_Masden]

At this point, I’ve solved the problem using Steve’s approach. I’m still studying the other approaches and they may be equally good, but I’ve confirmed that the direction @Steve pointed me in worked out.

Here’s my understanding of Steve’s approach:

1. Use list functions to reorder all of the values in a proper list.
2. Use CONCATENATE() to make the list into a single text string so that you can chop it into two pieces at the appropriate point using SPLIT().
3. Then, save the half of the list you want (with the appropriate commas so that it can be split into a list again next time) to the sheet as text.

That’s a bit of an oversimplification but that’s pretty much what I have done following Steve’s lead.

I can report, however, that the following was rejected by AppSheet:

  + {""}  

This produced the following error:

{“”} could not be parsed due to exception: Sequence contains no elements.

Also, since I have many values over 1000 I found it necessary to use concatenate() several times to get rid of commas between the hundreds and thousands places. I found that if I had some values with the commas and some without that SORT() didn’t work properly. Also, the commas interfered with proper functioning of SPLIT(), as I recall.

I’m fascinated and amazed by the many different approaches that have been so kindly suggested. I would like to continue to study all of them but for now, @Steve’s approach has done the trick for me. Thanks everyone!

[Steve]

Kirk_Masden:

Here’s my understanding of Steve’s approach:

Exactly correct!

Kirk_Masden:

the following was rejected by AppSheet:> > auto> + {""} > >

Grrr… Try + LIST("") instead. Probably also should replace - {"", beyond-max} with - LIST("", beyond-max), too. I’ve updated my earlier post to reflect these fixes.