Forum Discussion
Array formula not working in my computer
Hallo People,
I want to extract values of a row which has duplicates into seperate columns.
I used the below formula
{=https://exceljet.net/excel-functions/excel-iferror-function(https://exceljet.net/excel-functions/excel-index-function(names,https://exceljet.net/excel-functions/excel-small-function(https://exceljet.net/excel-functions/excel-if-function(groups=$E5,https://exceljet.net/excel-functions/excel-row-function(names)-https://exceljet.net/excel-functions/excel-min-function(https://exceljet.net/excel-functions/excel-row-function(names))+1),https://exceljet.net/excel-functions/excel-columns-function($E$5:E5))),"")}
But the problem is while it works on my friends lap (Mac) , it does not work on mine ( windows 8). I keep getting an error message (please refer screenshot. I tried toggling between show formula as suggested online but it did not work. Kindly help !
Thanks in advance,
Sri
Hi,
It is working !! The syntax was wrong.
=IFERROR(INDEX(names;SMALL(IF(groups=$E5; ROW(names)-MIN(ROW(names))+1);COLUMNS($E$5:E5)));"")
This worked. The snytax on this excel was seperated by semi colons and not commas.
Thanks and best regards,
15 Replies
Dakkshinamurthi Pan Subramanian
An array formula to correspond to SergeiBaklan 's Power Query layout
= MAP(UNIQUE(Groups), LAMBDA(grp, TEXTJOIN(", ",, FILTER(Names,Groups=grp)) ) )If literally
=LET( groups, UNIQUE(Groups[Group]), names, MAP( groups, LAMBDA(grp, TEXTJOIN( ", ", , FILTER(Groups[Name], Groups[Group] = grp) ) ) ), VSTACK(Groups[#Headers], HSTACK(groups, names)) )Looks elegant! I tended to avoid stacking data arrays with their headers on the grounds that, though they may be part of the same table, a header is not part of the array. I think I may reconsider on the grounds that the latest batch of functions make referencing the data structure reasonably easy and self-contained.
= LET( hdr, TAKE(stackedTbl,1), data, DROP(stackedTbl,1), XLOOKUP(h, hdr, data) )
Hi,
No problems with this formula on PC as well if you enter it correctly (properly defined name ranges and enter as array formula).
Copy/paste this one
=IFERROR(INDEX(Names,SMALL(IF(groups=$E5,ROW(Names)-MIN(ROW(Names))+1),COLUMNS(D$5:$E5))),"")
and enter with Cntrl+Shift+Enter
Hi,
I am running into an array formula problem using MS 356 Excel (under a similar setting as this one). The formular is:
{=IFERROR(INDEX(names,SMALL(IF(groups=E$4,ROW(names)-MIN(ROW(names))+1),ROWS($E$1:E5))),"")}
The problem is the “ROWS($E$5:E5))” does not work properly under array formula setting:
Instead of a number sequence: 1, 2, 3, 4 .., the array formula resulted in a single number: 1. I also tried different computers and “ROW()” formula. They showed same results.
I would appreciate any helps. Thank you in advance!
Chuck
365 works with arrays natively, on practice you may forget about old style array formulas.
However, if you Ctrl+Shift+Enter formula in 365 it returns only first elements of the array. For example, {=SEQUENCE(4)} returns single element 1.
Hi,
Thank you for the fast response.
I did it but i faced the same error message. I tried selecting the whole column and only the data set as Named range and both didnt work.
COLUMNS(D$5:$E5)
Is it supposed to be D or E. Both did not work anyway.
FYI, I am using an English version of Excel on a German language system (German keyboard settings).
Best regards,
Hi,
It is working !! The syntax was wrong.
=IFERROR(INDEX(names;SMALL(IF(groups=$E5; ROW(names)-MIN(ROW(names))+1);COLUMNS($E$5:E5)));"")
This worked. The snytax on this excel was seperated by semi colons and not commas.
Thanks and best regards,
