Incrementing number inside a START based on unique position of value in List

I have a START/END inside a template and the first column in my report is a number.
The number needs to increment according to the position of a Name inside a list.

  1. The SELECT for the START produces a list of names with duplicates - [“ABC”, “ABC”, “LMN”, “LMN”, …“NameN”]
  2. I need rows in my report where the first column is based on the above list - such as [1, 1, 2, 2,… 22]

Basically, I need my output to look like this:

ID Name Amt
1  ABC $20
1  ABC $30
2  LMN $22
2  LMN $45
.....
22  NameN $5

Would appreciate any hints.

Solved Solved
0 19 1,444
1 ACCEPTED SOLUTION

If the data is coming from the same table, the template would be like this…

View solution in original post

19 REPLIES 19

How about <<Start: ORDERBY(SELECT(…),[ID],FALSE,[Name],FALSE])>>

ID is not actually a column in the table (and I don’t want to add it to the table) - I need to generate it using a formula only for the report.

Are you using spreadsheet as a database?

Yes, for now but I will be moving this app to SQL after I get these reports working.

How many unique names you could have?

Right now about 70 unique names (and about 300 max in duplicate list) - it will undoubtedly grow but not too much more than 100 unique names this year.

Create a virtual column with a formula like
COUNT(SPLIT(LEFT(SORT(UNIQUE(Table[Name]),FALSE),FIND([Name],SORT(UNIQUE(Table[Name]),FALSE)))," , ")) and then use it as normally as <<[ID]>>

Thanks for the tip but unfortunately it doesn’t make sense to add a VC that gets calculated every sync rather than once a month when the report is scheduled. Since the list, Name, itself is dynamic and comes from a different table than the one for the report, I would need two VCs.

I think this points to the need for defining temporary variables that are accessed only by workflows/reports.

@praveen, each workflow has a list of records that match the If this is true condition at any given time.

What if we could specify a sort order for the rows as it pertains to the execution of the workflow, then provide the developer a template variable representing the current rows position in the matching set of rows? [_THISROW].[_Position] maybe?

It would be similar to [_RowNumber] in that it is not persistent, but would be tied to a user defined sort order, rather than the actual sheet’s current sort order.

I would second the [_THISROW].[_Position] proposal.

In this case it probably won’t work because it’s not a pure 1.2.3.4.etc. list. Though this is a special case because the list is dynamic and can be like 1,1,1,2,3,3,3,4,5,6,etc.

If the data is coming from the same table, the template would be like this…

Hi @Aleksi,

Very well constructed expression for placing in a template.

Wow, that was a hard expression to deconstruct. Here’s copy-able text of Aleksi’s expression for anyone else curious like me that wants to examine it.

COUNT( SPLIT( LEFT( SORT( UNIQUE( Data[Data] ) , FALSE ) , FIND( [Data] , SORT( UNIQUE( Data[Data] ) , FALSE ) ) ) , “,” ) )

_POSITION would be great.
Also, how do we not have an INDEXOF() expression?

I want POSITION() as well…

How about a feature request?

I think I don’t know what to call it… I feel like the index function we have of what’s supposed to do it… But it’s backwards? INDEX() should return a position of a value in a list… Instead ours, you pass a position to it, and it returns the item from that position…

So I guess it would need to be
POSITION()
or
INDEXOF()

LeventK
Participant V

Upvote for INDEXOF()

Top Labels in this Space