Data manipulation - Changing Number Row

Hello Everybody,

I need some help to manipulate data in a column. Please refer attached two excel worksheets. One is ITEMNO-INPUT (Data sample) and the second one is ITEMNO-OUTPUT (Expected Results)

I did try to use Multi- Row Formula, but the logic is too complicated to work the way I wanted.

I want the [CANOTEITEM] column to reflect ITEMNO - OUTPUT excel (Expected Results). This column needs to be INT Value.

Thanks In advance

ITEMNO - INPUT.xlsx

ITEMNO- OUTPUT with numbers.xlsx

Datasets

Custom Tools

Data Investigation

Accepted answers

afv2688

Hello @EvansM ,

Here you can find them re-ordered to your original output:

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

multirow.yxmd

All comments

echuong1

What logic is used to get to the values desired? Are you just trying to remove the leading zeros?

Jonathan-Sherman

Hi @EvansM,

You could achieve this by simply changing the data type of the column to an INT using a select tool, INT64 would work perfectly fine.

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

20200117 - ChangingDataType.yxmd

afv2688

Hello @EvansM,

I used the multi row formula with the following:

IF IsEmpty([Row-1:CANOTEITEM]) THEN 101
ELSEIF Right([Row-1:CANOTEITEM],1)='1' THEN [Row-1:New Field]+1 ELSE [Row-1:New Field]+99 ENDIF

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Regards

multirow.yxmd

EvansM

@echuong1 I am not trying to remove the Zeros. I want to [CANOTITEM] on INPUT to look like [CANOTITEM] on OUTPUT.

If you look at [CANOTITEM] on OUTPUT. Is very different with [CANOTITEM] on INPUT.

That is how I want to look.

EvansM

@afv2688 Thank you very much for your quick response. This is very close to the expected results. The problem is line 43 of OUTPUT [CANOTEITEM] needs to go back to 101 because the [CUSt_NO] changes.

This means very time [CUSt_NO] change,[CANOTEITEM] needs to go back to 101.

That is the logic

EvansM

@Jonathan-Sherman Thank you very much for the response. Please refer to those two excel worksheets.I want [CANOITEIM] for ITEMNO - INPUT to look like [CANOITEIM] for ITEMNO - OUTPUT.

ITEMNO - INPUT is the data sample.

ITEMNO - OUTPUT is the expected results

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703