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,
Thank you for your help. You are right that 365 works with arrays natively! However, I do solve my problem by using "aggregate" function after looking this video:
https://www.bing.com/videos/search?q=excel+rows+does+not+work+in+arry+function&docid=608008378289452945&mid=225508992978832E475F225508992978832E475F&view=detail&FORM=VIRE&msclkid=eeca77e3b43211ecac1e1d8a6f8b7e87
For those who have the similar issues, it is good choice!
Chuck
https://www.bing.com/videos/search?q=excel+rows+does+not+work+in+arry+function&docid=608008378289452945&mid=225508992978832E475F225508992978832E475F&view=detail&FORM=VIRE&msclkid=eeca77e3b43211ecac1e1d8a6f8b7e87
For those who have the similar issues, it is good choice!
Chuck
Sorry, but I disagree. To use 365 as if nothing has changed is a dreadful choice. True Mike Girvin puts out some great material with more tricks than a cart load of monkeys, but the game has changed. All of the time-honoured tricks with AGGREGATE (to compress a list of indices) and ROW (to generate a sequence of numbers) are no longer needed.
= FILTER(Table, (SalesRep=SelectedRep)*(Date=selectedDate))is neater or, padding it out
= LET(
repCriterion, (SalesRep=selectedRep)+(selectedRep=""),
dateCriterion, (Date=selectedDate)+(selectedDate=""),
FILTER(Table, repCriterion*dateCriterion)
)is more flexible in terms of catering for omitted criteria. Similarly, Mike's other formula would now be
= TAKE(SORTBY(UnitsRep, Units,-1), Top)
or, failing that,
= INDEX(
SORTBY(UnitsRep, Units,-1),
SEQUENCE(Top),
{1,2}
)