Forum Discussion

Nathan_S_Seizmic's avatar
Nathan_S_Seizmic
Copper Contributor
May 01, 2024

VBA to read and sort based on information in cells

 

 

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

 

  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor
    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?
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor

    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.

    • Nathan_S_Seizmic's avatar
      Nathan_S_Seizmic
      Copper Contributor

      NikolinoDE 

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

Resources