Highlight and order most popular values

Hello there VBA experts,

I have a list of faults and each fault has a specific fault code e.g. Hardware failure = fault code 101

There are only a set amount of fault codes in the data I extracted roughly around 10. What I would like to do is organise the data based on the fault code and highlight the whole row red for the fault code that appears the most times. So if fault 101 appeared the most times that month the data would be organised with all fault 101's at the top and then decline with the fault codes that appear less and less. I think it would be pretty cool if the data could be organised like this with the most popular faults highlighted in red at the top and then correspond down to the faults that maybe have one occurance and highlight them in green or amber etc.

Any tips or starting points with this VBA macro would be most welcomed !

Thanks

HB

Thanks for the quick response !

This is brilliant ! exactly what I was after, Would I be able to see your code for the above spreadsheet if possible.

Try this:-
This code asumes you Fault codes are in column "A" and you have 6 columns of Data.
Results start "K1".

Please Login or Register  to view this content.

Regards Mick

Thanks Mick !
That worked perfectly, very impressive peice of coding there

How would I change the colour for each fault code

In addition Could I replace the current data with the new organised data rather then having it start at k1 ?

Originally Posted by Harrison_VBA

Thanks Mick !
That worked perfectly, very impressive peice of coding there

How would I change the colour for each fault code

This will allow a header Row(1) then the code will reorganizre the data below, with "Fault Number" column = "A"
See remarks in code for colours.

Please Login or Register  to view this content.

If you don't know the new colour codes you want:-
Colours cells in new sheet "A1:A10", then run code for "Colour Index" numbers in "B"

Please Login or Register  to view this content.

Regards Mick

looks like when the +10 is removed to allow the original columns to be organised we are getting different numbers falling in the same category breaking the code

I thought that might happen posssibe!!
Try this:-

Please Login or Register  to view this content.

Regards Mick

abousetta , Thank you for your comments and the Links, I'm sure ther will be useful.
I used the code as below because I also wanted to capture the Individual ranges for each Unique as shown in red.
[code]
For Each Dn In Rng
If Not .Exists(Dn.Value) Then
.Add Dn.Value, Array(Dn, 1)
Else
Q = .Item(Dn.Value)
Q(1) = Q(1) + 1
Set Q(0) = Union(Q(0), Dn) .Item(Dn.Value) = Q
End If
Next
/code]
Regrds Mick

Unfortunately that still breaks it

Originally Posted by MickG

I thought that might happen posssibe!!
Try this:-

Please Login or Register  to view this content.

Regards Mick

Replace the last line as below:-

Please Login or Register  to view this content.

I think thats a OT SOLVED ! Thanks for all your help Mick

Originally Posted by MickG

Replace the last line as below:-

Please Login or Register  to view this content.