How do I sort (or group?) a spreadsheet by similar rows?
 

This is the type of data I have. The actual spreadsheet is 15 columns
by 100 rows.

A B C D E

Task 1 x x x
Task 2 x x x x
Task 3 x x x
Task 4 x x x
Task 5 x x x x

I want to be able to identify which rows are the same, and to be able
filter the final list by either row group or column (to be able to see,
for example, only rows that are part of a particular group, or only
rows that have check marks in specific columns).

In the example, I would identify rows 1 and 4 as a group, rows 2 and 5
as a group, and row 3 as a group.

In the actual spreadsheet, I have been able to identify the row groups
by repeatedly filtering the list, and then identifying in column F
which rows are in a group. This allows me to filter column F by group.

This mechanical process is time consuming. Is there a faster, easier
way?

You could add a row above the table, and insert numbers to score the
columns. For example:

In cell B1, type the number 1.
In cell C1, type the formula: =B1*2
Copy the formula across to column P
In cell Q1, type the heading, Total
In cell Q2, type the formula: =SUMPRODUCT(--(B2:P2="x"),--($B$1:$P$1))
Copy this formula down to the last row of data
Sort the table by the Total column, to see similar rows together.

You can hide the row of numbers.

steve wrote:
This is the type of data I have. The actual spreadsheet is 15 columns
by 100 rows.

A B C D E

Task 1 x x x
Task 2 x x x x
Task 3 x x x
Task 4 x x x
Task 5 x x x x

I want to be able to identify which rows are the same, and to be able
filter the final list by either row group or column (to be able to see,
for example, only rows that are part of a particular group, or only
rows that have check marks in specific columns).

In the example, I would identify rows 1 and 4 as a group, rows 2 and 5
as a group, and row 3 as a group.

In the actual spreadsheet, I have been able to identify the row groups
by repeatedly filtering the list, and then identifying in column F
which rows are in a group. This allows me to filter column F by group.

This mechanical process is time consuming. Is there a faster, easier
way?



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra,

Your solution worked beautifully. Thank you!

I understand the notion of scoring the columns. Now I just need to
spend some time learning about the SUMPRODUCT function so that I
thoroughly understand how your method works!

You're welcome! J.E. McGimpsey has some information on SUMPRODUCT:

http://www.mcgimpsey.com/excel/formulae/doubleneg.html


steve wrote:
Debra,

Your solution worked beautifully. Thank you!

I understand the notion of scoring the columns. Now I just need to
spend some time learning about the SUMPRODUCT function so that I
thoroughly understand how your method works!



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html