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.

 

[shobolos]

How do you identify the stages from your data, is it by the rows or the columns?

 

[splreece]

THanks for the reply...

Its by column movment.

So in the example above I can see that:
- 4 unique customers joined at stage 1 and left(didn't continue to stage 2)
- 4 unique customers joined at stage 2 and left (werent in stage 1 so much be unique and didn't appear in stage 3)
- 4 unique customers joined at stage 3 and continued to stage 4 the following week (werent in stage 2 but appear to move to stage 4)


i hope this helps...

I need a way to map out numbers of movement between stage like the above, but do i for 100,000's of transactions (so its impossible to hand count).

 

[shobolos]

So the stages are by the week #? .... stage 1 = week 1, stage 2 = week 2, etc etc

 

[splreece]

So the stages are by the week #? .... stage 1 = week 1, stage 2 = week 2, etc etc

Unfortunately not.... the reports are pulled weekly but customers may stay on stage 1 for weeks then after (X) time move to stage two.. or may move to stage two before the next report is printed.

The core dataset doesn't list dates, it just lists stages so there is no time definition as such. The only time constant are weekly reports.

 

[shobolos]

So how do you identify which stage the customers are on, and can you post a sample of this being stated on a spreadsheet.

 

[splreece]

So how do you identify which stage the customers are on, and can you post a sample of this being stated on a spreadsheet.

So if I look at the sheet data below... I can see that customer 2577549317 has moved from stage 2 to stage 3

Wk1 Report Wk2 Repo Wk3 Report Wk4 Report
stage1 stage2 stage3 stage4
2621892115 2577549317 2633689381 2633689381
2626821250 2583418722 2633698086 2633698086
2629445370 2589460465 2577549317 2709502534
2630895976 2591805032 2713387475 2713387475



In the same report I can see that 2709502534 hasn't appeared before (so is a unique / new entry to stage 4)

2621892115 2577549317 2633689381 2633689381
2626821250 2583418722 2633698086 2633698086
2629445370 2589460465 2577549317 2709502534
2630895976 2591805032 2713387475 2713387475


In the same report I can see that all below in bold have moved from stage 3 to stage 4.
2621892115 2577549317 2633689381 2633689381
2626821250 2583418722 2633698086 2633698086
2629445370 2589460465 2577549317 2709502534
2630895976 2591805032 2713387475 2713387475

The timeframe isn't as relevant for reporting purposes than the stage move.. (hense no timing information on the findings statements above).

 

[MickG]

Try this on your "Trial" Data for Results starting "G1".
Please supply example of your expected results if this is not what you wanted.

[COLOR="Navy"]Sub[/COLOR] MG17Jul35
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, K [COLOR="Navy"]As[/COLOR] Variant, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Ray = Range("a1").CurrentRegion
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] Ac = 1 To UBound(Ray, 2)
    [COLOR="Navy"]For[/COLOR] Rw = 3 To UBound(Ray, 1)
        [COLOR="Navy"]If[/COLOR] Not .Exists(Ray(Rw, Ac)) [COLOR="Navy"]Then[/COLOR]
            .Add Ray(Rw, Ac), Ac
        [COLOR="Navy"]Else[/COLOR]
            .Item(Ray(Rw, Ac)) = .Item(Ray(Rw, Ac)) & "," & Ac
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Rw
[COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Dim[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Range("G1").Value = "Customer": Range("H1").Value = "Started": Range("I1").Value = "Moved To"
c = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    Sp = Split(.Item(K), ",")
    [COLOR="Navy"]If[/COLOR] UBound(Sp) = 0 [COLOR="Navy"]Then[/COLOR]
        c = c + 1
        Cells(c, "G") = K: Cells(c, "H") = "Stage " & Sp(0): Cells(c, "I") = "Not Moved"
    [COLOR="Navy"]Else[/COLOR]
        c = c + 1
        Cells(c, "G") = K: Cells(c, "H") = "Stage " & Sp(0): Cells(c, "I") = "Stage " & Sp(UBound(Sp))
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] With


[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[splreece]

Bloomin Nora.


That is fantastic...

....extract
"
Customer Started Moved To
2686812683 Stage 1 Not Moved
2808308061 Stage 1 Not Moved
2641049771 Stage 1 Stage 2
2727558224 Stage 1 Stage 2
2848650342 Stage 1 Stage 2

"

This is exactly what I was after as I need to count each stage (which can be simple if statements)...

Is it straight forward to simply add additional else statement to take into account stage 3+4 or will that require new programming.

I am putting to together in my head and I need 4 stage looked at so I should be able to run if statements on static data .... ahh glorious... it works on pivot table detail....

Try this on your "Trial" Data for Results starting "G1".
Please supply example of your expected results if this is not what you wanted.

[COLOR="Navy"]Sub[/COLOR] MG17Jul35
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, K [COLOR="Navy"]As[/COLOR] Variant, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Ray = Range("a1").CurrentRegion
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] Ac = 1 To UBound(Ray, 2)
    [COLOR="Navy"]For[/COLOR] Rw = 3 To UBound(Ray, 1)
        [COLOR="Navy"]If[/COLOR] Not .Exists(Ray(Rw, Ac)) [COLOR="Navy"]Then[/COLOR]
            .Add Ray(Rw, Ac), Ac
        [COLOR="Navy"]Else[/COLOR]
            .Item(Ray(Rw, Ac)) = .Item(Ray(Rw, Ac)) & "," & Ac
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Rw
[COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Dim[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Range("G1").Value = "Customer": Range("H1").Value = "Started": Range("I1").Value = "Moved To"
c = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .keys
    Sp = Split(.Item(K), ",")
    [COLOR="Navy"]If[/COLOR] UBound(Sp) = 0 [COLOR="Navy"]Then[/COLOR]
        c = c + 1
        Cells(c, "G") = K: Cells(c, "H") = "Stage " & Sp(0): Cells(c, "I") = "Not Moved"
    [COLOR="Navy"]Else[/COLOR]
        c = c + 1
        Cells(c, "G") = K: Cells(c, "H") = "Stage " & Sp(0): Cells(c, "I") = "Stage " & Sp(UBound(Sp))
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] With


[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[MickG]

The code looks at all the data , then provides the "First" Stage No and the "Last", (I can provide all intermediate values as required)
If could provide an example of the "Results" you would expect to see for a range a data I should be able to alter the code to suit.