Forum Discussion
How to sort with formula very challenging problem
Hi!
I have the table attached. I need help with a formula solution that will extract in a single column from multiple columns unique values and also sorted depending on number of times each text appear in that range.
Hello,
Indeed a challenging formula.
Here is what I could come up with. put this formula in J2 with control shift enter.
=INDEX($B$3:$G$9,MIN(IF(MAX(COUNTIF($B$3:$G$9,$B$3:$G$9)*NOT(COUNTIF($J$1:J1,$B$3:$G$9)))=COUNTIF($B$3:$G$9,$B$3:$G$9),ROW($B$3:$G$9)-MIN(ROW($B$3:$G$9))+1,"")),MATCH(MAX(COUNTIF($B$3:$G$9,$B$3:$G$9)*NOT(COUNTIF($J$1:J1,$B$3:$G$9))),(COUNTIF($J$1:J1,"<>"&INDEX($B$3:$G$9,MIN(IF(MAX(COUNTIF($B$3:$G$9,$B$3:$G$9)*(COUNTIF($J$1:J1,"<>"&$B$3:$G$9)-ROWS($J$1:J1)+1))=COUNTIF($B$3:$G$9,$B$3:$G$9)*(COUNTIF($J$1:J1,"<>"&$B$3:$G$9)-ROWS($J$1:J1)+1),ROW($B$3:$G$9)-MIN(ROW($B$3:$G$9))+1,"")),,1))-ROWS($J$1:J1)+1)*COUNTIF($B$3:$G$9,INDEX($B$3:$G$9,MIN(IF(MAX(COUNTIF($B$3:$G$9,$B$3:$G$9)*(COUNTIF($J$1:J1,"<>"&$B$3:$G$9)-ROWS($J$1:J1)+1))=COUNTIF($B$3:$G$9,$B$3:$G$9),ROW($B$3:$G$9)-MIN(ROW($B$3:$G$9))+1,"")),,1)),0))
6 Replies
Hello,
Indeed a challenging formula.
Here is what I could come up with. put this formula in J2 with control shift enter.
=INDEX($B$3:$G$9,MIN(IF(MAX(COUNTIF($B$3:$G$9,$B$3:$G$9)*NOT(COUNTIF($J$1:J1,$B$3:$G$9)))=COUNTIF($B$3:$G$9,$B$3:$G$9),ROW($B$3:$G$9)-MIN(ROW($B$3:$G$9))+1,"")),MATCH(MAX(COUNTIF($B$3:$G$9,$B$3:$G$9)*NOT(COUNTIF($J$1:J1,$B$3:$G$9))),(COUNTIF($J$1:J1,"<>"&INDEX($B$3:$G$9,MIN(IF(MAX(COUNTIF($B$3:$G$9,$B$3:$G$9)*(COUNTIF($J$1:J1,"<>"&$B$3:$G$9)-ROWS($J$1:J1)+1))=COUNTIF($B$3:$G$9,$B$3:$G$9)*(COUNTIF($J$1:J1,"<>"&$B$3:$G$9)-ROWS($J$1:J1)+1),ROW($B$3:$G$9)-MIN(ROW($B$3:$G$9))+1,"")),,1))-ROWS($J$1:J1)+1)*COUNTIF($B$3:$G$9,INDEX($B$3:$G$9,MIN(IF(MAX(COUNTIF($B$3:$G$9,$B$3:$G$9)*(COUNTIF($J$1:J1,"<>"&$B$3:$G$9)-ROWS($J$1:J1)+1))=COUNTIF($B$3:$G$9,$B$3:$G$9),ROW($B$3:$G$9)-MIN(ROW($B$3:$G$9))+1,"")),,1)),0))
Hi Flora,
If you have Excel 2010 or later the easiest way is to use Power Query (Get&Transform in 2016) - query your table, unpivot days columns, group by fruit name counting them and sort by count, land the result into Excel sheet.
Like attached.
