Sorting Data Based on Multiple Criteria

Question

I am creating a spreadsheet for post processing data pulled from an engineering program. The input data is copied into the sheets named "Wall Panel Data", "Node Coordinates", "Wood Wall Axial", and "Wood Wall In-Plane". For the purposes of this question we only need to work from the sheets named "Plan Geometry" and "Grouping Test". Within sheet "Grouping Test" I need to sort the wall information to group stacking walls. Stacking means the walls with the same start and end coordinates. Each group should be sorted from the top floor down (L4 = Level 4, L3 = Level 3, etc.). The first wall group is setup correctly, but I am having trouble figuring out how to find the next "L4" wall to start the next wall group without considering the walls already chosen in the previous group.

Additional things to consider:

-sometimes the start and end coordinates are flipped, but the walls need to be grouped regardless. I've remedied this with a nested xllookup function (see A3 in "Grouping Test").

-Not all wall groups will have (4) walls. Some stacks may start at L3, L2, or Base. It seems like this will require some kind of VBA loop, but unfortunately that's outside my range of knowledge.

Excel file linked here: https://we.tl/t-BhcC5Zw5ll

Answer 1

This is a geometric puzzle, the way you tried would never work. But if we look at all data at once we can solve it in a simple way.

Each Wall Panel is unique, the A,B,C,D nodes are the X,Y,Z coordinates of each vertex. In your building the Y coordinate is the height => Floor.

That means after we pulled all coordinates for each Wall Panel we can determine the Start(X,Y,Z) and End(X,Y,Z) very easy because the Start is Min(X,Y,Z) and End is Max(X,Y,Z) of each node.
Now we can group all data by Start.Y to find the floors, the lowest Y is the Base, next is L2, next is L3 and so on. That way we have no limit about the number of floors.
After that we can group all data by (Start.X, End.X, Start.Z, End.Z) to find the coordinates with the same location.
The final step is to sort the data by Group and Floor descending to get the order you want.

Sample file updated, please download again.

Andreas.

Answer 2

A brief summary of sheet names and descriptions are listed in the initial post but the sheet named “Grouping Test” should yield the result pulling data from the sheet named “Plan Geometry" . “Plan Geometry” is not the raw data, but it has all the data needed, pulling and sorting from other sheets.

Answer 3

there are many sheets in your shared excel workook.

Could.you show us which one is raw data.and which sheet is the expected result?

Answer 4

Hi Shane,

Thanks for your response. The first "sort" is grouping matching coordinate sets (as this determines if the walls actually stack). This cannot be done using the sort function you mention since some of the start and end coordinates are flipped.

The sheet I posted can already find the stacking walls given the first coordinate set of the wall in the group. The part I'm having trouble with is automating to select the first wall for the next group. I used Xlookup to find the first wall at floor "L4", but I can't using xlookup to find the next "L4" wall. It seems like some sort of VBA loop is needed.

Answer 5

Hi,

It looks like you want to do a two-level sort, but I don't know what the first level is.

So at this point let's pretend your primary sort is on Wall Labels (it's not because there are no duplicates, but let's pretend.) You will need to unmerge some of the titles.

Then the sort issue becomes how to deal with the floor sort.

1. Select the four items in the order shown on Grouping Test:

2. Copy the list and then choose File, More, Options, Advanced and scroll down to the General area and choose Edit Custom List:

3. Paste into the box on the right and click OK, OK.

Now how do you use this?

1. Select the range you want to sort, for example all the data on the Plan Geometry sheet (Except the incomplete rows at the bottom.
2. Choose Home, Sort & Filter, Custom Sort:

In the Custom Sort box pick your primary sort column from the Sort by drop down (I picked A), click Add Level and pick column N as your secondary sort. Then open the Order drop down for the Column N sort and choose Custom List...

This will display the Custom List box shown in step 3 above. Click your custom list on the left and then OK, OK.

Hope this gives you some ideas.