How to sort multiple columns without cutting and pasting into one long string first

I have four (4) column headings of data that I sort by "Item" then by "Date"
These columns, together as a whole, only use about a 1/3 of a sheet of paper but the string is quite long. Seven pages when printed. (8 1/2 x 11 portrait)
I've been cutting and pasting the string into three blocks of data to maximize the sheet of paper and conserve on paper when printing.

Is there a way to sort the two columns, as mentioned, while the data is spread out across the sheet so as to avoid having to cut and paste. In essence, selecting 6 columns but the sorting process would be accurate as a whole. Listing all items by product number and in oldest date order. I hope this makes sense.

This is rather vague. I have no idea what kind of structures/delimiters/parameters you are working with.
Could you post an example please?

Attached is the spreadsheet I'm using. As you can see I have three blocks of data (DATE, ITEM, LOC) spanning a letter size sheet of paper.
I need to have all data sorted by ITEM, then by DATE. I am currently taking all data and placing it in one long string, (4 columns wide) to sort as needed.
Once sorted I cut and paste the data back into 3 blocks of data across the sheet before printing.
I'm hoping there is a way to sort the data without having to cut and paste each time we update the file which is daily.

If you are happy to use a macro, this sub detects if your data is in 1 column or 3. Run the macro to get the data into one column, do your sort, then run the macro again to put it back into 3 columns

Public Sub ThreeColumnRearrange()
Dim ws As Worksheet
Dim lrw_1 As Long
Dim lrw_2 As Long
Dim lrw_3 As Long
Dim temp As Long
    Set ws = Sheet1
    ws.Activate
    
    lrw_1 = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
    lrw_2 = ws.Cells(ws.Rows.Count, "H").End(xlUp).Row
    lrw_3 = ws.Cells(ws.Rows.Count, "M").End(xlUp).Row
    
    If lrw_2 > 4 Or lrw_3 > 4 Then 'if in 3 columns, make 1
        If lrw_2 > 4 Then   'copy and past section 2
            ws.Range("H5:K" & lrw_2).Cut
            ws.Cells(lrw_1 + 1, "C").Select
            ws.Paste
            lrw_1 = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row

        End If
        If lrw_3 > 4 Then   'copy and past section 2
            ws.Range("M5:P" & lrw_3).Cut
            ws.Cells(lrw_1 + 1, "C").Select
            ws.Paste
            lrw_1 = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
        End If
        
    ElseIf lrw_1 > 4 Then 'if in 1 column, divide into 3
        temp = (lrw_1 - 4) \ 3
        Debug.Print (lrw_1 & " " & temp)
        ws.Range(Cells(lrw_1 - temp + 1, "C"), Cells(lrw_1, "F")).Cut
        ws.Range("H5").Select
        ws.Paste
        lrw_1 = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row
        ws.Range(Cells(lrw_1 - temp + 1, "C"), Cells(lrw_1, "F")).Cut
        ws.Range("M5").Select
        ws.Paste
    End If
End Sub

NB - I don't generally like use "Select" and but as I am unsure where your level is at, this seemed best.
-Bare in mind that if you data is in slightly different locations, you'll need to edit the cell references

Originally Posted by hdbikerchick

For the most part I'm only familiar with the basics of Excel..
Suspected a macro may be my only hope. I have no experience with them but will give this a try.

Thanks much!


I just ran the macro. WHOA. That is awesome and I am so, so thankful for your help on this truk2. Very pleased.

I'm glad I could help.

Would you mind clicking "Add reputation"? I'm new, and am eager to build up some stats.