• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sorting matrix based on cell colour

M

MarkK

New Member
Hi Guys


Please see the attached workbook.


http://www.smallfiles.org/download/3306/Student_Scores.xlsx.html


On the current tab, i have setup a listing of students who have completed a spelling test, in which a conditional colour formatting has been applied to the cells based on the correct answer which is noted in column B.


If they have spelt the word correctly as per the cell in column B, the cell for each student will populate to green. If it is incorrect, it will populate in red.


Whilst this part was fairly simple, im wanting to sort these columns/rows based on the colour, so it can stack and segment the results based on colour, that way i can visually determine who is performing well and who is struggling.


Please refer to the ‘Intention’ tab, in particular rows 89 onwards.


Ignoring the actual spelling on the words inputted (as i have taken out the conditional formatting to create a staggered effect by manual colour fill), this is how i want the end product to look, with the student getting the most correct answers, or most green cells, to be on the left, whilst the student has has the lowest amount of green to be on the right.


What is the best way i can achieve this, as im struggling to do this via ‘Custom Sort’. Perhaps im overlooking something?


Is there a way in which i can have excel count the number of green and red cells per column? I tried a COUNTIF, with no success.


Appreciate your help.

workbook.
 
MarkK


I don't believe you can sort based on Conditional Formats


Why not add a Helper Column and use the CF formula to add values to the helper column

Then use that column for the sort.
 
Hi Mark ,


The issue is that if you do a sort with the CF on , the CF will colour all the cells according to the rules , which you do not want.


What you have to do is convert the CF to a permanent cell colour which is not governed by any rules ; thereafter you can do a sort.


This conversion will mean that the CF rules will no longer be present in those cells ; is this acceptable to you ? If so , check this link :


http://www.mrexcel.com/forum/excel-questions/284013-convert-conditional-formatting-into-standard-one.html


Narayan
 
You must log in or register to reply here.
Back
Top