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

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.

Transformation

Expression

Accepted answers

LordNeilLord

Hey @AkisM

Try something like this:

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

Endif

All comments

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

Hey @AkisM

Try something like this:

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

Endif

AkisM

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

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703