Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 12th 07, 09:02 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2007
Posts: 5
Default Sorting rows

Hello, I hope you can provide me a solution to this problem. I have a series
of data as outlined below. The data list has over 4000 rows and I need to
sort each row in ascending order, while keeping the data association in
column A.


A B C D E F G

Date B1 B2 B3 B4 B5 B6
1 5/7/1988 3002 4419 1729 4934 4267 1507
2 5/14/1988 1885 1507 2366 1973 1154 2237
3 5/21/1988 1507 4093 812 4770 2563 2237
4 5/28/1988 3657 1227 1973 1372 2089 4841

When I am finished sorting I would like to be able to see the data :

1 5/7/1988 1507 1729 3002 4267 4419 4934
2 5/14/1988 1154 1507 1885 1973 2237 2366
3 5/21/1988 812 1507 2237 2563 4093 4770
4 5/28/1988 1227 1372 1973 2089 3657 4841

I have tried several methods but I have not found anything that really works
well. The best I have done is converting the data using data list but that
really does not help me.


--
Lowell Shoaf
Realtor
RE/MAX Gulfstream Realty
Sarasota, Florida

  #2   Report Post  
Old May 12th 07, 10:05 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2007
Posts: 812
Default Sorting rows

Copy | Paste Special | Transpose the numbers to another range of
cells. Sort the result, each column independently. Copy | Paste
Special | Transpose the sorted numbers back to their original
location. Since you have 4000 rows (and unless you have Excel 2007),
you will have to do it in chunks. The following Sub should help with
the sorting. Change the number of columns, etc. to suit.

Sub Macro1()
Dim iEnd As Integer
Dim ws As Worksheet
Set ws = Sheets("Sheet1")
iEnd = ws.Range("A1").End(xlDown).Row
For iCol = 1 To 4
ws.Range(Cells(1, iCol), Cells(iEnd, iCol)).Sort _
Key1:=Cells(2,iCol), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
Next iCol
End Sub

Hth,
Merjet


  #3   Report Post  
Old May 12th 07, 11:03 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2007
Posts: 812
Default Sorting rows

Interesting, indeed. Thanks!

Merjet


  #4   Report Post  
Old May 13th 07, 12:32 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 20
Default Sorting rows

you're welcome, Merjet

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



merjet wrote:
Interesting, indeed. Thanks!

Merjet


  #5   Report Post  
Old May 13th 07, 05:54 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 20
Default Sorting rows

you're welcome, Lowell happy to help


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Lowell wrote:
Thanks, your terrific, the sub routine worked perfectly.



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
Sorting Rows susielotus Excel Worksheet Functions 6 June 28th 09 05:47 AM
How many rows after sorting? Big Kahuna Excel Discussion (Misc queries) 5 February 20th 09 05:45 PM
Sorting rows Bernie R. Excel Worksheet Functions 5 February 25th 08 10:58 PM
Sorting Rows Quick Orange Excel Worksheet Functions 1 August 6th 06 03:55 PM
Sorting Rows Jez[_7_] Excel Programming 0 April 2nd 06 11:10 PM


All times are GMT +1. The time now is 12:10 PM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017