Add an ascending number to rows, which will stay the same if the last row was repeated

[AkisM]

My excel table looks like this:

Counter                 ProductDescription
1                            Bed
2                            Chair
3                            Chair
4                            Table
5                            Chair
6                            Table

I want every time it finds the same consecutive description, to not increase the counter. So the output should look like:

Counter                 ProductDescription
1                            Bed
2                            Chair
2                            Chair
3                            Table
4                            Chair
5                            Table

I used a MultiRow formula tool with the "Update Existing Field" set to [Counter], and my expression is:

IF [ProductDescription]=[Row-1:ProductDescription] THEN ToNumber("1") ELSE ToNumber("[Counter]+1")
ENDIF

But it gives me endless 0s and 1s. I used the "ToNumber" function because I was getting an error about a string being entered into a numerical field or something. Counter is an integer and productdescription is a string.

[danilang]

Hi @AkisM 

You're close with your formula

Try this modification

IF [ProductDescription]=[Row-1:ProductDescription] THEN ToNumber("Row-1:[Counter]") ELSE ToNumber("Row-1:[Counter]")+1
ENDIF

If the previous description is the same as the current then use the previous counter.  Otherwise, add 1 to the previous counter

Dan

[LordNeilLord · Accepted solution]

Hey @AkisM 

Try something like this:

If [Field2]=[Row-1:Field2] Then [Row-1:Field1] Else
ToNumber([Row-1:Field1])+1

Endif

[AkisM]

Hey, thanks for the help. Unfortunately even after trying the above formula I still get an endless series of 1's and 0's, as well as some conversion errors "Lost information in corvesion". When I try @LordNeilLord 's formula I get the "Counter" resulted in a string but the field is numeric error

EDIT: Nevermind, the error was in my syntax. Thanks to both.

[MarqueeCrew]

Here's what to do (blindly blabbering):

Create a new field with your multi-row formula as an int16.

IF
[counter] = 1 THEN 1 ELSEIF
[product description] = [Row-1:product description] THEN [Row-1:New Field] ELSE
[Row-1:New Field] + 1
ENDIF

cheers,

Mark

[danilang]

---

@MarqueeCrew wrote:
... (blindly blabbering)...

---

That's how I get through my day.

Dan