-1

Here's the formula that I'm starting with:

=arrayformula(if(isblank(H1:1),"",query(A2:C,"Select C where A = "&F1&" and B = '"&$H$1:$1&"'")))

However, it's not referencing the date value in the query the way I am expecting it to, based off of the query() documentation. It's only referencing the value in H1. Current State

I'm trying to figure out how to best return results from a dataset, based off of a value in a row.

In the past, I've used an arrayformula(vlookup()) to do this, but I'm needing for all results to show under their corresponding header, where it matches 2 criteria:

  1. The store number in F1
  2. The day in the dataset in H1:1 (if not null)

Any help/guidance you all could provide would be greatly appreciated.

Below is a .gif of what I'm trying to accomplish, and how the desired output would look. I'm manually copy/pasting the values from C2:C to show where they would optimally go. IdealState

Please feel free to reference this Google Sheet, which has the same data layout and formulas highlighted in yellow.

Thanks!

Update: below is the query formula I'm using to get the unique dates based off of a F1's value:

=transpose(unique(query(A2:C,"Select B where A = "&F1&" Order by B Asc")))
swolfe2
  • 429
  • 1
  • 6
  • 24

1 Answers1

1

Note to anyone coming in the future:

I was able to resolve my needs with the following scalable formula.

=ARRAYFORMULA(TRANSPOSE(SPLIT(REGEXREPLACE(TRANSPOSE(QUERY(QUERY(A2:C10," Select max(C) where A="&F1&" group by C pivot B")&"☠",,2^99)),"((?:☠ )+)",""),"☠")))
swolfe2
  • 429
  • 1
  • 6
  • 24