0

I have 2 sheets : shot and deadline.

https://docs.google.com/spreadsheets/d/1drNUQVtAxd0HU4QCVSKJSsKHoanE735n8HsjkYpYiGs/edit?usp=sharing

This is my shot sheet :

enter image description here

and this is the deadline sheet : enter image description here

So in the deadline sheet, it's based on the dates. It will pull the shotid from shot sheet.

in A2 of deadline sheet , i use :

=ifna( filter(shot!$A$2:$A, shot!$B$2:$B=A$1) ,)

Then i copy the formula to B2:2. The image2 above is the ideal output i'm looking for. The formula works fine, but i want to make a single arrayformula().

My first trial to use arrayformula() with the previous filter() :

=arrayformula( ifna( filter(shot!$A$2:$A, shot!$B$2:$B=A$1:$1) ,))

and it returns error. Filter() doesn't work together with arrayformula. So i second trial is using vlookup :

=arrayformula( ifna( vlookup( A$1:$1,{ shot!$B$2:$B, shot!$A$2:$A },2,0) ,))

it will give me only 1 output per column. The vlookup only find the first match : enter image description here

Can anyone help me ? is it possible to use arrayformula() for this case ?

2
  • Consider sharing a publicly editable sample spreadsheet with realistic-looking data, and showing your hand-entered expected results there. Jul 20 at 7:24
  • Sorry forgot the link, I've included the link in my post. Also here : docs.google.com/spreadsheets/d/… .The expected result is there in the deadline sheet.
    – andio
    Jul 20 at 7:43
1

Use this query-pivot-countifs pattern to get a list of dates in column B and the shots in column A associated with each date:

=arrayformula( 
  transpose(
    query( 
      query( 
        { 
          A1:B, 
          countifs(B1:B, B1:B, row(B1:B), "<=" & row(B1:B)) 
        }, 
        "select Col2, max(Col1) 
         where Col2 is not null 
         group by Col2 
         pivot Col3", 
        1 
      ), 
      "offset 1", 
      0 
    ) 
  ) 
)

Note that the formula will only list dates that actually appear in the data. To get a sparse result, match the output of the formula against your list of dates with an arrayformula(iferror(vlookup(row()))) pattern, like this:

=arrayformula( 
  iferror( 
    vlookup( 
      A1:1, 
      query( 
        query( 
          { 
            data!A1:B, 
            countifs(data!B1:B, data!B1:B, row(data!B1:B), "<=" & row(data!B1:B)) 
          }, 
          "select Col2, max(Col1) 
           where Col2 is not null 
           group by Col2 
           pivot Col3", 
          1 
        ), 
        "offset 1", 
        0 
      ), 
      row(data!A2:A), 
      false 
    ) 
  ) 
)
4
  • sorry can you revisit my post. I've added the link to my sheet. I can't make this script to work
    – andio
    Jul 20 at 7:44
  • Thank you for the sample spreadsheet. The formula seems to work fine. To use it in another sheet, prepend data! to all range references. Edited the answer to show an example of the arrayformula(iferror(vlookup(row()))) pattern. Jul 20 at 7:59
  • Fantastic ! sorry, last time i had typo problem.
    – andio
    Jul 20 at 8:19
  • Btw, if you have time pls check my other post, to do counta() per row , pls check my link : webapps.stackexchange.com/questions/155917/…
    – andio
    Jul 20 at 8:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.