Sorting a sheet of item attributes with certain restrictions?

[Ultimist]

I originally posted this question on an ExcelIsFun youtube video, and the channel owner PM'ed me to ask me to post it here. I'm not exactly an Excel noob, but I really only know the basics and a few specific commands that might be a bit more advanced.

I have a sheet with 65 columns. The first column is the item name. The other 64 represent different attributes assigned to the item in the first column. Each column in a row can contain a null space if it does not have the attribute, or a 1 if it does. The catch is, each item on the left only (and always) has 4 out of the 64 attributes.

The 4 attributes can be totally different attributes from the other items, the same, or partially the same.

What I need to be able to do is sort the entire sheet so that items with the most attributes in common are in the top row. The list should procede downward with items that have fewer and fewer attributes in common. The last row would be the item with the least in common with any of the others.

The worksheet in question contains some fields that have private information, otherwise I'd post the actual excel file somewhere and link it here. But here's a simplified, comma delimited example using only the item column and 10 attribute columns (in this example, only 3 of the 10 columns can have 1's. Null cells are 0's. The first line is column labels:

Animal, Brown, Black, Spotted, Two Legs, Mammal, Fish, Avian, Reptile, Healthy, Treated
Horse,1,0,1,0,1,0,0,0,0,0
Cow,0,0,1,0,1,0,0,0,1,0
Cat,0,1,0,0,1,0,0,0,1,0
Dog,1,1,0,0,1,0,0,0,0,0
Mouse,0,1,1,0,1,0,0,0,0,0
Chicken,0,0,0,1,0,1,0,0,0,1
Goose,1,0,0,1,0,1,0,0,0,0
Rabbit,1,1,0,0,1,0,0,0,0,0
Snake,0,0,1,0,0,0,0,1,0,1

Whether there are many or few animals with certain things in common isn't important in this example. What's important is the ability to sort the items in such a way that one can easily see the greatest degree of commonality at the top, and the least at the bottom.

How would I sort this list that way? And how would I do it for my 64-attribute (always 4 attributes for each item) table?

I'd prefer a method that is somewhat flexible in case the number of attributes goes higher or lower, or the number of allowed 1's per row changes.

Thanks!

 

[Ultimist]

shift-del,

I did everything you suggested, then sorted according to your instructions. It seems to have done something, but I'm not sure what. The first item still has nothing in common with the item directly below it. Each item shares 1 attribute with (at least) 1 other item, and up to 3. We may be getting closer, though, so... good work!

Edit: Looking at things a bit closer, your suggestion sorts the list so that the items that share 3 things in common with other items appear at the top. The list just doesn't tell me *which* 3 items it shares attributes with. It does the same for items that share 2 attributes, but again, doesn't tell me which 2.

The next step would be somehow further sorting the list so that the items sharing those attributes are listed directly below the item in question.

 

[shg]

I don't know that it's possible to sort the way you want.

Item 1 may have 3 attributes in common with item 2 (say, a, b, & c) and 3 (a, b, & d), but 2 and 3 have only two (a & b) in common with each other. Similarly, say, for items 4, 5, and 6. In what order should those 6 items appear? A linear list isn't going to capture that.

Sounds to me like a graph where the edges indicate the number of common elements, which is sort of what I posted.

 

[Ultimist]

Would it be possible if the table were allowed to relist the items more than once? For example, list item 1 and the the items that have attributes in common with it, starting from the ones with the greatest number in common, to the least.

Then list item 2 and its common items, even if they are already listed under item 1, and even item 1 itself.

It would be easy (but extremely time consuming) to do this by hand... sorting by the attribute columns, then copying all the items with 1's in that attribute column to another sheet. Sort the list by the next attrib column, and repeat until you have a bigger list with repeated items, but sorted so that each *cluster* has at least one attribute in common. It would be nice to have an automated way to do such a thing, though, as doing it manually for hundreds of items isn't something I'd look forward to doing.

 

[Ultimist]

I'm not convinced that it is impossible to do in excel, as there are only a few required constraints that would have to be followed. Compare each item to the entire list and find those that have 3 attribs in common, list them, and do the same for 2 attribs and 1. Then repeat for the next item, making sure to include the possibility that it matches 1 or more attribs from the items listed before it, and items listed after it. I think with allowing item rows to be repeated where necessary, it would be possible. Figuring out how to do it that way, though, is where I'd need help.

 

[shg]

There's no question that Excel can do it if the algorithm can be articulated.

 

[Ultimist]

I wonder, if each attribute were unique... such as having "AA" or "A1" in the column wherever Attribute 1 is present, AB for Attribute 2, etc... would having unique values for each attribute solve anything that having all 1's prevents?

 

[Ultimist]

It seems I can do what I want to accomplish by manually sorting by each attribute column, and then manually arranging all items that share that 1 attribute from those that share this one and 2 more, then 1 more, and none. Then I paste those results to a new sheet, go back to the first sheet, sort all items by the next attribute column, and repeat the whole process. BUT... this is extremely tedious and time consuming. Any way to make excel do this for me?

This option causes repeated items, but that's ok as I stated. Just need to be able to do it somewhat automatically and save (hours and hours) of doing it manually.

 

[shg]

I still don't know what the end result is supposed to look like. Post a workbook that shows the result and explain why it is what it is.

 

[Ultimist]

I can post a small example, but it may have to be tomorrow as I'm pretty busy today. I can't do the whole sheet as the "end result" would take far too long to do manually. Any end result of the entire sheet would be the result of figuring out how to do this properly with excel.

 

[shg]

Don't rush on my account.