Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-09-2018, 03:56 PM
thumb thumb is offline How to sort multiple columns with pages Windows 10 How to sort multiple columns with pages Office 2013
Advanced Beginner
How to sort multiple columns with pages
 
Join Date: Aug 2018
Posts: 35
thumb is on a distinguished road
Default How to sort multiple columns with pages

I have a database if you want to call it that with program names in one column and disk ID numbers in the next column. I can With all the data in just two columns is no problem sorting and keeping everything in order but that's where my expertise stops. What I have is a spreadsheet that has 8 columns with the names and ID's in each two. The page break is at every 50 lines for printing purposes. My problem is when I want to add a new program name and disk ID, I can't make everything either move down and over and to the next page. Am I making any sense? Here's an example of what I need to achieve:





If I added a new program name Bull's Eye 2 after the Bull's Eye entry, I would have to insert a space before the Business Card M entry along with the disk ID which would move everything down. The problem is I do not want everything to move down in a way, I need it to move down but the last entry in that column before the page break would move over to the top of the next column and so forth. That would also mean that the last entry in the last column would have to move down to the next page and in the first column. Do you follow? So if I did insert that space for the new program name/ID, the last program and ID number in the last two columns at the bottom of the page break which would be Disk Doctor U259, would be forced to move down to the next page line 51 columns 1 & 2. I've attached a pic showing what I am talking about. I am a novice at this so this has me pulling my hair out or at least what I have left of it.
Attached Images
File Type: jpg excel examp.jpg (123.9 KB, 35 views)
Reply With Quote
  #2  
Old 08-09-2018, 09:18 PM
NoSparks NoSparks is offline How to sort multiple columns with pages Windows 7 64bit How to sort multiple columns with pages Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

What's in row 1 and what's the name of the sheet ?
Reply With Quote
  #3  
Old 08-09-2018, 10:56 PM
thumb thumb is offline How to sort multiple columns with pages Windows 10 How to sort multiple columns with pages Office 2013
Advanced Beginner
How to sort multiple columns with pages
 
Join Date: Aug 2018
Posts: 35
thumb is on a distinguished road
Default

Right now just the same data as you see in the pic and the name of the sheet is Utility ID. Here's a pic of the top of the sheet. I can put names to it if needed.
Attached Images
File Type: jpg top excell.jpg (47.2 KB, 28 views)
Reply With Quote
  #4  
Old 08-10-2018, 08:32 AM
NoSparks NoSparks is offline How to sort multiple columns with pages Windows 7 64bit How to sort multiple columns with pages Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

On a copy of your picture...

add a new worksheet named ScratchPad
paste this code into a standard vba module
Code:
Option Explicit

Sub Eight_To_Two_And_Back()
    Dim src As Worksheet, dest As Worksheet
    Dim r As Long, c As Long, x As Long
    Dim ray As Variant

Set src = Sheets("Utility ID")
Set dest = Sheets("ScratchPad")

dest.UsedRange.Delete

r = 1
For c = 1 To 10 Step 3
    With src
        ray = Range(.Cells(1, c), .Cells(Rows.Count, c + 1).End(xlUp)).Value
        x = UBound(ray, 1)
    End With
    With dest
        .Cells(r, 1).Resize(x, 2) = ray
        r = r + x
    End With
Next c
'sort
    dest.Sort.SortFields.Clear
    dest.Sort.SortFields.Add Key:=Range("A1:A" & Rows.Count).End(xlUp), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With dest.Sort
        .SetRange Range(dest.Range("A1"), dest.Range("B" & Rows.Count).End(xlUp))
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
Call BackToEight
    
End Sub

Private Sub BackToEight()
    Dim i As Long, lr As Long
    Dim r As Long, c As Long
    Dim src As Worksheet, dest As Worksheet

Set src = Sheets("ScratchPad")
Set dest = Sheets("Utility ID")

dest.UsedRange.ClearContents

lr = src.Range("A" & Rows.Count).End(xlUp).Row
r = 1
c = 1

For i = 1 To lr Step 50
    dest.Cells(r, c).Resize(50, 2).Value = src.Cells(i, 1).Resize(50, 2).Value
    c = c + 3
    If c = 13 Then
        r = r + 50
        c = 1
    End If
Next i

End Sub
save the workbook as a .xlsm (maco enabled) file

add some data to "Utility ID"

Alt+F8 will bring up the macro dialogue box
run the Eight_To_Two_And_Back macro

the "ScratchPad" sheet can be hidden.
Reply With Quote
  #5  
Old 08-10-2018, 10:34 AM
thumb thumb is offline How to sort multiple columns with pages Windows 10 How to sort multiple columns with pages Office 2013
Advanced Beginner
How to sort multiple columns with pages
 
Join Date: Aug 2018
Posts: 35
thumb is on a distinguished road
Default

OK, I understand what you are telling me to do but where do I find the VBA Editor?
Remember, I'm a 65 year old newbie at this.
Reply With Quote
  #6  
Old 08-10-2018, 10:43 AM
NBVC's Avatar
NBVC NBVC is offline How to sort multiple columns with pages Windows 10 How to sort multiple columns with pages Office 2013
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default

hit the ALT key + F11 key
Reply With Quote
  #7  
Old 08-10-2018, 10:45 AM
thumb thumb is offline How to sort multiple columns with pages Windows 10 How to sort multiple columns with pages Office 2013
Advanced Beginner
How to sort multiple columns with pages
 
Join Date: Aug 2018
Posts: 35
thumb is on a distinguished road
Default

I did but nothing happened. I did go into the macro settings and told it to trust the vba editor, do I need to turn something else on?
Reply With Quote
  #8  
Old 08-10-2018, 10:47 AM
thumb thumb is offline How to sort multiple columns with pages Windows 10 How to sort multiple columns with pages Office 2013
Advanced Beginner
How to sort multiple columns with pages
 
Join Date: Aug 2018
Posts: 35
thumb is on a distinguished road
Default

Oh, wait, some letters came up FHNPMARWX
Reply With Quote
  #9  
Old 08-10-2018, 10:50 AM
NBVC's Avatar
NBVC NBVC is offline How to sort multiple columns with pages Windows 10 How to sort multiple columns with pages Office 2013
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default

Do you have a "Developer" tab in your ribbon?


If not, go to File|Options then pick "Customize Ribbon" and in the right pane check the "Developer" checkbox.


Click Ok.


Now try Alt+F11 again or go to Developer tab and click "Visual Basic" on the far left.
Reply With Quote
  #10  
Old 08-10-2018, 10:57 AM
thumb thumb is offline How to sort multiple columns with pages Windows 10 How to sort multiple columns with pages Office 2013
Advanced Beginner
How to sort multiple columns with pages
 
Join Date: Aug 2018
Posts: 35
thumb is on a distinguished road
Default

OK, I'm in the editor and I pasted the code into Module1 and then saved the ScratchPad.xlsm. What do I do next?



I added some data to the Utility ID sheet which just moved everything down in that column. I tried the alt F8 and a message came up saying I had to turn off instant replay whatever that is.
Reply With Quote
  #11  
Old 08-10-2018, 11:13 AM
thumb thumb is offline How to sort multiple columns with pages Windows 10 How to sort multiple columns with pages Office 2013
Advanced Beginner
How to sort multiple columns with pages
 
Join Date: Aug 2018
Posts: 35
thumb is on a distinguished road
Default

I may be messing things up. I opened up the editor and brought up the macro1 code and told it to run it using the Eight_To_Two_And_Back macro and it came up with an error: Run-time error '9': Subscript out of range.
Reply With Quote
  #12  
Old 08-10-2018, 11:20 AM
NBVC's Avatar
NBVC NBVC is offline How to sort multiple columns with pages Windows 10 How to sort multiple columns with pages Office 2013
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default

I think NoSparks will have to help you with that. You seem to have run the macro correctly but something wrong with the code supplied or dataset you have.
Reply With Quote
  #13  
Old 08-10-2018, 11:24 AM
thumb thumb is offline How to sort multiple columns with pages Windows 10 How to sort multiple columns with pages Office 2013
Advanced Beginner
How to sort multiple columns with pages
 
Join Date: Aug 2018
Posts: 35
thumb is on a distinguished road
Default

OK, I have to run out for a couple hours so maybe he can look at it. I do have a question though about when he says to enter an entry into my Utility ID, if I try to insert the entry to where it should go, I have to right click on that cell and it asks me how I want to move the data that is already there. Where and how am I suppose to add a new entry? I'll be back later. Thanks to both of your help so far, I really appreciate it.
Reply With Quote
  #14  
Old 08-10-2018, 11:29 AM
NBVC's Avatar
NBVC NBVC is offline How to sort multiple columns with pages Windows 10 How to sort multiple columns with pages Office 2013
The Formula Guy
 
Join Date: Mar 2012
Location: Mississauga, CANADA
Posts: 215
NBVC will become famous soon enoughNBVC will become famous soon enough
Default

I think he means to make sure your "Utility ID" sheet is complete with the data you want in it.
Reply With Quote
  #15  
Old 08-10-2018, 11:48 AM
NoSparks NoSparks is offline How to sort multiple columns with pages Windows 7 64bit How to sort multiple columns with pages Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Would have been better to post a sample Excel file rather than a picture, then there's no questions as to what you're working with.
Anyway, here's the test file I put together for this, "ScratchPad" is hidden.
The code is for this file, not necessarily yours, unless I've got everything right as to what you're working with.

As for adding data... doesn't matter where you put it as everything gets re-sorted. I'd just stuff it on the bottom as another record.

Instant replay.... never seen or heard of that, can't help there.


You're past this part of things but just for info...
Don't know about Excel 2013 but my 2010 installation had
Disable all macros without notification as a default setting.
I changed it to Disable all macros with notification.
This was done under File > Options > Trust Center > Trust Center Settings > Macro Settings.
Attached Files
File Type: xlsm eight_column_sort.xlsm (45.5 KB, 7 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to sort multiple columns with pages Macro to sort columns SerenityNetworks Excel Programming 4 09-02-2016 06:20 AM
.pdf to .xlsx - How to get sort-able columns? CCinPA Excel 0 07-05-2016 08:30 AM
How to sort multiple columns with pages Normal sort not bringing along other columns Dave Fraser Excel 2 06-06-2014 11:48 AM
quick replace, sort, change columns userman Excel 1 05-01-2012 06:24 AM
How to sort multiple columns with pages How to sort table having three columns? Bahir Barak Word 2 01-20-2011 01:52 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:14 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft