Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this community
I have a sheet with client names (Column A) and the number of visits to the business by mm-yyyy (Columns B - AC). I'm trying to find the first month that they visited the business and the last month we've seen them.
I have attached a screenshot and I have also published a small section of the sheet.
I have been struggling with this for a week and have gotten nowhere. I would greatly appreciate any help.
=ARRAYFORMULA(IFERROR(QUERY(TRANSPOSE(IF(D3:3>0, TO_TEXT($D$1:$1), )),
"where Col1 is not null limit 1", 0)))
=ARRAYFORMULA(IFERROR(QUERY(TRANSPOSE(IF(D3:3>0, TO_TEXT($D$1:$1), )),
"where Col1 is not null limit 1 offset "&(COUNTIF(D3:3, ">0")-1), 0)))
IF(D3:3>0 finds all values that are more than zero in the 3rd row and gets them replaced by the header values Jun-2017. header values are kinda a valid dates so any formula will auto-convert them into datevalue (44234) - to avoid this conversion we use TO_TEXT so visually it will stay as is. next this table IF(D3:3>0, TO_TEXT($D$1:$1), ) is rotated with TRANSPOSE so columns become rows and vice versa. this is then passed to the QUERY where we get rid of the empty cells is not null and trim the output to show only 1 result (first one) with limit 1.
offset "&(COUNTIF(D3:3, ">0")-1) which will again look for all values in the row bigger than 0 and count them up and as correction for limit 1 we add -1 - which will return us the last value from the row. last 0 is for query to avoid weird stuff. IFFERROR to null all possible errors and finally ARRAYFORMULA is there only for this to work: IF(D3:3>0, TO_TEXT($D$1:$1), )