Search for a value in "column A" of Excel and read the corresponding value in "column B"

Hi All, I’m a beginner in Dynamo, trying to create a program to size pipes based on the fixture units. Could you please tell me which node to be used for searching for a value in “column A” of Excel and read the corresponding value in “column B”…?

Thanks in advance… :slight_smile:

First of all: you’re going to want to simply load in the excel sheet by using a File Path node and attaching it to a Excel.ReadFromFile node.
With that you simply load in the entire excel page. After you’ve done that it might be the moment to start looking how to deconstruct it and read out the right values.

1 Like

Thank you @PauLtus:slight_smile:

I did the loading of the excel file. What I want to know is how to scan that Excel file and find the required value.

Could you show me what you currently have (a Watch node is very useful for that) and also show me and what do you want to search for in Column A?

1 Like

Hi, you need to get to know with the basics of looping in python. Try out this video. It also explains the basics of Revit API and working with python nodes in dynamo.

For this particular case I can help you with a prepared example. Study it hard and watch the video. It will help you much in future.

val = IN[0]
A = IN[1]
B = IN[2]
new = []
for i in range(len(val)):
    if val[i] in A:
        n = A.index(val[i])
        new.append(B[n])
    else:
        new.append('Not in the list')
OUT = new

To use this script create a new “python script” node with 3 input slots. The first one is you values. The second one is column “A”. The third one is column “B”. This script reads the values and looks them up in column “A”. If the element is in the list, it takes the corresponding value from the column “B”. If not - the script returns string “Not in the list”.

Welcome to our community!))) And a piece of advice for the future: we don’t simply ask how to do this or that here. Firstly we try to create something, then we share our script with others and ask about the problems and errors that we have. Good luck!

1 Like

This can be done with a simple FilterByBoolMask. Get your excel data into Column A values and Column B values. Use whatever conditional statement you’re using to check Column A. This will return a True/False value for each item. Use the True/False values as your mask for Column B.

2 Likes

I agree with @Nick_Boyts - no need to go python on this exercise as it’s fairly straightforward with OOTB content and some lacing. A “Dynamo 201” exercise.

1 Like

Hi @PauLtus this is what I have now.

I’m trying to read from this Excel sheet.

For example for a Fixture Units of 11 I’m trying to select Pipe Size 22

Thanks & Regards

Thank you @architect.bim.

I’m not sure where I’m going wrong. But this is what i get when I run the script.


I’m trying hard to learn python as I’m new to it. :slight_smile:

Thanks & Regards.

This would be very easy to set up as a dictionary. Column A = keys. Column B = values.

1 Like


Do you want it like so?

1 Like

2 Likes

Tried this, but for me its showing value null :frowning:

Well…
Have you tried my method.:upside_down_face:

Your values are probably different types. Use an Object.Type node to make sure your values are all numbers.

1 Like

yea I did… it’s working :slight_smile:

Only thing is first 3 values are wrong… It should have returned 15 instead of 22 :frowning:

That’s an easy fix, the reason it worked for me and not for you is because I still have “Size” in my list, the index 6 does not mean the sixth value on the list, but the value with index 6, so that’s the seventh value. You could simply add a code block after the fixture units subtracting 1 of all of these values (edit: that’s the indices, so the lower list).

1 Like

Its working @PauLtus …!!! thanks a lot…!!! :smiley:

Thank you guys @Nick_Boyts @architect.bim @JacobSmall …!!!

1 Like

In my case the @Nick_Boyts method worked perfectly!, thank you guys!!!

1 Like