VBA & Macro Problems

How to Group and Sort Data by Multiple Coordinates in Excel VBA

Emma Brown Oct 13, 2026 870 views

Question details

The user needs to group architectural data (stacking walls) based on start and end coordinates, handling reversed coordinates, and then sort each group by floor height from highest to lowest.

How to Group and Sort Data by Multiple Coordinates in Excel

Product: Microsoft Excel
Device & OS: not provided
Scenario: Organizing and automating the grouping of geometric plan data across multiple sheets in a workbook.
Observed behavior: Currently, the user can manually find stacking walls after selecting the first wall but needs an automated solution to select the first unassigned wall for every subsequent group.

Before you start

Ensure your raw plan geometry data is consolidated on a single sheet and that you have enabled the Developer tab in your spreadsheet application to run VBA macros.

Solution 1Recommended

Use a VBA Macro to Normalize Coordinates and Group Data

Create a VBA loop to automatically normalize X and Z coordinates, group matching walls (including those with reversed coordinates), and sort by the Y coordinate (floor) in descending order.

Normalizing coordinates using the MIN and MAX functions ensures that walls with reversed start and end points are successfully matched together.

A VBA loop can automate the process of finding the first unassigned wall in each group and applying the sorting logic dynamically without manual intervention.

Normalize the Coordinates

In your VBA script, use Application.WorksheetFunction.Min and Max to create normalized X and Z values for each wall's start and end points.

Group Matching Records

Write a loop that checks the normalized coordinates against unassigned records, assigning a unique group ID to walls with matching X and Z values.

Sort by Floor Height

Use the Range.Sort method in VBA to sort the grouped data by the Y coordinate column in descending order to arrange them from the highest to the lowest floor.

Automate Group Selection

Wrap the logic in a Do While or For Each loop to continuously find the next unassigned wall row until all walls are successfully grouped.

Use a VBA Macro to Normalize Coordinates and Group Data

Data Preparation: Make sure your target sheet (e.g., 'Grouping Test') is cleared before running the macro to avoid overwriting or blending with existing expected results.

Solution 2

Use Helper Columns and Advanced Formulas for Grouping

If you prefer a formula-based approach without VBA, you can structure your data with helper columns using MIN/MAX and SORT functions.

Advanced Data Management

Automate Complex Data Sorting and VBA Macros in WPS Spreadsheet

WPS Spreadsheet provides robust support for VBA and advanced array formulas, allowing you to easily normalize, group, and sort complex architectural coordinates with high performance.

1. Open your dataset: Launch WPS Spreadsheet and open your workbook containing the plan geometry data.
2. Enable Macro tools: Navigate to the 'Developer' tab on the top ribbon and click 'Macros' to access the VBA editor.
3. Write and execute your code: Paste your coordinate grouping VBA script into a new module and click 'Run' to automate the sorting process.

Full support for VBA (Visual Basic for Applications) to automate repetitive grouping tasks.Advanced functions like SORT, FILTER, and MIN/MAX for dynamic data organization.Seamless compatibility with Microsoft Excel (.xlsx and .xlsm) file formats.

microsoft office alternative - wps office

Frequently Asked Questions

Why are my reversed coordinates not grouping properly in Excel?

Reversed coordinates fail to group because their start and end points differ in sequence. Using the MIN and MAX functions to normalize these points ensures they generate the same unique identifier regardless of direction.

Can I run VBA loops to automatically group unassigned rows?

Yes, you can use a Do While loop in VBA to scan a helper column or array for empty cells, select the first unassigned wall, and iterate through the remaining rows to find and group matching coordinates.

How do I sort data by multiple criteria using VBA?

In VBA, you can use the Range.Sort method with Key1 assigned to your primary group identifier and Key2 assigned to your secondary criteria, such as the floor coordinate (Y). Set the Order2 parameter to xlDescending to achieve the desired high-to-low sorting.

Excel & Data Problems