Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 21st 05, 05:40 PM
steve
 
Posts: n/a
Default Sorting while grouping 3 rows together...

i need to keep three rows together while i sort by column. so it will be
groups of three.

i thought to copy the name down three times, but is there a way i can
automate this? this is a sheet that gets updated everyday.

thank you

  #2   Report Post  
Old June 21st 05, 06:44 PM
Dave Peterson
 
Posts: n/a
Default

I assumed headers in row 1 and the name in column A.

I inserted a new column A (moving the name to column B).

I put =b2 in A2.

Then I put =IF(MOD(ROW(),3)=2,B3,A2&"x")
in B3 and dragged down.

I started with names like:
Name
aaa
aaa
aaa
eee
eee
eee
bbb
bbb
bbb
ccc
ccc
ccc

My new columns A:B looked like
Key Name
aaa aaa
aaax aaa
aaaxx aaa
eee eee
eeex eee
eeexx eee
bbb bbb
bbbx bbb
bbbxx bbb
ccc ccc
cccx ccc
cccxx ccc

Then I converted column A to values (edit|copy, edit|paste special|values).

Then sorted by that helper column.

When I was done, I deleted column A.


steve wrote:

i need to keep three rows together while i sort by column. so it will be
groups of three.

i thought to copy the name down three times, but is there a way i can
automate this? this is a sheet that gets updated everyday.

thank you


--

Dave Peterson
  #3   Report Post  
Old June 21st 05, 06:44 PM
gazornenplat
 
Posts: n/a
Default


Might be just me, but I don't quite understand what you want here. Could
you give a simple example?

Gaz


--
gazornenplat
------------------------------------------------------------------------
gazornenplat's Profile: http://www.excelforum.com/member.php...o&userid=24494
View this thread: http://www.excelforum.com/showthread...hreadid=380927

  #4   Report Post  
Old June 21st 05, 07:25 PM
Jay
 
Posts: n/a
Default

i need to keep three rows together while i sort by column. so it will
be groups of three.

i thought to copy the name down three times, but is there a way i can
automate this? this is a sheet that gets updated everyday.


One way is to use a helper column.

I'm supposing that column A has the field to be sorted, and you want to
sort by the first of the three values.

In the helper column, put this in row 1:
=A1
Put the identical formula in rows 2 and 3.

Select the three cells and extend down for the length of the list.

Then select the whole table and sort by the helper column.
  #5   Report Post  
Old June 21st 05, 08:01 PM
steve
 
Posts: n/a
Default

right, that's what i did. The problem i am running into is that there are
blank cells for entries that have not been entered yet. so when i sort, the
equations like =A1 get messed up. here is what it looks like

Project Name Project status Salesman Date

walmart estimate km 6/21
detail
barlist

estimate, detail, barlist are seperate cells, but when i sort by project
name, i need these three rows to be considered one row.

"Jay" wrote:

i need to keep three rows together while i sort by column. so it will
be groups of three.

i thought to copy the name down three times, but is there a way i can
automate this? this is a sheet that gets updated everyday.


One way is to use a helper column.

I'm supposing that column A has the field to be sorted, and you want to
sort by the first of the three values.

In the helper column, put this in row 1:
=A1
Put the identical formula in rows 2 and 3.

Select the three cells and extend down for the length of the list.

Then select the whole table and sort by the helper column.



  #6   Report Post  
Old June 23rd 05, 10:39 PM
Jay
 
Posts: n/a
Default

?B?c3RldmU=?= wrote:

right, that's what i did. The problem i am running into is that there
are blank cells for entries that have not been entered yet. so when i
sort, the equations like =A1 get messed up. here is what it looks
like

Project Name Project status Salesman
Date

walmart estimate km
6/21
detail
barlist

estimate, detail, barlist are seperate cells, but when i sort by
project name, i need these three rows to be considered one row.


The idea is to sort by the helper column.

Below is an example. Save it in a file with the ".csv" extension.

Double-clicking the csv file should open it in Excel.

Select everything and sort bon the Helper column.

--------------------- cut here ------------------------
Project Name,Project status,Salesman,Date,Helper
walmart,estimate,km,6/21,=A2
,detail,,,=A2
,barlist,,,=A2
sears,estimate,ab,2/13,=A5
,detail,cd,3/22,=A5
,cancelled,tr,4/11,=A5
penney,estimate,mi,6/16,=A8
,detail,,,=A8
,barlist,,,=A8



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Microsoft Excel - Grouping and Sorting Cells Michael Blogg Excel Worksheet Functions 1 May 26th 05 09:27 PM
Microsoft Excel - Grouping and Sorting Cells Michael Blogg Excel Discussion (Misc queries) 1 May 26th 05 12:50 PM
How to keep rows together when sorting columns? Amit Excel Worksheet Functions 1 March 28th 05 06:01 PM
Sorting Rows by Color SharonJo Excel Discussion (Misc queries) 1 January 14th 05 12:56 AM
Macro for sorting different rows ciscopena Excel Worksheet Functions 0 November 2nd 04 12:04 AM


All times are GMT +1. The time now is 09:39 PM.

Powered by vBulletin® Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.
Copyright 2004-2022 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017