Search a particular word in every table data and automatically list it in a column
I'm kinda new with formulas in Google Sheet and was wondering how can I pull this. I have a sheet where I get to list down different tables with different Keyword on a single row. For example, Keyword:Here, or on another table Keyword:There. I wanted to be able to filter all this titles with 'keyword'on it and be able to list it all down in another sheet.
For reference, I have prepared a Practice sheet.
I was only manually adding this together with their data. Means, from one sheet, I'll just put = then refer to the cell I wanted to import the value. I was wondering if we have any formula we can use that even if I add new table with "Keyword:xx" everyday, it'll automatically imported to another sheet.
Solution
You may try:
=iferror(choosecols(filter('Sheet 1'!$27:$27,scan(,'Sheet 1'!$2:$2,lambda(a,c,if(c="",a,c)))=A6),2,3,4))
To pull the whole list automatically:
=reduce(tocol(,1),'Sheet 1'!A2:index('Sheet 1'!2:2,match(,0/('Sheet 1'!4:4<>""))),lambda(a,c,vstack(a,if(c="",tocol(,1),hstack(c,offset(c,25,1,,3))))))
Explanation:
'Sheet 1'!A2:index('Sheet 1'!2:2,match(,0/('Sheet 1'!4:4<>"")))dynamically translates toA2:L2because that's where the table data ends as of now- Now if a cell in row_2 is blank → Do Nothing [
tocol(,1)is a null value withinvstack()] → Otherwise output the values present at a certain offsetoffset(c,25,1,,3) - Look into reduce() explanation here to get a bit more clear picture on what it does
- Check if Cell Contains Month and Compare to Date
- Arrayformula with Query in Google Sheets
- Conditional borders troubleshooting
- Troubleshooting Spreadsheet Form Submit Events Google
- Macro for replacing data and then collating it?
- QUERY Formula referring to the value of a cell rather than a constant piece of text
- Index Function - Use One Formula to Generate the Result List with Two Variables
- google sheets IMPORTXML for currency prices
- How to add plus 5 hour to the copy Cell and past in other with plus time
- How to connect Google Sheets to Database
- Merging Google Sheets formulas into1 to get the same Result
- TypeError: UrlFetchApp.fetch(...).then is not a function
- Using Column Headers with COUNTIF To Add those that Fall Between Dates in Cells
- Using SUMIFS with MAP/LAMBDA
- Deal with line break while using IMPORTXML in Google Sheet
- What is an equivalent for INDEX in ARRAYFORMULA?
- Google Sheet Script - Removing a gap of blank cells within a range to keep a list compact and tidy?
- For loop running only for first element
- How can I use a Custom Function in Formulas across Multiple Spreadsheets?
- How to use a filter inside SortN
- Problems with ShinyApp and Google authentification, when making Public
- Newbie Help: Syntax error: SyntaxError: Unexpected identifier (JavaScript)
- Error in Google App Script when submitting form data to Google Sheets
- Failed to submit form responses from a spreadsheet
- Maps.newDirectionFinder().SetArrive & SetDepart (Google Maps API) in GSheets App Script aren't impacting returned driving times w/ tested scripts
- Check if range is empty and copy data from Range
- How to convert duration of MM:SS to seconds in Google Sheets?
- Conditional formatting comparing cell to cell values in a range
- showModalDialog in SpreadsheetApp Won't Close
- Google Sheets Create Template