How to find the Index() position of an item from a...

MultiTech · 12-30-2020 09:40 AM

Sometimes when working with lists, you find yourself needing to know at what position an item sits in a list.

Is it the first thing? The last thing?
- At what point does this item sit in a list? Is it number 5 in the list? Number 25?

I’ve put together a sample app that demonstrates how to find such things:

The real magic is with the following formula (which I’ve cleaned so it’s “universal”)

count(
 split(
   left(
 concatenate(ENTER_LIST_HERE),
 find(
   WHAT_TO_FIND,
concatenate(ENTER_LIST_HERE)
 ) + (LEN(WHAT_TO_FIND) - 1)
),
 " , ")
)

Replace the placeholders with the appropriate data:

ENTER_LIST_HERE should be the list you need to use
- this could be an EnumList [column] or list virtual column
- this could be a table[Column] result
- this could be a SELECT() result
- any list of values is appropriate here
WHAT_TO_FIND
- This is the data-value that you wish to find the index position for
  - this could be a [column] value
  - this could be a “hard-coded value”
  - this could be the result of a formula
    - If you’re working with a non-text value, you’ll need to convert the data into a text in order for things to work - the sample app contains specifics and examples.

MultiTech

Other posts from the community that literally answer the same thing, but that didn’t pop up when I was making my post - and people can’t seem to find when asking a question with the keywords:

“list”
“index”
“position”

Maybe people will be able to find the answer now???

Rafael_ANEIC-PY

Now THAT is an expression. Bookmarking it for later, thanks Matt !

MultiTech

Bahbus

Jonathan_S

Such a juicy expression

alphacp

Just a note on this tip.... If the value is not in the list it will return position 1. You may want to start with something like IFS(IN(xxx,list),expressionabove) to improve this excellent tip

RBTAndrade

Very creative ! I had not notice that we can have any type as CONCATENATE's argument, even been clear in the function documentation:

dbaum

And, for anyone who lands here seeking a streamlined way to identify the index (position) of a row among multiple rows, here's the technique I'm using--fortunately much more straightforward find the index of an item within a list.

COUNT(SELECT(Table/SliceName[AnyColumnName], [_RowNumber] <= [_THISROW].[_RowNumber]))

If you can depend on the group of rows being contiguous in the table's data source, here's an even simpler version:

[_RowNumber] - MIN(Table/SliceName[_RowNumber]) + 1

Gustavo_Eduardo

Friend, you outdid yourself, you are a genius, thank you very much

How to find the Index() position of an item from a list