Forum Discussion

Flora Sim's avatar
Flora Sim
Brass Contributor
Mar 23, 2018
Solved

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 tex...
Formulas & Functions
Tutorial
  • Jamil's avatar
    Jamil
    Mar 23, 2018

    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))

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Mar 23, 2018

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.

Jamil's avatar
Jamil
Bronze Contributor
Mar 23, 2018
Sergei

good approach with using power query.

Resources