status tracking and number of days in status

[rickparmar]

Hi all,

I hope I can explain this properly

I'm after some ideas/formulas which will help me create a status tracker.

Background - customer data will have max 6 status depending on where they are in the life cycle. (all customers have a unique identifier)
Weekly data will be provided with customer data either having the same status as previous week or having an updated status. Some customer could stay in the same status for weeks.
Also this weekly data will add new customers to the list,
Weekly data is provided by another team

Currently I have taken 3 weeks worth of data and put them side by side, with the date and customer status. This works out the number of days the customer has stayed in the status. I have used the following formula to give me the result for the past 3 weeks.

However this is not the best practice as I don't want to keep extending the if formula when new weeks are added.

=IF(K2=G2,SUM(TODAY()-F2),IF(K2=I2,SUM(TODAY()-H2),IF(K2<>I2,SUM(TODAY()-J2))))


I have created a master sheet and keep adding new weeks data side by side
[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Date[/TD]
[TD]Status[/TD]
[TD]Date[/TD]
[TD]Status[/TD]
[TD]Date[/TD]
[TD]Status[/TD]
[TD]Number of Days[/TD]
[/TR]
[TR]
[TD]abc1[/TD]
[TD]31/07/2017[/TD]
[TD]XY[/TD]
[TD]07/07/2017[/TD]
[TD]XY[/TD]
[TD]14/07/2017[/TD]
[TD]ZA[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]abc2[/TD]
[TD]31/07/2017[/TD]
[TD]XY[/TD]
[TD]07/07/2017[/TD]
[TD]ZA[/TD]
[TD]14/07/2017[/TD]
[TD]ZA[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]abc3[/TD]
[TD]31/07/2017[/TD]
[TD]TA[/TD]
[TD]07/07/2017[/TD]
[TD]TA[/TD]
[TD]14/07/2017[/TD]
[TD]TA[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

could someone help and think of any ideas please

Thanks in advance
Rik

 

[mart37]

Re: status tracking and number of days in status - help needed

Make from your table a special Excel-table (Format as Table).
The table grows now automatic when you insert new columns.
If you have new data insert two columns before the last.
Rename the columntitles.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Customer	Date 1	Status 1	Date 2	Status 2	Date 3	Status 3	Date 4	Status 4	Number of Days
2	abc1	31-7-2017	XY	7-7-2017	XY	14-7-2017	ZA	10-8-2017	VB	]"),SUMPRODUCT(--SMALL(((INDIRECT("Table1:)-2)/2&"]]")=INDIRECT("Table1)-2)/2&"]]")))*TRANSPOSE(ROW(INDIRECT("$1:$"&COLUMNS(Table1)-2))),COUNTIF(INDIRECT("Table1:)-2)/2&"]]"),"<>"&INDIRECT("Table1)-2)/2&"]]"))+1))-1)}]7
3	abc2	31-7-2017	XY	7-7-2017	ZA	14-7-2017	ZA	11-8-2017	ZA	41
4	abc3	31-7-2017	TA	7-7-2017	TA	14-7-2017	TA	12-8-2017	VB	5
Sheet

 

[rickparmar]

Re: status tracking and number of days in status - help needed

Hi Thanks for that,

I tried and I got the J2 cell the same,

However there is a slight change to the requirements. Is it possible to do this?

In the end columns I need the number of days each stage took

Hope you can help, thanks

[TABLE="width: 500"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]date[/TD]
[TD]status[/TD]
[TD]date[/TD]
[TD]status[/TD]
[TD]date[/TD]
[TD]status[/TD]
[TD]date[/TD]
[TD]status[/TD]
[TD][/TD]
[TD]XY[/TD]
[TD]ZA[/TD]
[TD]TA[/TD]
[TD]DE[/TD]
[TD]WE[/TD]
[TD]FE[/TD]
[/TR]
[TR]
[TD]abc1[/TD]
[TD]31/07/2017[/TD]
[TD]XY[/TD]
[TD]07/08/2017[/TD]
[TD]XY[/TD]
[TD]14/08/2017[/TD]
[TD]XY[/TD]
[TD]21/08/2017[/TD]
[TD]ZA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]abc2[/TD]
[TD]31/07/2017[/TD]
[TD]XY[/TD]
[TD]07/08/2017[/TD]
[TD]ZA[/TD]
[TD]14/08/2017[/TD]
[TD]XY[/TD]
[TD]21/08/2017[/TD]
[TD]XY[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]abc3[/TD]
[TD]31/07/2017[/TD]
[TD]ZA[/TD]
[TD]07/08/2017[/TD]
[TD]WE[/TD]
[TD]14/08/2017[/TD]
[TD]WE[/TD]
[TD]21/08/2017[/TD]
[TD]WE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]abc4[/TD]
[TD]31/07/2017[/TD]
[TD]DE[/TD]
[TD]07/08/2017[/TD]
[TD]WE[/TD]
[TD]14/08/2017[/TD]
[TD]FE[/TD]
[TD]21/08/2017[/TD]
[TD]FE[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[mart37]

Re: status tracking and number of days in status - help needed

is the data form customer abc2 correct?
can a status return in the future?

how do you calc the last column (FE)?

can you give the results?