How to auto-populate all values of the row in a new excel sheet if two keywords are found in the row

[stormShadow1409]

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.

[#2]

@stormShadow1409 

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?

[#3]

@stormShadow1409 

=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.

[#4]

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.