Forum Discussion
Nathan_S_Seizmic
May 01, 2024Copper Contributor
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
- peiyezhuBronze ContributorThe 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?- peiyezhuBronze ContributorDo 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-excel/2aabe102-9f53-41ec-b70f-78a5ecff8d84- Nathan_S_SeizmicCopper Contributor
Thank you for this link. This is part of what we're trying to accomplish.
- NikolinoDEGold Contributor
Automating sorting and interpreting complex descriptions in Excel using VBA is possible.
Here is a basic outline of how you can approach this task:
- 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.
- 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.
- 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.
- Output Sorted Data: Once the data is sorted, write it back to the worksheet in the desired order.
- 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_SeizmicCopper Contributor
Thank you for that information. Any recommendations on where I can learn more about developing those algorithms to interpret our data?