Forum Discussion

jiwhiteff's avatar
jiwhiteff
Copper Contributor
Jun 03, 2022

Find row in Cloud Excel table matching text

I have 2 identifiers in rows in Excel and a block of text.  I want get the row of data in Excel that has the identifiers in the text. For example, if I have text that says, "I love peach cobbler and pork dumplings," I would return row 3 from the table below.  Currently, I'm running an Office Script from Power Automate to iterate through the rows looking for the first criteria.  If that's found, it checks for the second criteria.  If it finds both, break the loop. Is there a way to use table autofilters or something else to do this more efficiently?

 

Row #Favorite FruitFavorite MeatFavorite Sport
1AppleSteakSoccer
2CherryChickenSwimming
3PeachPorkSkiing
Excel for web
Office Scripts

4 Replies

Sort By
    • jiwhiteff's avatar
      jiwhiteff
      Copper Contributor
      Jun 03, 2022
      Is there a way to do that in Typescript, not Excel formulas? I have multiple calls running at the same time. I don't want to populate temporary cells or tables because that will slow things down considerably.
      • SergeiBaklan's avatar
        SergeiBaklan
        MVP
        Jun 03, 2022

        jiwhiteff 

        Sorry, I'm not very familiar with the ExcelScript. As a minimum you may

        function main(workbook: ExcelScript.Workbook) {
	let selectedSheet = workbook.getActiveWorksheet();
	let formula = "=REDUCE($C$1, SEQUENCE(ROWS(data)), LAMBDA(a,v, IF(SUM(--ISNUMBER(SEARCH( INDEX( data, v, {1,2} ), $A1))) = 2, a & INDEX(data, v, 3),a)))";
	selectedSheet.getRange("D1").setFormulaLocal(formula);
}

        I took C1 as blank cell, don't know how to add empty string within text.

        Not sure which temporary cells do you mean, if where the text is it could be wrapped by another lambda with text as parameter. 

         

Resources