Hello,
My ultimate goal is to look up two values in two different columns and find the row number that both of them are in and thereby locating a third value in a third column from an excel file. This is a very tedious process to do manually because it has to happen for so many objects. Thats why I am trying to read the unknown value from excel using the two other known values from revit to find it. I have the entire script written and working for finding the known values from revit, but I am stuck on the excel part. I am able to import the excel file, but I cant seem to work with it. Here is my first effort:
I think it’s as simple as a lacing issue. Both nodes search a list for an input, but you’re supplying a list of lists. Try setting the lacing to longest or using list levels to run the node on the correct sublists.
well I tried setting the list level for bot the list. transpose and indexOf nodes but neither one works. Also, I am getting a lot of null values, is there a way to filter those out? Thanks in advance.
IndexOf only returns the first instance of that item. Use StringContains (which shouldn’t require any lacing.)
You can either filter out nulls or leave them if they don’t mess up your list structure. You can try RemoveNulls or ReplaceNulls from Lunchbox.
Edit: Can you show the error you’re getting? It’s probably the nulls, which you’ll most likely want to convert to False.
Ok, well I am having trouble with this then, because if I take the list of list, then I lose all of the actual cell values dont I? Becasue look at this:
I actually copied this value to my clipboard and Ctrl+F in excel and it found it no problem, but dynamo is telling me that it doesnt exist. What is going on? Perhaps I dont fully comprehend the list of list concept. Is there a way to separate each excel column out in its own list so I can do each separately?
Thats what I thought but it still cant find it. And I cant get an Item from a list if I dont have its index, can I? Thats why I am trying to find its index. But I dont seem to be able to. Here is what the error message is saying.
I’m referring to the element input 5RF-D07. It’s technically a list of a single item. So Dynamo thinks you’re searching for a list of a single item. You need to get just the value so it looks for that item within a list (not a list of that item within a list).
Edit: And again, I think you want String.Contains.
Edit: Have you gone through your String.Contains output to make sure there aren’t any True values? That part should work, but the FilterByBoolMask won’t work if there are nulls.
Ok so now we are making progress, but still I have a few questions before I can make use of this information. I wrote them on my screen capture:
Edit:
I need to be able to find the index of the value in the original list, not just extract the value of the list cropped from its index value. I need to be able to equate the index values to look up the other values in the excel spreadsheet.
As to why IndexOf isn’t returning anything I’m not sure at this point.
The empty lists from your FilterByBoolMask node are the sublists that don’t contain that value.
From reading your explanation I don’t think you do need the indices. If I understand correctly, you’re comparing different column values within a single row (at a time). Rather than getting the item at the same index for each column list, you can use the same boolean mask to filter the list instead - it works the same way.
This is exactly what I am trying to do.
Edit, I am using two values from two columns to locate a single row in excel and then looking to the A column in that row and trying to extract that cell’s value.
But each columns value will contain a different index for the same row. I am afraid I dont follow you, I dont understand what you mean or how it will work. Can you elaborate please?
Maybe you could show your Excel file or elaborate on how it’s setup. I imagine it looks one of two ways:
You have x number of columns. You check Column1 for “A” and Column3 for “B”. If a row meets both these criteria, get the value in Column5 for that row.
You have x number of columns. If Column1 and Column3 have the same value, return the Column5 value for that row.
Both scenarios would be able to use a boolean mask or an index.
I need to look up an items source and destination, (sometimes the source or destination, is both a source and a destination, so it matters which column it is found in) and then look up the feeder number:
Edit: but I still dont know how to extract a single column of values out of excel. What you say makes sense but I dont know how to do that in dynamo.
Excel data is listed by row (each sublist represents a row). You can Transpose the lists to get the data by column (each sublist represents a column). From there you can get the columns you need and query each one individually.
Yeah I already had the excel file transposed to set it up by columns, but I still cant do what you are talking about. I still cant extract the index value of the items. Are you suggesting that I use the un-transposed version? If so how to I extract a sub list with two search values in it? Cause if you tell me how to do that, I can make that work.
Edit:
What I meant by “I still dont know how to extract a single column of values out of excel”…was that I can get ALL of the columns, but I cannot isolate one column of data. In other words how to I look up a value of x find it in sublist N and then report all the values in sublist N. Sublist N would be an entire excel column. I cant pull out a column of data…
I’m still not clear on what you’re doing with the values and what you need the index for (besides aligning values, but that’s already done).
Edit: Once you get your values into columns you can do whatever you need with them - get values, check conditions, etc. - then manage them as necessary because the lists match (each index represents a row).
Yeah, I know how to pull out a sublist of values IF i know the index of the column or row. However, I need to search the list of lists for a value and be able to report the index value of the sublist it is found in so that I can do what you are doing above. Because, since I there is no way to know which sublist (index value) the search value will be in I cant use the method you are using. To use your example above, I would need to query your first list for the value 400 and have it return, tell me, that it is index value 3, in sublist 2. That way I can query the value of, index value 3 in sublist 0. the index value being the Row number in excel, and the sublist being the column value. The Row value will change dramtically with every iteration, so I need dynamo to tell me what it is, and use that value to find the other value I need.
No, are not following me. To use your example above, lets say I want to use value 3 in column 2 and value 2 in column 3. The dynamo script would search the two lists and determine that index value 2 contains the value of 3 in column 2 and value 2 in column 3. this means that the index value that I am looking for is 2. Then the dynamo script would search Column 1 for index value 2 and report back or provide the answer = 2.
