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]

Thanks for the reply. I put the formula in BF2 as suggested and copied across and down to dh111. However, I'm having a hard time figuring out what these rows and columns are actually counting... Is it calculating correctly? I'm not sure what to do with the 1's, 2's, and 3's that are the result.

 

[shg]

I'm not sure what to do with the 1's, 2's, and 3's that are the result.

Me either, but it tells you how many 1s are common to each pair of items.

 

[Ultimist]

When you say "pair" of items... Do you mean that it only calculates commonalities between 2 items? I need to see all items that have 3 things in common first, and then those with 2, and 1.

If I see a 1 in one of the columns from your formula, what is that 1 telling me? And if it is a 2 or 3, what is it telling me?

 

[Ultimist]

For example, in BI2, there is a 1... indicating commonality with "something"... Items 1 and 2 share no common attributes from the original 55, so I'm trying to figure out what that 1 in BI2 actually means.

 

[shg]

BQ5 is 2, which says that item 4 and item 12 have two attributes in common (attributes 7 & 15)

BI12 is blank in the example I posted, meaning that Item 11 (which appears in A12) and item 4 (in BI1) have no attributes in common.

I need to see all items that have 3 things in common first, and then those with 2, and 1.

I understand that; what I offered was a step, not a solution.

 

[Ultimist]

My apologies if that specific statement came across as being ungrateful. I'm *extremely* grateful for any help.

I should mention that the attributes don't have to stay in any particular order so long as the entire table is sorted consistently so that all items keep their attributes. I'm still trying to figure out what I can learn from the 1's, 2's, and 3's that would help me with the sorting. And I'm trying to wrap my mind around BQ5 and what it says about item 4 and 12... Is there a way to add something (perhaps in yet another set of rows and columns) that would tell me what you just told me about 4 and 12 sharing attributes 7 and 15?

I'm not sure whether I'm not looking at things with the right criteria in mind, or what, but shouldn't column BQ13 also have a 2 for item 12's relationship with item 4? I guess my confusion comes from not understanding what the original formula is doing in each step.

 

[shg]

The item numbers being compared are the items listed way over in col A and the items listed in row 1.

shouldn't column BQ13 also have a 2 for item 12's relationship with item 4?

BQ13 compares item 11 (in A12) with item 12 (in BQ1)

 

[Ultimist]

I did something interesting, but I'm not sure what to make of the results. In column DI2 I did the sum of all the 1's, 2's, and 3's from your sorting formula, and copied down so that I get the sum for each item. Then I sorted the entire sheet, except for the labels, by those sums, highest to lowest. But I think the sums changed when I changed the order, So I'd have to sum the actual results as if they were numbers and not the result of a formula doing calculations based on positions. The order of items did change, though, with a sum of 41 at the top, and the sums get lower and lower as you go down the list.

I'm not sure of how to operate on the actual values, though, rather than on the formulas, for the cells, so if I want to try it I'll have to copy the calculated contents of the sort cells to a text file, empty the cells, then paste back the data as real numbers.

I know there's a way to do it without having to resort to that, I'm just not familiar with it.

 

[shift-del]

Maybe I can provide the next steps.

Arbeitsblatt mit dem Namen 'Sheet1'
	FL	FM	FN	FO	FP	FQ	FR
1	1	2	3	4	5	6	7
2	2	1	1	1	1	1	1
3	2	2	2	2	2	1	1

<tbody>
</tbody>

Zelle	Formel
FL2	=LARGE($BF2:$FK2,FL$1)

<tbody>
</tbody>

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

<tbody>
</tbody>

Copy across columns and rows.
Of course you can extend beyond seven columns if needed.

Put some text in BE1 so Excel can detect the complete list.

Then convert all formulas into values and start sorting:
Column FL - descending
Column FM - descending
Column FN - descending
... and more if needed.

 

[Ultimist]

Edit: This post may no longer apply as I looked at the results further, shift-del, and I think I see where you're going with this. So please, for now, disregard what I wrote below, in this same post.

-----
I appreciate the input, shift-del, but your suggestion seems to break the connection between the attributes and their labels. As I noted earlier, the order of the attributes isnt important, but things have to remain consistent so that all the 1's, 2's, and 3's for each attribute stay with their attribute label.

I do appreciate the effort to help me, though.