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 - 27 Aug 2005 15:57 GMT
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.
> This is the type of data I have. The actual spreadsheet is 15 columns
> by 100 rows.
[quoted text clipped - 21 lines]
> This mechanical process is time consuming. Is there a faster, easier
> way?

Signature
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html
steve - 27 Aug 2005 18:23 GMT
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 - 27 Aug 2005 18:33 GMT
You're welcome! J.E. McGimpsey has some information on SUMPRODUCT:
http://www.mcgimpsey.com/excel/formulae/doubleneg.html
> Debra,
>
[quoted text clipped - 3 lines]
> spend some time learning about the SUMPRODUCT function so that I
> thoroughly understand how your method works!

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