2

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

player0
  • 124,011
  • 12
  • 67
  • 124
Axxi
  • 193
  • 1
  • 10

2 Answers2

2

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)
player0
  • 124,011
  • 12
  • 67
  • 124
  • Wow, gr8 job. I didn't clarify but it's important that the search is made vertically and not horizontally, is there an easy fix to implement that? Thank you so much for taking the time, much appreciated. – Axxi Apr 24 '22 at 23:01
  • @Axxi not sure I understand your vertical request. can you elaborate on it in more detail on why / where the proposed solution is insufficient? – player0 Apr 24 '22 at 23:27
  • Basically I want the result of ”boland1” to be sorted from up to down, your solution sorts them from left to right. Maybe somehow remove the (flatten) function? I will try to explain more in detail tomorrow with pics etc. Thanks again for taking your time. – Axxi Apr 24 '22 at 23:34
  • @Axxi I see. answer updated. try the 2nd fx – player0 Apr 25 '22 at 00:41
  • 1
    Thank you very much @player0, this is a tremendous help to me. – Axxi Apr 25 '22 at 16:08
1

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.

doubleunary
  • 13,842
  • 3
  • 18
  • 51