# Report the date/column header for the first and last non-zero entries for each client/row

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

## 1 Answer

• 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)))``````

• 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`. 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), )` 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 Mar 24, 2019 at 11:03