iferror
Member
Hi, i need your help in finding a smart way to solve a problem i'm having.
I have a table (CODE_LIST) with a list of item codes
And a table (MAIN_TABLE) where i have a text field "Description", in this column the user can type in multiple codes. As the filed is free text, i have no way to prevent the user to use creative variation of the code....hence the multiple entries in CODE_LIST[ITEMCODE].
In MAIN_TABLE i created a new column (column AA) whith this array formula
=SUM(IF(ISERROR(FIND(CODE_LIST[ITEMCODE];SUBSTITUTE(UPPER([@[Description]]);"_";"-")));0;1))
to count the codes in Description. (yes, there might be rows that have no code in description)
I use this column to populate the next columns 5 where i have this formula
where itemnum =0 for column AB, 1 for column AC and so on.
It all works fine, but this approach has a flaw.
Suppose i have code ITEM-10 in Description. The count return 2 and the list of values in column AB,AC.. returns ITEM01 and ITEM10.
How'd you work around this problem?
I have a table (CODE_LIST) with a list of item codes
ITEMCODE | NORMALIZEDCODE |
---|---|
ITEM-01 | ITEM01 |
ITEM-1 | ITEM01 |
ITEM 01 | ITEM01 |
ITEM 1 | ITEM01 |
ITEM-10 | ITEM10 |
ITEM-11 | ITEM11 |
And a table (MAIN_TABLE) where i have a text field "Description", in this column the user can type in multiple codes. As the filed is free text, i have no way to prevent the user to use creative variation of the code....hence the multiple entries in CODE_LIST[ITEMCODE].
In MAIN_TABLE i created a new column (column AA) whith this array formula
=SUM(IF(ISERROR(FIND(CODE_LIST[ITEMCODE];SUBSTITUTE(UPPER([@[Description]]);"_";"-")));0;1))
to count the codes in Description. (yes, there might be rows that have no code in description)
I use this column to populate the next columns 5 where i have this formula
Code:
=LET(
itemnum; 0;
indxs; IF(ISERROR(FIND(CODE_LIST[ITEMCODE];UPPER([@[Description]])));0;1)*(ROW(CODE_LIST[ITEMCODE])-1);
Filtered; FILTER(indxs;indxs>0;0);
IF(SUM(Filtered)>itemnum; INDEX(CODE_LIST[NORMALIZEDCODE];SMALL(Filtered;itemnum+1)); ""))
where itemnum =0 for column AB, 1 for column AC and so on.
It all works fine, but this approach has a flaw.
Suppose i have code ITEM-10 in Description. The count return 2 and the list of values in column AB,AC.. returns ITEM01 and ITEM10.
How'd you work around this problem?
Last edited: