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://exce...
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,
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) )Yes, in general you are right
- Sergei and Peter,
Thank you both for your help. I am running into another problem about running Excel 365 script online. I was wondering if you can point me to a right direction.
The problem is that I embedded an Excel report in my website (https://www.westernqrm.com/test-1) that includes a script (button "Select03"). Everything works well except the script did NOT run.
Any help and your advice would be appreciated. Thanks
Chuck (Email address removed)
