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
Solved! Go to Solution.
What logic is used to get to the values desired? Are you just trying to remove the leading zeros?
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
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
@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.
@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
Hello @EvansM ,
Done 🙂
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regards
@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