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]

I meant to edit this one more time, but I guess the time limit has passed for editing. I wanted to add that I initially thought of adding multiple sorting levels (through the Sort function on the Data tab of Excel (2013)), but I quickly learned that this method doesn't accomplish what I need. It merely puts all the items with 1's in the first column, first, and then sorting of further columns goes haywire because the additional sort levels don't deal with the 1's from the previous column(s). I envisioned a long and complicated formula involving multiple levels of IF/OR/AND but the nesting got so complicated my mind almost exploded, lol.

Any help would be greatly appreciated!

 

[Ultimist]

Bump. Wow, I can't believe this got pushed down in the list 4 pages overnight.

 

[Ultimist]

Bumping again. It's incredible how quickly this got pushed to page 7!

 

[Ultimist]

Bump for the day. Hopefully someone will see this before it scrolls off the front page. This forum moves faster than any I've seen! I really need help with this problem, though. So here's hoping...

 

[shift-del]

Hi

What would be the expected result and why?

 

[Ultimist]

The expected result would be the items with the most 1's in common at the top. As I said in my post, my 64-attribute table allows 4 boxes to have 1's for each item. So, all the items with 4 attributes in common (if any) would be at the top of the list. Then 3, then 2, 1, and 0. So, if ten items have 4 attributes in common, they'd be in the first 10 rows. Another set of 9 or fewer items could have 4 different things in common from the first set, and would appear in rows 11 to 19. Alphabetical sorting of the items within a set that has things in common isn't necessary, so long as we see the most commonality at the top of the chart, and the least commonality at the bottom. It's going to be a huge list of items for which I need to be able to easily find the things that have the most in common. If it were just a few items I could just look at it and figure it out. But since it will potentially hold a list of hundreds of items, doing it by hand would rapidly get tedious and wouldn't be very fun.

Thanks for your reply, and I hope this clarifies what I'm looking for.

 

[Ultimist]

I've uploaded an example of what I'm trying to do, to mediafire. It contains a list of 110 items with 55 attribute columns. The rule still holds true that each item can only have 4 attributes.

Here's the example file, saved in Excel 2003 format:

AttribSortExample.xls

I just need to sort the items as described in my previous posts. The reason this has 55 attributes instead of 64 is that while listing these (manually) into the file, I haven't gotten to any items that have the 56th to 64th attributes. Also, as mentioned, I'd prefer if any solution offered allows for the number of items to increase or discrease, and the same for the number of attributes, if possible.

Thanks!

 

[Ultimist]

I should mention that my example file already has the attributes sorted in a specific way. I just can't figure out how to sort it further. The attributes are sorted so that the column with the greatest number of 1's is on the left, followed by the column with the next highest number of 1's, and so on. This by itself doesn't accomplish what I need, though, which is to get the *items* sorted into those with the greatest number of things in common first, proceeding downward to the items with the least in common with others.

In the example file, there are no items that have 4 things in common (so far). So, the items at the top would be the ones with 3 attributes in common, followed by 2, 1, and 0. The 1's and blanks are pasted from my actual document that has private information. I just created a document with generic attribute names and item names, and pasted the 1's in so that I could provide an example file without revealing the private information associated with the *real* attribute names.

 

[shg]

You could start with calculating the number of common attributes:

       -BF-- -BG-- -BH-- -BI-- -BJ-- -BK-- -BL-- -BM-- -BN-- --BO-- --BP-- --BQ-- --BR--
   1   Item1 Item2 Item3 Item4 Item5 Item6 Item7 Item8 Item9 Item10 Item11 Item12 Item13
   2                         1     1                 1     1                            
   3                   1                 1                        1                    2
   4             1                 1                 1            1      1              
   5       1                                         1            1             2      1
   6       1           1                       1     1                                  
   7             1                             1                         1             2
   8                               1     1                                              
   9       1           1     1     1                                                    
  10       1                                                                            
  11             1     1     1                                           1      1      1
  12                   1                 1                        1                    2
  13                         2                                    1                    1

The formula in BF2 and copied across and down is

=CHOOSE(COUNTIFS($B2:$BD2, 1, INDEX($B$2:$BD$111, COLUMNS($BF$1:BF$1), 0), 1) + 1, "", 1, 2, 3, "")