Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Worksheet Functions / August 2005

Tip: Looking for answers? Try searching our database.

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

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
steve - 27 Aug 2005 14:01 GMT
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

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.