Counting specific changes in cell contents across two datasets

[robdcal]

Hi,

I've been a long-time reader so I owe you all thanks before I even get started!


With my data I'm trying to compare two months of data and count the number of users with the following criteria:

• Created Date takes place in the first of the two months.
• The Stage was "A" in the first month and changed to either "B" or "C" (not "D") in the second month.

With this info, I can then work out a conversion rate for users at each Stage, month-to-month.

For example, using the data below comparing January with February, I would want to return a result of 2 (User ID's 5 and 7).

Month 1 - January

[TABLE="width: 500"]
<tbody>[TR]
[TD]Created Date[/TD]
[TD]User ID[/TD]
[TD]Stage[/TD]
[/TR]
[TR]
[TD]01/01/2018[/TD]
[TD]1[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]12/01/2018[/TD]
[TD]2[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]12/01/2018[/TD]
[TD]3[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]15/01/2018[/TD]
[TD]4[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]20/01/2018[/TD]
[TD]5[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]24/01/2018[/TD]
[TD]6[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]31/01/2018[/TD]
[TD]7[/TD]
[TD]A[/TD]
[/TR]
</tbody>[/TABLE]

Month 2 - February

[TABLE="width: 500"]
<tbody>[TR]
[TD]Created Date[/TD]
[TD]User ID[/TD]
[TD]Stage[/TD]
[/TR]
[TR]
[TD]01/01/2018[/TD]
[TD]1[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]12/01/2018[/TD]
[TD]2[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]12/01/2018[/TD]
[TD]3[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]15/01/2018[/TD]
[TD]4[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]20/01/2018[/TD]
[TD]5[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]24/01/2018[/TD]
[TD]6[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]31/01/2018[/TD]
[TD]7[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]01/02/2018[/TD]
[TD]8[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]01/02/2018[/TD]
[TD]9[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]15/02/2018[/TD]
[TD]10[/TD]
[TD]D[/TD]
[/TR]
</tbody>[/TABLE]


Created Date and User ID won't change, but as you can see the Stage will.

As you can also see, new users will be added each month. Users may also be deleted.

The order of the data may also change.


Now, imagine the two tables above are actually side by side with one column space in between, so Month 1 takes up A, B and C while Month 2 takes up E, F and G. The formula below is the closest I've got so far, but it's still incorrect. The problem is, Month 2's Created Date is never considered and matched against - it's just comparing the Stage cells in the order they are presented in (as above). Re-ordering the data in month 2 results in incorrect counting.

{=SUMPRODUCT((MONTH(IF(ISNUMBER(A:A),A:A))&YEAR(IF(ISNUMBER(A:A),A:A))=MONTH(K15)&YEAR(K15))*(C:C="A")*(G:G={"B","C"}))}


Any ideas?

Thanks,
Rob