0

I recently developed code to find a keyword I input and it finds the keyword by iterating over the rows of an excel sheet, but when I find that keyword in the row how do I move horizontally and get the value from a column cell in the very row I found the keyword in?

Nimantha
  • 6,405
  • 6
  • 28
  • 69
violette
  • 35
  • 7

2 Answers2

0

You have not given much information here as to what library you are using, which would be essential to give you any syntax hints. Openpyxl? Pandas?

So I can just help you with some pointers for your code:

You have a function that iterated over the rows.
You should write the function in a way that it keeps track of which row its checking, and then, when it finds the keyword, it should return the row number. Perhaps with the enumerate function. Or with a simple counter

counter = 1
for cell in column:
    if keyword = cell.value:
        return counter
    else:
        counter += 1

With the row number, all you need to do is to create a reference to the cell in which the value is, then add 1 column to the reference.

For example, if the reference for the keyword is (1, 2) (column, row) then you do a transformation like

keyword_ref = (1, 2)
value_ref = (keyword_ref[0] + 1, keyword_ref[1])

Finally you return the value in the value_ref.

Nimantha
  • 6,405
  • 6
  • 28
  • 69
iansedano
  • 6,169
  • 2
  • 12
  • 24
  • i am using pyopenxl – violette May 27 '20 at 16:42
  • basically the code finds a specific keyword inside the excel sheet by iterating over the rows using iter_rows(), Once the loop stops on the row that it finds the keyword in then how do I get another cell in the 3rd or 4th column horizontal in the same row the. keyword was found in? – violette May 27 '20 at 16:43
0

A simple way to do this is to grab the value from a cell in a different column as you iterate over each row. Below, I'm assuming you are working from an existing workbook, which you can load by declaring the filepath variable.

import openpyxl

wb = openpyxl.load_workbook(filepath)
ws = wb.active


# Iterate each row of the spreadsheet
for row in ws.iter_rows():

    # Check if the value in column A is equal to variable "target"
    if row[0].value == target:

        # If there is a match, output is value in same row from column B
        output = row[1].value

In this example, you iterate through each row to check if the value in column A is equal to the target variable. If so, you can then retrieve any other value on that row by changing the index for the output variable.

Column index values run from 0 on, so row[0].value would be the value in the row for column A, row[1].value is the value in the row for column B, and so forth.

Andrew Teal
  • 11
  • 4