Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Feb 2001
    Posts
    1,424
    Thanks
    0
    Thanked 0 Times in 0 Posts

    sorting question (MS Excel2000)

    Hello: I am not sure that this is a possible action but I would like to sort five columns of numbers and combine them into one column so that I can see what numbers are missing. Is there any way to accomplish this without having to cut and past each of the four columns into the first one and then sorting them all together so that they are in order?

    And then, if this were possible, can that same information be printed out on one page? without the cut and past routine?

    I hope this isn't too confusing. I have parts that come into various departments and they enter the numbers on excel sheets. Then it is left to me to sort and combine this information in numerical sequence and then print out the resulting information. I can do it with the cut and paste technique but I was hunting for an easier way of doing it. And there may not be anything simpler then what I am doing now.

    Tia for any advise on this subject!


    "Peace begins with a smile. "-- Mother Teresa

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sorting question (MS Excel2000)

    I don't know of any way to sort the data into one list without getting the values into one column. There are a couple of things that you might consider.

    1- Create a new worksheet and in one column create references to cells so that the values are all in one column. Sort this column.

    2- Write a macro to combine the values into one column and sort them. If you want to do that and need some help, then give us some more information on exactly what the sheet looks like. Where are the numbers located? Are the columns always the same length? Are there any blank/empty cells? Where do you want them combined and sorted. etc.
    Legare Coleman

  3. #3
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sorting question (MS Excel2000)

    Why do you need to cut/paste them into one column to see what's missing? Why not just sort on the 5 columns from major to minor (in 2 passes, of course)?

  4. #4
    Bronze Lounger
    Join Date
    Feb 2001
    Posts
    1,424
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sorting question (MS Excel2000)

    It is just a simple a excel sheet with rows and columns, nothing fancy. I don't believe I made it very clear on my first explanation but these are actually part numbers that I want to sort into one column from the various other columns so that I can readily see which part is missing in the sequence. Usually the entries end up in four, five, six or more columns and yes there can be blanks as well as uneven column lengths. They could be combined on the same sheet in a column of their own. I had thought about a macro of some sort but as you have guessed, I don't have much experience in that area.

    If you have any suggestions, I would appreciate them very much. <img src=/S/smile.gif border=0 alt=smile width=15 height=15> TIA


    "Peace begins with a smile. "-- Mother Teresa

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: sorting question (MS Excel2000)

    OK, the VBA procedure below assumes that the workbook contains a worksheet named Parts and a worksheet named SortedSheet. It assumes that the Parts sheet has no more than 10 columns of part numbers. The code clears column A of the SortedList sheet and copies all of the part numbers from the first 10 columns of the Parts sheet and then sorts column A into Ascending order.

    <pre>Public Sub BuildSortedList()
    Dim I As Integer, J As Integer, K As Integer
    Dim iLastRow As Integer
    Worksheets("SortedList").Range("A:A").Clear
    K = 1
    With Worksheets("Parts")
    For J = 1 To 10
    iLastRow = .Cells(65536, J).End(xlUp).Row
    If iLastRow > 1 Or .Cells(1, J) <> "" Then
    For I = 1 To iLastRow
    If .Cells(I, J) <> "" Then
    Worksheets("SortedList").Cells(K, 1) = .Cells(I, J)
    K = K + 1
    End If
    Next I
    End If
    Next J
    End With
    Worksheets("SortedList").Range("A:A").Sort _
    Key1:=Worksheets("SortedList").Range("A1"), _
    Order1:=xlAscending
    End Sub
    </pre>

    Legare Coleman

Posting Permissions

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