VBA to read and sort based on information in cells

[Nathan_S_Seizmic]

Hi All,

Looking for help sorting some data in Excel 365.  We’ve been able to get close using formulas and helper columns but have hit a bit of a brick wall.  Below is a small sample of the data we are sorting.

We currently group and sort Paint_Color, Category, and Part Number prefix/suffix manually using formulas, helper columns, and the Sort function in the Data ribbon. 

We’d like to automate this using VBA and expand the sorting functionality to look at each Description, correctly interpret the numerical and dimensional values therein, and sort largest to smallest, progressively from the left most numerical/dimensional value to the right most.

We had tried adding this to our manually sorting, breaking the description down in a series of helper columns by breaking out the numerical/dimensional values, then separating them using “ X “ as the delimiter.  Unfortunately we ran into an issue with Excel seeing our fractional dimensions as dates, and reading our architectural dimensions (which contain a hyphen between the feet and inches) as feet MINUS inches.

The goal is to automatically group and sort the line items without having to manipulate the data in the cells.

Ideally we’d like to achieve 100% automatic sorting.  However, given the irregular nature of our descriptions, we realize some descriptions may get missed or misread.

In that case, a code which would catch as much as possible, then allow for manual renumbering in the Item column to reposition those items which didn’t take, would be a major help.

Is there a way to handle this, using VBA to break apart and interpret the description within a reasonable shot?

Thank you, Nathan

 

[#2]

@Nathan_S_Seizmic 

Automating sorting and interpreting complex descriptions in Excel using VBA is possible.

Here is a basic outline of how you can approach this task:

1. Read Data: Write VBA code to read the data from your worksheet into an array or a collection in memory. This will allow you to manipulate the data efficiently.
2. Parse Descriptions: Write functions or procedures to parse the descriptions and extract the numerical and dimensional values. You will need to develop algorithms to handle different formats and variations in descriptions.
3. Sort Data: Implement sorting logic based on the extracted numerical and dimensional values. You can use built-in sorting functions in VBA or develop custom sorting algorithms.
4. Output Sorted Data: Once the data is sorted, write it back to the worksheet in the desired order.
5. Error Handling: Include error handling mechanisms to deal with descriptions that cannot be parsed or sorted automatically. You can prompt the user for manual intervention in such cases.

Here is a simplified example to get you started:

vba code is untested, please backup your file first.

Sub SortData()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim dataArray As Variant
    Dim i As Long
    
    ' Set reference to your worksheet
    Set ws = ThisWorkbook.Worksheets("YourSheetName")
    
    ' Assuming your data is in columns A:D
    Set dataRange = ws.Range("A2:D" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
    
    ' Read data into array
    dataArray = dataRange.Value
    
    ' Loop through the array and parse descriptions, then sort data
    For i = LBound(dataArray, 1) To UBound(dataArray, 1)
        ' Parse description and extract numerical/dimensional values
        ' Implement your parsing logic here
        
        ' Sort data based on parsed values
        ' Implement sorting logic here
    Next i
    
    ' Write sorted data back to the worksheet
    dataRange.Value = dataArray
    
    ' Notify user of completion
    MsgBox "Data sorted successfully.", vbInformation
End Sub

This is a starting point, and you will need to fill in the details for parsing descriptions and sorting data according to your specific requirements. You may need to use string manipulation functions, regular expressions, or other techniques to extract and interpret the numerical and dimensional values accurately. The text, steps and functions were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

[#3]

The goal is to automatically group and sort the line items without having to manipulate the data in the cells.


?
I can not understand your requirement.

Can you share your file and expected result?

[#4]

Do you intend to order description by NATURAL_CMP?

https://answers.microsoft.com/en-us/msoffice/forum/all/how-to-sort-927-7010-10010-kind-of-values-in-...

[#5]

@peiyezhu 

Thank you for this link.  This is part of what we're trying to accomplish.

[#6]

@NikolinoDE 

Thank you for that information.  Any recommendations on where I can learn more about developing those algorithms to interpret our data?