Baffling Duplicate & Unique value Question

[splreece]

Afternoon all,

I have a challenge to identify the changes over time with customers moving through our sales process. The data looks like this:

Week 1 Week 2 Week 3 Week 4
Customer Customer Customer Customer
2621892115 2577549317 2633689381 2633689381
2626821250 2583418722 2633698086 2633698086
2629445370 2589460465 2709502534 2709502534
2630895976 2591805032 2713387475 2713387475

(each number above is a unique customer number)

Essentially I need a way to find out:
- how many new customers have hit stage 1 (and 2,3,4)
- how many stage 1 customers have moved to stage 2 (and 2-3, 3-4 and so on)
- how many stage 1 customers have stayed at stage 1 (and 2,3,4 and so on)
- how many customers disappear altogether at each stage

and so on..


I have no starting point except to look for duplicates in each column but that is impossible to automate (for user manipulation or report updating).


Does anyone have any good starting points or know of the terms I need to research to start building this?

Your time is much appreciated.

 

[splreece]

apologies for the outburst, but pivottables are fantastic but my limited vba knowledge is showing when trying to manipulate pivot tables through vba

 

[splreece]

Thanks again.

Below is an extract of the full report I get weekly...

Ultimately I am tasked with looking for why we are seeing an increase in the length of time it takes for sales to get through the whole process

So I am looking to identify if there are increases in particular stages or with particular customer types or order types or engine adaptations...etc


As you can see below, there is sales movement for customer 55432 and 01321.

An all singing all dancing user interface to manipulate figures on the fly would (I assume) be a full on project with quite substantial costs involved so I am trying to break it down to manageable chunks as there is no budget for improvement so I am trying to limit what I ask for as I don't want to upset all you very generous advisors.

So I am hoping to pull figures (or i will take advise on alternative ways of reporting):

New (unique- not on any other stage prior)
Static (no movement)
Progression (Moving stages)
Dropped off (was on but disappeared)


This way when I look at further reports I can look to see if a similar picture is occurring over time.

(I hope this makes sense)


Report for Sales Channel 01/06/2015
[table="width: 100%"]
[tr]
[td]Location[/td]
[td]Office[/td]
[td]Sale Stage[/td]
[td]Volume[/td]
[td]Engine Adapted[/td]
[td]Owner[/td]
[td]Sales Offer[/td]
[td]Engine Torque[/td]
[td]Overview[/td]
[td]Volume [/td]
[td]Type[/td]
[td]SalesChannel[/td]
[td]Team [/td]
[td]Cust No[/td]
[td]Sales Date [/td]
[td]Sales Contact[/td]
[td]Processing Time[/td]
[/tr]
[tr]
[td]Denmark[/td]
[td]Peterborough[/td]
[td]2[/td]
[td]2[/td]
[td]Y[/td]
[td]Service[/td]
[td]X[/td]
[td]DT[/td]
[td]Oil Refinement[/td]
[td]7[/td]
[td]N[/td]
[td]3[/td]
[td]14[/td]
[td]55432[/td]
[td]01/07/2015 00:00:00 [/td]
[td]Mr Smith[/td]
[td]11[/td]
[/tr]
[tr]
[td]Edinburgh[/td]
[td]Ireland[/td]
[td]4[/td]
[td]3[/td]
[td]Y[/td]
[td]Service[/td]
[td]D[/td]
[td]UD[/td]
[td]Sump adaptation[/td]
[td]9[/td]
[td]B[/td]
[td]3[/td]
[td]4[/td]
[td]33944[/td]
[td]01/07/2015 00:00:00 [/td]
[td]Mr Smith[/td]
[td]29[/td]
[/tr]

[tr]
[td]London[/td]
[td]Sunderland[/td]
[td]1[/td]
[td]2[/td]
[td]N[/td]
[td]Sales[/td]
[td]A[/td]
[td]DD[/td]
[td]Sump adaptation[/td]
[td]2[/td]
[td]D[/td]
[td]1[/td]
[td]7[/td]
[td]01321[/td]
[td]05/07/2015 00:00:00 [/td]
[td]Mr Smith[/td]
[td]14[/td]
[/tr]
[/table]

Followed by the next report

Report for Sales Channel 07/06/2015
[table="width: 100%"]
[tr]
[td]Location[/td]
[td]Office[/td]
[td]Sale Stage[/td]
[td]Volume[/td]
[td]Engine Adapted[/td]
[td]Owner[/td]
[td]Sales Offer[/td]
[td]Engine Torque[/td]
[td]Overview[/td]
[td]Volume [/td]
[td]Type[/td]
[td]SalesChannel[/td]
[td]Team [/td]
[td]Cust No[/td]
[td]Sales Date [/td]
[td]Sales Contact[/td]
[td]Processing Time[/td]
[/tr]
[tr]
[td]Denmark[/td]
[td]Peterborough[/td]
[td]4[/td]
[td]2[/td]
[td]Y[/td]
[td]Service[/td]
[td]X[/td]
[td]DT[/td]
[td]Oil Refinement[/td]
[td]7[/td]
[td]N[/td]
[td]3[/td]
[td]14[/td]
[td]55432[/td]
[td]01/07/2015 00:00:00 [/td]
[td]Mr Smith[/td]
[td]11[/td]
[/tr]
[tr]
[td]Edinburgh[/td]
[td]Ireland[/td]
[td]4[/td]
[td]3[/td]
[td]Y[/td]
[td]Service[/td]
[td]D[/td]
[td]UD[/td]
[td]Sump adaptation[/td]
[td]9[/td]
[td]B[/td]
[td]3[/td]
[td]4[/td]
[td]33944[/td]
[td]01/07/2015 00:00:00 [/td]
[td]Mr Smith[/td]
[td]29[/td]
[/tr]

[tr]
[td]London[/td]
[td]Sunderland[/td]
[td]2[/td]
[td]2[/td]
[td]N[/td]
[td]Sales[/td]
[td]A[/td]
[td]DD[/td]
[td]Sump adaptation[/td]
[td]2[/td]
[td]D[/td]
[td]1[/td]
[td]7[/td]
[td]01321[/td]
[td]05/07/2015 00:00:00 [/td]
[td]Mr Smith[/td]
[td]14[/td]
[/tr]
[/table]

 

[MickG]

Based on your Example (monthly) Data and it layout (sheet1), the attached file shows on sheet 2 a list of all customers and the stages numbers and related dates.
You can see from this the position/holdups for each customer/Job.
Please comment on any updates improvements you can think of , showing an example of the any modified results.
https://app.box.com/s/bzj68lvk2kgsl5vgohvrhcijlzp91qmh

 

[splreece]

thats great work.... really great.

Is it possible to refer to the stage as freetext (i,e so i can change the stage 1,2,3 etc for formal process names (this can change over time so the ability to amend these stage terms would be ideal)).

 

[MickG]

Remove parts shown in red, then the results you will be left with, will be the actual numbes from the data :-

If Not .Exists(R.Value) Then
            n = n + 1
            Ray(n, 1) = R.Value: Ray(n, 2) = [B][COLOR=#ff0000]"StageNo " &[/COLOR][/B] R.Offset(, -11)
            .Add R.Value, n
        Else
            Ray(.Item(R.Value), col) = [B][COLOR=#ff0000]"StageNo " &[/COLOR][/B] R.Offset(, -11)
        End If