List Sorting

Shah

New member
Joined
Oct 13, 2018
Messages
3
Reaction score
0
Points
0
Excel Version(s)
2013
Hi, I have a list (rows) of random books (with columns for authors, book #'s, etc.) in an excel sheet. I want to sort books with similar titles. For example, the books with titles containing 'Real Analysis' be stacked/sorted together from the list. What is the best way? Your help will be highly appreciated!! Shah
 
This will probably require a VBA solution. You will need to supply a list of categories you wish to have accumulate and the order you wish that list of categories. With that information and a sample workbook showing some lines of data, we can provide you with a workable solution.
 
Please, find below data of a minimum workable example. For instance, I want to sort this list such that all books with words "Calculus", "Fluid", "Probability", and "Modeling" are sorted separately in this list in MS Excel. I could not find a way to attach a file in this thread so I just copied data in the text filed. Looking forward to suggestions to fix this problem. Regards from Shah

Books Collection
Sr. #TitleAuthorsBook #
1Fluid DynamicsDr.M.D Raisinghania1062
2CalculusAnton2183
3Mathematical ModelingJN kapur100
4Introduction to probbability statisticsJohns.Schiller 981
5mathematical modeling in biologyL.E Keshet2740
6Ideal FluidDr.Nawazish Ali Shah477 or 497
7Thomas CalculusThomas1594
8Probability and random processS.Dalaniammal2444
 
List Sort

This will probably require a VBA solution. You will need to supply a list of categories you wish to have accumulate and the order you wish that list of categories. With that information and a sample workbook showing some lines of data, we can provide you with a workable solution.

Please, find attached a workable example. I want to sort titles in a way that titles containing "Calculus", "Modelling", "Fluid", and "Probability" are grouped together and stacked one below the other. Waiting for the response!
 

Attachments

  • wokable_example.xlsx
    8.8 KB · Views: 17
are grouped together and stacked one below the other
In the attachment you have two files. Maybe one help?
 

Attachments

  • shah-navic-example1.xlsx
    14.8 KB · Views: 9
  • shah-navic-example2.xlsx
    16.8 KB · Views: 10
In your example you have modelling spelled with one l and in your post it is spelled with two. Be aware that in my example below you need to make sure that your spellings are exactly the same.

Here is a VBA solution.
Code:
Option Explicit


Sub Books()
    Dim s1 As Worksheet, s2 As Worksheet
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    Dim lr As Long, lr2 As Long
    lr = s1.Range("A" & Rows.Count).End(xlUp).Row
    Dim i As Long
    Dim crit1 As String, crit2 As String, crit3 As String, crit4 As String
    crit1 = "Calculus": crit2 = "Modelling": crit3 = "Fluid": crit4 = "Probability"
    Application.ScreenUpdating = False
    s1.Range("A2:D2").Copy s2.Range("A1")
    For i = 3 To lr
        lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
        If InStr(s1.Range("B" & i), crit1) > 0 Then
            s1.Range("A" & i & ":D" & i).Copy s2.Range("A" & lr2 + 1)
        End If
    Next i
    For i = 3 To lr
        lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
        If InStr(s1.Range("B" & i), crit2) > 0 Then
            s1.Range("A" & i & ":D" & i).Copy s2.Range("A" & lr2 + 1)
        End If
    Next i
    For i = 3 To lr
        lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
        If InStr(s1.Range("B" & i), crit3) > 0 Then
            s1.Range("A" & i & ":D" & i).Copy s2.Range("A" & lr2 + 1)
        End If
    Next i
    For i = 3 To lr
        lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
        If InStr(s1.Range("B" & i), crit4) > 0 Then
            s1.Range("A" & i & ":D" & i).Copy s2.Range("A" & lr2 + 1)
        End If
    Next i
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    MsgBox "Completed"


End Sub

How to install your new code
Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)


To run the Excel VBA code:
Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button
 
Back
Top