How can I search row in excel 365

Questions & Answers
1

Hi i try to do a automate update a report in excel 365 but i cant update a row if a cant search the row I want to update

2

Hi. You need the row id before update it. So you need to a search for the row and later do the update

Donā€™t hesitate to contact us if you require further assistance.

Cheers
//HFBR

1 Like
3

Thank you.
The problem is that, I donĀ“t know how to search for a ID Row in excel 365.
In google sheets have the option but in Excel 365 donĀ“t

image
image1368Ɨ1152 64.1 KB

4

Try use list the Worksheet rows module and filter the result with your term to search.

Donā€™t hesitate to contact us if you require further assistance.

Cheers
//HFBR

1 Like
5

How do you ā€œfilter the resultā€?

6

Here is how you can do thisā€¦

Microsoft 365 Excel List Worksheet Rows module.
The output will be one bundle for each row.

Example, my output has 10 bundles, shown here are three of them:
image

I happen to be looking for Store Number = 42.
Store Number is my column A heading.

In my filter after this module (the wrench icon on the connection between two modules), I have it set up like this:

image
image1273Ɨ453 67.1 KB

This says filter out only the bundles where the Value for (Column A) Store Number = 42.
Ideally, the data in this column are unique so only 1 bundle should pass through the filter.

Set up as many filters as you need to ensure only 1 bundle will be filtered. Otherwise, the modules that follow will all run once for each bundle.

On the module after Excel 365, I need the Row ID from the bundle, which in this case is 4:

image
image1055Ɨ315 47.6 KB

image
image801Ɨ376 33.1 KB

The screenshot shows Row ID: 2 because thatā€™s just the sample data the Excel 365 module generated the last time it ran.

2 Likes
7

Thank you so much for clarifying! So does that mean if my spreadsheet has thousands of rows, I should change the limit to something high like 10k? Screenshot: Markup 2023-08-30 at 12.04.52.png - Droplr

8

Sorry this ended up being a very long-winded responseā€¦

Iā€™ve never tried with that many rows.
I tried leaving limit blank and it returned all 247 of my rows, I just donā€™t know what the limit is.

I donā€™t think it will even allow you to go that high, but even if it did, you may run into a lot of timeout errors while Make tries to get all those rows, pages at a time.
Make will only get so many rows per page, and so many pages per overall request.

For Excel, it looks like the recommendation is to use filtering to find what youā€™re looking for, and this takes several Ops to accomplish since you have to clear the filter, set a new filter, read the rows, clear the filter again, etcā€¦

Hereā€™s another option you might tryā€¦

  • In your Workbook, create a new Sheet, letā€™s call it something like ValueLookup.
  • In ValueLookup Sheet, create a formula in Cell B1, with a MATCH() function.
  • =MATCH(A1,Sheet1!A:A,0) ā† Update Sheet1 to match your main sheet name
  • In Cell A1, enter the value which you are searching for.
  • The result of the MATCH() function will give you either #N/A if it couldnā€™t find the value, or the number of the row in which it is located.
  • Back in Make, use a Microsoft 365 Excel Update a Worksheet Row module to update cell A1 in the ValueLookup sheet. When it updates, the formula will update and the output bundle will give you either the row that value is in, or the #N/A.

As you can see here, I used the module to update cell A1 to ā€œ8940ā€ which is what Iā€™m looking for in my main Sheet (Sheet1), then cell B1 immediately updated with 84, which is the row itā€™s on.

image

I would go with this since it makes Excel do most of the work.
Plus, itā€™s only one Op.
Just be sure to set Calculation Options to Automatic, otherwise youā€™ll have to force a re-calculation, which you could probably do with a simple API call as well, if you want to be safe.

image

Thereā€™s even a way to run a MATCH() function through the API if you feel like going that route.

3 Likes