Forum Discussion
How to auto-populate all values of the row in a new excel sheet if two keywords are found in the row
Hello all,
I want to know if it’s possible to auto populate all data present in a row in a new excel sheet if the row from which I need to copy has two keywords.
for instance if the workbook from which I am copying is having keywords like CARD and complete, how can I make sure that the whole row gets copied to a new sheet as soon as these words are triggered in the row when someone inputs these words in the sheet from which I want to copy.
also is it possible to set it up for multiple sheets. For instance I have a folder which has multiple excel spreadsheets, is it possible to pull this data from multiple spreadsheets to a single sheet if these keywords are found within those sheets in the folder.
thank you in advance for the help it would be a great help please.
- Share some data to work with and show the expected result.
pull this data from multiple spreadsheets to a single sheet
https://b23.tv/gyWBYHl
if these keywords are found
apply sql where to filter. - =LET(tables,XFD1:XFD5,
DROP(REDUCE("",SEQUENCE(ROWS(XFD1:XFD5)),
LAMBDA(x,y,VSTACK(x,IFERROR(FILTER(INDIRECT("'"&INDEX(tables,y,)&"'!A2:F15"),
BYROW(INDIRECT("'"&INDEX(tables,y,)&"'!A2:F15"),
LAMBDA(z,(COUNTIF(z,"Card")=1)*(COUNTIF(z,"complete")=1)))),{"".""."".""."".""})))),1))Let's say the sheetnames are in range XFD1:XFD5 e.g. sheet1, sheet2, sheet3, sheet4 and sheet5. This formula performs an exact match in each sheet and checks in each row of range A2:F15 if the keywords Card and complete are found. If both keywords are found once within a row the formula returns the row in the output sheet. If within a certain sheet there isn't a row which contains both keywords the formula returns an empty row. If you have Microsoft 365, you can use the FILTER function to return rows from a worksheet that satisfy some conditions, and VSTACK to combine the result of FILTER from multiple worksheets.
If you'd like help with that, please provide more detailed information. For example: in which column (or columns) should we look for the keywords?
