trying to figure out what type of formula to use

[nmss18]

Hello,
In the table below, I have a list of clients and the number of license fees they bought into per month. As soon as I see a value I know that is the first month that client signed up. If there are blank values following any cell with a number, I know that client was deactivated.
So in the table below, I see that Client 2 was added in 2014 June, and client 10 was deactivated after 2013 December.
In cell B6, I need to formulate a formula that just tells me simply if this client was added in this period or not. So in cell B7 next to client 2, I would like to see "ADDED", and next to client 10 in cell B15, I would like to see "LOST". The rest of th cells, I would like to leave blank.
I am thinking that it should be some sort of LOOKUP formula but am not sure.

If anyone, could help, I would appreciate it.
Thanks,
nmss18

( I am using a free online HTML maker, so the quality isnt so great)

		Fiscal Month
End User	Current User Status	2013 July	2013 August	2013 September	2013 October	2013 November	2013 December	2014 January	2014 February	2014 March	2014 April	2014 May	2014 June	2014 July	2014 August	2014 September
Client 1	Active	11	11	11	11	11	11	11	11	11	11	12	12	13	13	12
Client 2	Active												1	1	1	1
Client 3	Active	3	3	4	4	4	4	4	5	5	5	5	5	5	5	5
Client 4	Active	4	4	4	4	4	4	4	4	4	4	3	4	4	4	3
Client 5	Active	8	8	8	8	8	8	8	8	8	8	8	8	8	8	8
Client 6	Active	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6
Client 7	Active	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4
Client 8	Active	1	1	1	1	1	1	1	1	1	1	2	2	2	2	2
Client 9	Active	6	6	6	6	6	6	6	6	6	6	5	5	5	5	5
Client 10	Inactive	5	5	5	3	3	3
Client 11	Active	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5

<tbody>
</tbody>

 

[DKaur]

I'm assuming that you have no completely blank rows. In which case you only need to test the beginning and end of the rows so something like this will tell you if they have been added, lost or added then lost:
=IF(C4="","Added","")&IF(Q4="","Lost","")
I just copied your table into excel and put this formula in column S, feel free to switch the cell references around.

 

[nmss18]

That's really amazing. It works. But why? What is the logic behind this?

Also, there will never be completely blank rows but this wil be a dynamically updating table and the time range may change.
what would you do in this case?
Thanks!

 

[DKaur]

For a dynamically changing table I would have two entry cells above the table where you put the start of your time range in B1, for example, and the end time in C2. (You can setup a formula for it to take the last occupied time range in the row, but for now lets just test the formula below.) Then have a hlookup for these times with an extended table like so:
=if(hlookup($b$1, $d$4:$aa$100, a4, false)= "","Added","")&if(hlookup($c$1, $d$4:$aa$100, a4, false)="","Lost","")
Where d4:aa100 is your data table, column a has a list of numbers that reference the row in the hlookup, so then a3 = 1 and a4 = 2 etc. You may have to tweak the cell references to adjust for where your data is.
Then as you add rows and columns you won't need to adjust the formula until you reach the max (max row being 100 and max column being aa) then you can either extend the hlookups or if you insert the row/column, the hlookup should extend by itself.

I haven't checked this solution since I'm on a train replying from a phone. But hopefully it will work.

 

[nmss18]

I will check this out. Thanks for your help.