# List Management Question - Dynamic Excel Values

Hi all. I am new to Dynamo and am struggling with some list logic. I know there are lots of questions about lists out there, but I can’t seem to find a post with similar scenarios. Apologies if this is answered elsewhere.

I often find myself creating a lot of redundant series of nodes. Typically I am doing the following:
Read Excel File > Filter Excel Data into List A and List B > Perform a series of tasks on both lists, but slightly different. I end up creating duplicate series of nodes with one minor difference in each one. It just seems like there is a better way to do this.

For instance, in this graph I am taking Program Data from excel, then filtering for Level 1 and Level 2 spaces in order to place mass families on Level 1 and Level 2 respectively. I am first filtering out the levels, then placing masses. In turn, I end up completely duplicating the Level 1 “Place Mass” nodes in order to create Level 2. The slight difference is that for Level 2 I am translating them up (or in this case over), and then assigning their respective room names and departments at the end. Even if I were to do this repetitive task, what happens when I have Level 3, 4, 5, etc? Keep copying the series? How do I make it more dynamic so that my Excel data can change from 1 Level to 5 Levels without me adjusting the graph?

Another similar situation - I want to color the masses by department, but am struggling to find the best way to do so dynamically. Seems I have to know ahead of time if I have 2 departments or 5, and to place 2 to 5 separate “Set Parameter Value by Name” and “Material by Name” nodes. Also not sure how to filter by department after my masses are currently grouped into Levels. How do I separate them again to color by department, without having to repeat the same set of nodes at the end of each series (i.e. assign colors/materials to Depts X, Y, Z at the end of the Level 1 series, then assign colors/materials again to Depts X, Y, Z at the end of the Level 2 series).

Third scenario - if I want to place masses in order of size, and I sort my Area data (from Excel) according to size, how do I then match up and apply the Name, Department, etc values (from Excel) now that my list is out of order (because I sorted the Area). I think this has something to do with assigning and re-mapping key values to the Name and Department data?

If you make it this far, I am also struggling with getting the tagging to work. I seem to have the nodes set up correctly, and the tag is loaded into the project, but I don’t see any tags in the views I select.

Sample Graph, Excel Data, and Family used for placement attached.
Place Masses By Level_Sample.dyn (199.0 KB)
Program Data.xlsx (13.4 KB)
MSS-Program-SpaceType-Box-UH.rfa (400 KB)

1 Like

Grouping your data by level and working with list levels and lacing will simplify this a ton. I recommend you do a little review before adjusting this though as it’s likely you’re going to struggle mightily if you’re don’t have a good map. Try working though the designing with lists section of the primer as a first step.

http://dynamoprimer.com/en/06_Designing-with-Lists/6_designing-with-lists.html

2 Likes

There is a lot you are asking for so I am going to take several pokes at it.

For now, the first idea I thought of when you wanted to create a color schedule based on department, you can use unique items of the department list and populate a list of colors to match using shortest lacing. The only caveat would be that you would first have to make a list of colors/materials that is longer than the longest list of departments you would ever have.

So if the max amount of departments you could ever have in a project is like 10, then a color list of at least 10 would be needed. Then when it comes time to assign, using a list of this structure, {{Unique Department Name, Color for department},{Unique name 2, color 2}, etc} with an if statement by department name for the combined list of masses of all floors of some form could be used. (This is essentially a dictionary if you are familiar with the term)

This is all easier said than done but would be how I would tackle that situation.

Here is a very quick and over simplified version of what I meant:

The main part is getting the unique department names, a long list of colors/materials, then associating a department name to a color. From there you can use a if element’s department name == a department name, use it’s color as value.

Also includes a very liberal use of list levels that can hopefully give you some clues as to how to use them.

1 Like

For the first situation, what @JacobSmall said is the best approach. Having sublists grouped by floors/levels and running it all as one instead of splitting it by floors would be best and easier to follow. List levels becomes very important and it might be easier to try making a few smaller scripts as test versions when trying to figure out which list level to use where.