Jun 03 2022 09:49 AM
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 Fruit | Favorite Meat | Favorite Sport |
1 | Apple | Steak | Soccer |
2 | Cherry | Chicken | Swimming |
3 | Peach | Pork | Skiing |
Jun 03 2022 11:01 AM
Jun 03 2022 11:22 AM
Jun 03 2022 01:15 PM
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.
Jun 06 2022 12:08 PM