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
and
In the 'M9' cell
Converted formulas to Non-US settings?
In the 'L9' cell
and
In the 'M9' cell
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)<\COUNTA.........List<\>\"")),ROW..........^0)>\0))
2nd -> ......FALSE))<\>\"",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 < or >?
Does anyone have a solution for the above formulas?
Attached to this post is an Excel file.
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)))
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)<\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)))
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))
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)<\COUNTA.........List<\>\"")),ROW..........^0)>\0))
2nd -> ......FALSE))<\>\"",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 < or >?
Does anyone have a solution for the above formulas?
Attached to this post is an Excel file.