Search code examples
arraysgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query

Pulling text and count of specific text from a different sheet using importrange

I have a Google sheet containing a 'Notes' column and i would like to import the notes from those cells into another sheet and count how many times a certain word or phrase occurs. I would like for the words/phrases to appear in the same cell separated by a comma. So for example a cell in the import sheet would look like "1 Initiated, 3 Completed, 2 Needs more information, 1 Pending". There is a set number of words/phrases which could be in the Notes column.

Import From Import To

Solution

  • try:

    =ARRAYFORMULA(IFNA(VLOOKUP(INDIRECT("A3:A"&COUNTA(A3:A)+2), 
 SPLIT(REGEXREPLACE(REGEXREPLACE(TRIM(TRANSPOSE(QUERY(IF(""<>
 QUERY(IMPORTRANGE("1dnpQQX0YA-_BCxmcfpWYhkLupLPVarP_-C7RSKdpvJ0", "Sheet1!A2:B"),
 "select count(Col2) where Col2 is not null group by Col2 pivot Col1"), 
 QUERY(IMPORTRANGE("1dnpQQX0YA-_BCxmcfpWYhkLupLPVarP_-C7RSKdpvJ0", "Sheet1!A2:B"),
 "select count(Col2) where Col2 is not null group by Col2 pivot Col1")&" "&INDEX(
 QUERY(IMPORTRANGE("1dnpQQX0YA-_BCxmcfpWYhkLupLPVarP_-C7RSKdpvJ0", "Sheet1!A2:B"),
 "select Col2,count(Col2) where Col2 is not null group by Col2 pivot Col1"),,1)&",", )
 ,,99^99))), " , ", "♦"), ",$", ), "♦"), 2, 0), "Not Data"))

    0