• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Separator in Array formula with Non-US settings?

navic

Active Member
Hi
Can anyone help me (i.e. explain) why the formulas below do not work in my Excel 2013?
I use Non-US settings on my Windows OS and also in Excel (Eastern Europe).

I know that the separator in Excel formulas is different from US settings and Non-US settings.

I'm wondering why the formula I use with Non-US settings doesn't work.
These below are the original formula given by the author @Sajan at this link https://chandoo.org/forum/threads/h...out-0-in-excel-solved.10681/page-2#post-62083

Original formulas with US settings by Sajan
In the 'L9' cell
Code:
=IFERROR(IF(COUNTIF(L$8:L8,L8)<COUNTA(INDEX(List,0,MATCH(L8,Headings,0))),L8,NA()),INDEX(Headings,MATCH(1,ISNA(MATCH(Headings,L$8:L8,0))*(TRANSPOSE(MMULT(TRANSPOSE(N(List<>"")),ROW(List)^0)>0)),0)))
and
In the 'M9' cell
Code:
=INDEX(List,SMALL(IF(INDEX(List,0,MATCH(L9,Headings,FALSE))<>"",ROW(INDEX(List,0,MATCH(L9,Headings,FALSE)))-MIN(ROW(List))+1),COUNTIF(L$8:L8,L9)+1),MATCH(L9,Headings,FALSE))

Converted formulas to Non-US settings?
In the 'L9' cell
Code:
=IFERROR(IF(COUNTIF(L$8:L8;L8)&#60\COUNTA(INDEX(List;0;MATCH(L8;Headings;0)));L8;NA());INDEX(Headings;MATCH(1;ISNA(MATCH(Headings;L$8:L8;0))*(TRANSPOSE(MMULT(TRANSPOSE(N(List&#60\&#62\""));ROW(List)^0)&#62\0));0)))
and
In the 'M9' cell
Code:
=INDEX(List;SMALL(IF(INDEX(List;0;MATCH(L9;Headings;FALSE))&#60\&#62\"";ROW(INDEX(List;0;MATCH(L9;Headings;FALSE)))-MIN(ROW(List))+1);COUNTIF(L$8:L8;L9)+1);MATCH(L9;Headings;FALSE))

This part of the formula below is problematic (red font).

Original
1st -> ......COUNTIF(L$8:L8,L8)<COUNTA.........List<>"")),ROW..........^0)>0))
2nd -> ......FALSE))<>"",ROW.......

Converted
1st -> ......COUNTIF(L$8:L8,L8)&#60\COUNTA.........List&#60\&#62\"")),ROW..........^0)&#62\0))
2nd -> ......FALSE))&#60\&#62\"",ROW.......

If I use the TRANSPOSE function (in the Row) as the Array formula, then I have to set "backslash" instead of "comma" as the separator
but
If I use the TRANSPOSE function (in the Column) as the Array formula, then I have to set "semicolon" instead of "backslash" as the separator (see image1)

In the case of the formula given by Sajan, I can't use "backslash"?
Also, my Excel 2013 does not accept the string &#60 or &#62?

Does anyone have a solution for the above formulas?
Attached to this post is an Excel file.
 

Attachments

  • image1.png
    image1.png
    9.2 KB · Views: 5
  • separatorinnonussettings.xlsx
    13.5 KB · Views: 3
Try,

Original >>
1st -> ......COUNTIF(L$8:L8,L8)<COUNTA.........List<>"")),ROW..........^0)>0))
2nd -> ......FALSE))<>"",ROW.......

Change to >>
1st -> ......COUNTIF(L$8:L8,L8)>COUNTA.........List<>"")),ROW..........^0)>0))
2nd -> ......FALSE))<>"",ROW.......

Regards
 
Hi @bosco_yip

Thank you for your answer.
I know that CHAR(60) returns '<' and CHAR(62) returns '>'.
At this moment, I don’t know why I concentrated on the formula separator, so the separator in the formula seemed problematic to me. I have tried many variations and always involved separator.

I just got lost in the formula or I didn’t fully understand it.
Thank you again, the problem is solved.

Regards
 
Back
Top