Next stage calculation using current stage and certain criteria

[Adithya_S]

Hello All,

I have a call center data, and we have customers with certain stage like stage 1,2,3,4,5

and all the stages has certain criteria to pass on to next stage, refer below criteria,

Stage	Outcome 1	Outcome 2	Outcome 3	Outcome 4
Stage 1	DC PIN setup	IB registration	MB registration
Stage 2	Salary mandate	UPI registration	DC txn activation	Family account
Stage 3	IB txn activation	UPI txn activation
Stage 4	Billpay setup	CC cross-sell
Stage 5	Demat cross-sell	Referral benefits

Now for example user is in stage1 then he have to satisfy below criteria to move to next stage i.e stage 2

DC PIN setup

IB registration

MB registration

and same for all the stages,

Please help me out with a formula that can do it,

Please let me know if you need anymore insights.

Thanks in advance.

[Emil_Kos]

Hi @Adithya_S,

I suggest using combination of the Concatiation + Find and replace tool. 

This will work if the outcomes are always in the same sequence. If that would not be the case we would need to add a transpose tool first and sort the data in the correct way.

Please let me know if this was helpful for you.

I wouldn't advise using formula tool only as this will be problematic for you to maintain. 

[Qiu]

@Adithya_S 
Can you provide a sample of call center data?

[Adithya_S]

Hi Qiu,

Sample file attached.

[Adithya_S]

Hello Qiu,

Please find all the files related,

We need to assume current stage nd create a dummy column, and then we need to find if the customer is satisfying all the criteria in current stage so tht he will move to next stage,

I need a logic to perform this,

Please help me out

[Qiu]

@Adithya_S 
You said all the related files, but I can only see one Excel file.

Please confirm.

[Adithya_S]

Hi Qiu,

Sorry for the confusion,

Here are the files.

[Adithya_S]

Hello Qiu,

Any update?