Google Spreadsheet, How to search an entire sheet for same matches and return values located to the right of them?

user_14644246 0
Viewed 77

In Sheet1 I have many values that are the same, for example the name "boland1" exists in many places in Sheet1 and although the content is stuctured, the positions vary. Each "boland1" have different values to the right of them, which is what I want to extract.

I am looking for a formula that searches through that entire first sheet and returns the values located to the right of each "boland1". I do not want the formula to choose from specific columns, I already know how to do that. I want the entire sheet to be the lookup range, is this possible?

Here is a spreadsheet example, look at the sheet names for info and also look at Sheet2 for more clarification on what I am looking for: https://docs.google.com/spreadsheets/d/1W8uVmyLS9O907v8Ony-rfbhDuHJqAqsP_eH3Gj1PjUg/edit?usp=sharing

Here are some pictures from the spreadsheet for the people that don't want to click on links: enter image description here enter image description here

google-query-language google-sheets-formula google-sheets filter sorting
2 Answers

use:

=QUERY({
 FLATTEN(FILTER(Sheet1!A2:1000; MOD(COLUMN(Sheet1!A2:2)-1; 6)=0))\ 
 FLATTEN(FILTER(Sheet1!A2:1000; MOD(COLUMN(Sheet1!A2:2)-2; 6)=0))\ 
 FLATTEN(FILTER(Sheet1!A2:1000; MOD(COLUMN(Sheet1!A2:2)-3; 6)=0))\ 
 FLATTEN(FILTER(Sheet1!A2:1000; MOD(COLUMN(Sheet1!A2:2)-4; 6)=0))\ 
 FLATTEN(FILTER(Sheet1!A2:1000; MOD(COLUMN(Sheet1!A2:2)-5; 6)=0))}; 
 "where Col1 = 'Boland1'"; 0)

enter image description here

update:

=QUERY(SORT({
 FLATTEN(FILTER(Sheet1!A2:1000; MOD(COLUMN(Sheet1!A2:2)-1; 6)=0))\
 FLATTEN(FILTER(Sheet1!A2:1000; MOD(COLUMN(Sheet1!A2:2)-2; 6)=0))\ 
 FLATTEN(FILTER(Sheet1!A2:1000; MOD(COLUMN(Sheet1!A2:2)-3; 6)=0))\ 
 FLATTEN(FILTER(Sheet1!A2:1000; MOD(COLUMN(Sheet1!A2:2)-4; 6)=0))\ 
 FLATTEN(FILTER(Sheet1!A2:1000; MOD(COLUMN(Sheet1!A2:2)-5; 6)=0))}; 
 FLATTEN(FILTER(COLUMN(Sheet1!A2:1000)*SEQUENCE(ROWS(Sheet1!A2:A); 
 COLUMNS(Sheet1!A2:1000); 1; 0); MOD(COLUMN(Sheet1!A2:2)-1; 6)=0)); 1); 
 "where Col1 = 'Boland1'"; 0)
user_56326290

It will make further processing easier if you stack all data into one row-oriented table using player0's recipe, and include the date on each row, like this:

=query( 
  {
    flatten(filter(iferror(Sheet1!A3:9999 / 0; Sheet1!A1:1); not(mod(column(Sheet1!A3:9999) - 1; 6)))) \ 
    flatten(filter(Sheet1!A3:9999; not(mod(column(Sheet1!A3:9999) - 1; 6)))) \ 
    flatten(filter(Sheet1!A3:9999; not(mod(column(Sheet1!A3:9999) - 2; 6)))) \ 
    flatten(filter(Sheet1!A3:9999; not(mod(column(Sheet1!A3:9999) - 3; 6)))) \ 
    flatten(filter(Sheet1!A3:9999; not(mod(column(Sheet1!A3:9999) - 4; 6)))) \ 
    flatten(filter(Sheet1!A3:9999; not(mod(column(Sheet1!A3:9999) - 5; 6)))) 
  }; 
  "where Col2 is not null order by Col1"; 0 
)

Once you have that in place, you can create many kinds of reports easily:

=query(Stacked!A1:F; "where B = 'Boland1' "; 1)

=query(Stacked!A1:F; "select B, count(B) where B is not null group by B pivot E"; 1)

See the new Stacked and Query sheets.

user_130451930
Related