Sort elements by same order as Excel list (un-alphabetical)

I received an IFC from a local manufacturer. In this IFC there are 114 windows modeled. Each window is modeled as one Assembly.
When importing this IFC in Revit, the Assemblies explode and leaving the window behind in multiple pieces (pieces of Structural Framings). Each piece has his own IfcGUID. My IFC import options are fine, because other IFC projects do load whole Assemblies in Revit.

I managed to get all the Assembly marks in Excel. My plan is to assign all the marks in Dolumn B to the Structural Framing IfcGUID’s in column D with Element.SetParameterByName. So all Structural Framing parts will get a mark.

I have shortened my project to 2 windows and a smaller Excel list. Currently I am heading in to 2 problems. My first one - who’s giving me a headace - is at the part where I have to order the elements with IfcGUID parameters in my project in the same order as the Excel list. See ‘list 2’ in my dynamo script.

My second problem is at the part where I have to remove the first new mark of the mark list in order to match up to the quantity of elements. As you can see my idea is not working out that well (see ‘list 3’ in the script)… :thinking:
Is there anybody who is willing to help me :wink:? Thanks in advance!

Test sort by excel list.dyn (26.9 KB)
Revit file:
Excel file: Test file.xlsx (9.6 KB)

Special thanks to lucamanzoni by making the Python script available from my last topic.

Get the IFC Guid from the IFC elements via Dynamo (Element.GetParameterByName), and then use a List.SortByKey node to sort the IFC elements by those values.


Hi JacobSmal, thank you for your reply!
I see I uploaded the old Excel file in my last post. This is the newest version: Test file.xlsx (9.6 KB)

I tried the List.SortByKey before in this script, but the node will sort its keys and the list alphabetically. I am trying to sort the IfcGUID elements in my project in the same order as the Excel list.

Is there any way to do that?

Sorry I meant List.GroupByKey. You could also use a List.FilterByBoolMask after an == node with cross product lacing and ignore the groupings.

Thanks again for your reply and input.
I don’t think I’m getting your potential solution. When I try your suggestion I am not getting the list I was hoping for. Might be that I am using your suggestion wrong though.
Are you willing to explain your suggestion a bit more? :smile:
Thanks in advance!

Compare the value of the excel data to the value of the parameter for IFCGUID with an == node, using cross product lacing. This should give you a list of lists, where each sub list is equal in length to the number of elements. If not wire the inputs in reverse order or use a List.Transpode node to flip the order.

Then use a List.FilterByBoolMask node where the elements are the list, and the Mask is the results of the == node. You’ll have to use @Level feature (I believe mask level 2) to make it work.

This should give you every item in the same order as was in excel. I suggest using a smaller data set to test with, like delete all but 5 elements and all excel rows but the ones which match the five.

Thanks a lot @JacobSmall! Sorts its elements like a machine :grin:

Hi @JacobSmall,
Your suggestion List.FilterByBoolMask after an == node with cross product lacing makes my elements sort perfect when I have a small Excel list. In the test project I have 25 Excel rows of IfcGUIDs.
Once I apply the script to my original project with +/- 7100 Excel rows with IfcGUIDs Dynamo keeps loading and my PC can’t handle the steps. I even tried to cut my project in 4 pieces, in about 2000 Excel rows, but still my PC can’t handle it.
You also mentioned an option with List.GroupByKey. I tried that option too, but when I use that node the group and the unique keys will be alphabetically sorted. Are you willing to explain a little bit more how to sort them correctly? I tried many options and came to far to quit making this script, but I am running out of attempts :sweat_smile:

Had no idea your data set was that big. At 7100 rows, your PC is going to take some time to do this exercise. Adding in that there are also 7100+ elements to test, you’ll need to reduce your data set to increase speed. The == test is 7100*7100+ tests, so 50,410,000+ hits on your system memory. Close the non-essential stuff (all bit Revit/Dynamo Player), open task manager (ctrl+shit+esc), and hit play on your graph and let it run. Even that would likely fail from lack of resources if you haven’t optimized your calculations.

In order to reduce the number of calculations, it may be better to sort your excel data alphabetically by IFC GUID and then sort your Revit elements by the same in this case, as that would allow you to use sequential runs (say 36 of them, based on the first character of the GUID, and quickly discarding element tests as well). Better still would be to build a dictionary of the excel data, using the IFCGUID as the key, and look up data with the elements IFCGUID. Would require you upgrade to Dynamo 2.0 though.

Upload a sample data set (xlsx and rvt), and I will set what I can come up with.

1 Like

Hi @JacobSmall
First of all I really appreciate your help, thank you for that!
Below you can find some samples of my project and my last attempt to complete the script.
Excel: Test file V2.xlsx (9.6 KB)

I tried to sort the Excel and IfcGUID list from Revit by the same node (List.SortListOfLists). If the .dyn file might be useful, you can find it here: BUKO new TEST.dyn (28.8 KB)

Filter by == with cross product is a rather brute force way to do it, as every possible combination has to be checked.
When you need to match two lists containing the same, unique, data in different order, a good method is to use First Index Of (Find the row number of item in list A in list B) and then Get Item at Index (Get the items in list B to rebuild the list in the same order as list A)
This only checks for each item once so it might be faster.
Only downside is if something is NOT unique then this will get the first occurrence in the list twice.

1 Like