Color Sorting Problems

[Trucky]

Hello all,

I'm Michael. I've recently started a new job and it requires me to rummage through some Excel files that are much larger than I am accustomed to... like ones with a few hundred rows at minimum, sometimes in the thousands.. But these come to me without any color coding or sorting, which is my function as far as this chart goes. I go one-by-one through each row (it gets extremely tedious) and color code each row in a certain column (in this case C). The screenshot I'm providing should show what I mean. What I have to do now is go through sort -> Sort by color fill -> then click every color, over and over, until the list looks like the 2nd image I've provided. Is there any way to do this automatically?

BEFORE:

This isn't just a few colors either, I routinely use 25+ colors. And I have to re-order this excel file multiple times to generate the kinds of data and graphs that the company wants. It's a killer, for sure.



Now, I know I ordered them by largest "group of color" in the 2nd one. That isn't a requirement, but if I could make that happen at the same time as well, that would be nothing short of amazing.

The ever-so-hopeful AFTER:

If someone can provide a solid solution, I'm very open to giving a reward! I've searched for a few hours now and I don't know if I'm just bad at searching or what - I can't find the answer.

Thanks,

Michael.

 

[mole999]

what makes you able to decide what colour to use

set out ALL the criteria and its colour

would achieving this make you redundant or more productive

 

[Trucky]

Perhaps I should have been a bit more specific.

All the colors I use are laid out on another sheet that I print out. Once I'm done with the initial sort, I do a count of each color and write it down. The I get the sections with the largest counts and subdivide those with font colors, and write that down as well. Problems arise when I have to use filter and sort over and over, due to the spreadsheet re-ordering itself into unusable patterns. Not only that, some of the data is unusable when it comes to me in the first place, but I cannot remove it until after all this color coding is done. Depending on how well I can "fix" a certain row, it might be reclassified.. this happens often.

You see, the entire process is inefficient and redundant anyways, but it is how they do things.. and until I make an entirely new process that they approve of, I'm stuck with this. The color sorting is just one facet of it, but it would save me many tens of minutes per iteration.

Also, I see by the posting rules that I've forgotten to mention the "version", which is 2010.

 

[mole999]

what makes you able to decide what colour to use (think is it a keyword that makes it RED, is it over or under a certain value)

set out ALL the criteria and its colour

Perhaps I should have been a bit more specific. YES

 

[Trucky]

ADVERTISEMENT

I'm beginning to think I'm barking up the wrong tree here.

But I'll give it one more go around: I'm sorting these rows with color (fill). This is *not* based on value, keyword, etc. There is no software that could add these fills in because this spreadsheet is made from data entered by many, many different people. Nothing is standard, which is why a human (me) has to go through it all. I can't sort it by word because half the people can't spell anything properly, and correcting all of the spelling errors would take at least an hour given the amount of text in each row and the very high chance that it isn't just a simple word that is misspelled but an abbreviation, etc. that is also messed up. If that happens, I have to go through all sorts of schematics and drawings to figure out what they meant, because chances are they're on another shift or in a building a mile away.

All I want is something that can take the extreme monotony out of sorting by the colors. That's it. Do you understand the situation now?

 

[mole999]

so the paintbrush can't help ?

Sorry If I misunderstood, but if you have a logic that you apply to decide on a colour then it should be able to be automated

 

[mikerickson]

ADVERTISEMENT

I think this will do what you want.

Sub test()
    Dim rngToSort As Range
    Dim listOfColors As Variant
    Dim i As Long, Pointer As Long
    Dim oneCell As Range
    
    Application.ScreenUpdating = False
    
    Set rngToSort = Sheet1.Range("C:C")
    With rngToSort
        Set rngToSort = Application.Intersect(.Cells, .Parent.UsedRange)
    End With
    ReDim listOfColors(1 To rngToSort.Count)
    
    For Each oneCell In rngToSort
        If IsError(Application.Match(oneCell.Interior.Color, listOfColors, 0)) Then
            Pointer = Pointer + 1
            listOfColors(Pointer) = oneCell.Interior.Color
        End If
    Next oneCell
    
    If 0 < Pointer Then
        With rngToSort.Parent
            With .Sort
                For i = 1 To Pointer
                
                    .SortFields.Clear
                    .SortFields.Add(rngToSort, xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = listOfColors(i)
                    
                    .SetRange rngToSort.EntireRow
                    .Header = xlNo
                    .MatchCase = False
                    .Orientation = xlTopToBottom
                    .SortMethod = xlPinYin
                    .Apply
                
                Next i
            End With
        End With
    End If
    Application.ScreenUpdating = True
End Sub

When you go through and give the cells their colors, is there a logic that you use to determine which color?
Could your determining which color for which row be automated?

 

[Trucky]

so the paintbrush can't help ?

Sorry If I misunderstood, but if you have a logic that you apply to decide on a colour then it should be able to be automated

Unfortunately not.

The main thing is column C.. for example, it might list what an inspector saw during an inspection. And it's usually written like how it would be spoken. When I mentioned the problems I was having with sorting and filtering earlier, how it messed the order up.. that was because of this very loose way of typing them up. If I filter for "loose fitting", it might hit upon 25% of the ones that actually should show up... Some people say loose, some say lose because they don't know any better. Or even just "ls". Or they skip the proper terminology anyways and say "it doesn't fit", and I've even run across "wonky fitting". It's mind numbing. And to be honest, I have to do these sorts and filters for each color many times over to get what I need.

I'll get together with my boss and see if we can't whip up something to deal with this bad reporting.

I think this will do what you want.

Sub test()
    Dim rngToSort As Range
    Dim listOfColors As Variant
    Dim i As Long, Pointer As Long
    Dim oneCell As Range
    
    Application.ScreenUpdating = False
    
    Set rngToSort = Sheet1.Range("C:C")
    With rngToSort
        Set rngToSort = Application.Intersect(.Cells, .Parent.UsedRange)
    End With
    ReDim listOfColors(1 To rngToSort.Count)
    
    For Each oneCell In rngToSort
        If IsError(Application.Match(oneCell.Interior.Color, listOfColors, 0)) Then
            Pointer = Pointer + 1
            listOfColors(Pointer) = oneCell.Interior.Color
        End If
    Next oneCell
    
    If 0 < Pointer Then
        With rngToSort.Parent
            With .Sort
                For i = 1 To Pointer
                
                    .SortFields.Clear
                    .SortFields.Add(rngToSort, xlSortOnCellColor, xlAscending, , xlSortNormal).SortOnValue.Color = listOfColors(i)
                    
                    .SetRange rngToSort.EntireRow
                    .Header = xlNo
                    .MatchCase = False
                    .Orientation = xlTopToBottom
                    .SortMethod = xlPinYin
                    .Apply
                
                Next i
            End With
        End With
    End If
    Application.ScreenUpdating = True
End Sub

When you go through and give the cells their colors, is there a logic that you use to determine which color?
Could your determining which color for which row be automated?

Wow! That works really well. And it doesn't take any time at all to run.

Now I'm very new to macros and I do have one further question: I forgot to mention (or include on my examples) that these tables have a top row (header I believe it's called), is there a way for the macro to ignore this? It has no "color" to it so when I run the macro it is still preserved (as the very bottom row).

As far as the sizing of the groups and the order they're displayed in, I think I'll try and figure that out on my own. It isn't critical and I think it might be interesting to have a starting point to "dive into" as far as VBA and the like goes.

As far as your questions:

There is a kind of logic, but it's very.. fluid. Maybe IBM's Watson would have a shot at it. Problems arise when I try and think of a way to color code them off the bat, because the very important column (C) usually contains anywhere from 5-50 words and they usually cross. For instance, a hardware problem (think screws) could go like: "AL2 Side Cabinet 2Y PCB mounting hardware is loose and appears to be of incorrect sizing". Incorrect hardware is something that I go after, but the word incorrect can pop up anywhere. So is loose hardware.. and mounting parts. And the spelling can change for these words.. hardware -> "hdw" or "hw" or "hardw"

The groups I'm looking for are all coming from "up top", aka the leaders so I can't change them at all.

As I've mentioned before, it is a very organic process. Tedious but necessary to gain anything useful out of these reports.

Even with those problems, it appears you code is just what I needed. I really appreciate the help! If you have a PayPal address I could send you a donation for the effort. And you may have just spurred an effort to learn this coding so I can apply it in the future without having to ask.

Thanks,

Michael R.

 

[mikerickson]

To accomidate a header row in Row 1.

With rngToSort
    Set rngToSort = Application.Intersect(.Cells, .Parent.UsedRange).Offset(1,0)
End With

I think that both mole and I, when we first saw the problem, were looking at the OP as asking more "which color should a line be" than "how to sort once colored".

 

[mole999]

clear out your auto correct words so a spell check can throw back obviously bad words like ls