1

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.

Click here for Sample Data

Sample

1 Answer 1

0
  • paste in cell B3 and drag down:

=ARRAYFORMULA(IFERROR(QUERY(TRANSPOSE(IF(D3:3>0, TO_TEXT($D$1:$1), )), 
 "where Col1 is not null limit 1", 0)))

  • paste in cell C3 and drag down:

=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)))

0

6
  • Thank you so much! This worked perfectly. Any chance you might be able to explain how that formula works so I can understand? Also, is it possible to get the columns to fill in using an array formula as opposed to dragging? I have the names and the visits/months automatically calculated and updated from another sheet using arrayformulas. Either way, thanks again!
    – SM1
    Mar 23, 2019 at 15:36
  • sure! 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.
    – user0
    Mar 23, 2019 at 17:58
  • for the last one we use 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), )
    – user0
    Mar 23, 2019 at 17:58
  • You sir are a wizard! Thank you again. Is there any way to make the column auto populate using an array formula or is a manual drag the only way?
    – SM1
    Mar 24, 2019 at 7:25
  • automated arrayformula for these two formulas won't be possible because each row looks for a different column
    – user0
    Mar 24, 2019 at 11:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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