Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sorting array of data (97/SR1)

    Hi,

    I have an array of data that contains over 3000 rows by 10 columns. What I want to do is sort the data by category along the 1st column while carrying the information from other columns along with it. What is the simplest way of doing this? A sample worksheet is attached.

    Thanks, Hanan.
    Attached Files Attached Files

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sorting array of data (97/SR1)

    Hanan,

    Have you tried Data, Sort.

    Place the cursor somewhere in the range of Data, and then select Sort from the Data menu. The entire range should be selected and you can select the column on which you want to sort the data.

    Andrew C

  3. #3
    Star Lounger
    Join Date
    Feb 2001
    Posts
    60
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting array of data (97/SR1)

    Hi Andrew,

    My mistake here. What my intention here is to get sort the data by Data->Sort and then get a list at the end of the array in the form of:

    Column A: Record Name; Column B: # of occurances

    In the attache worksheet the would be for example: Angle = 7

    Cheers, Hanan.

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sorting array of data (97/SR1)

    Hanan,
    1) Add a first row containing headers.
    2) Sort the data by category (first column)
    3) Use the Data>Subtotals:
    ---At each change of Category
    ---Use function Count
    ---Add subtotal to Category

    Hope this helps; subtotals are very useful! (Notice that you can collapse the original data) --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Sorting array of data (97/SR1)

    Hanan,

    Sorry about the misunderstanding, I think I know what you wanty now.

    As well as Sam's subtotal suggestion, you might consider a Pivot Table, which does not need the source data to be sorted first.

    I attach a copy of your workbook with the Pivot Table on sheet 1. I also pasted a capture of the PT wizard dialog which will give you an idea of how it was set up. You have the option of placing the table on a new sheet when you are creating it. Also on sheet 2 you will find a Subtotal sample as Sam suggested. You will notice to the right a grouping buttons which you can click to hide/show leverls of detail. At the top you will see 3 small buttons numbered from 1 to 3. Click number 2 will give you the subtotals by category, click number 1 for a grand total only.

    Hope you can follow it.

    Andrew C
    Attached Files Attached Files

  6. #6
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Kent, England
    Posts
    108
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sorting array of data (97/SR1)

    If you're just interested in looking at the file from time to time to find the count of an individual item, you can have a SUBTOTAL formula at the top of you're data. If you apply a filter to your data, subtotal will count/sum etc. your selection.

    =SUBTOTAL(3,A1:A5) - Count A1 to A5
    =SUBTOTAL(9,A1:A5) - Sum A1 to A5

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •