Formula help for identifying monthly product switches

[jfish07]

My dataset is a table in excel. The first column identifies the patient with a unique patient ID # on each row. The remaining column headers are the dates. Each row contains what product the patient is on. On any given month a patient could remain on the product, not be on the product (blank cell) or switch to a new product. For example:

Patient ID Jan 2016 Feb 2016 March 2016
1 Product1 Product2
2 Product2 Product2 Product2
3 Product3 Product4 Product4

In this example,

Patient 1 switched from Product1 to Product2 despite a blank
Patient 2 remained on the same Product2
Patient 3 switched from Product3 to Product4

What I am trying to accomplish in my analysis is how many patients switched, and to what product and from what product.


Number of switches for Product1 Jan 2016 Feb 2016 March 2016
Switch to Product2 0 0 1

Number of switches for Product2 0 0 0

Number of switches for Product3
Switch to Product4 0 1 0

My main challenge has been creating a formula that contains text as the values and not numbers.

Attached is an example dataset. Any assistance would be appreciated! Thanks!

[Pete_UK]

First of all, I would suggest that you change the dates in row 1. If you look at what is in cell C1, for example, in the formula bar you will see 15th Dec 2016 and in D1 it is 16th Jan 2016, but as they are formatted to show dd-mmm it looks as if you are moving from Dec 2015 to Jan 2016. Put your first date in B1 (e.g. 1st Nov 2015) then put this formula in C1:

=DATE(YEAR(B1),MONTH(B1)+1,1)

and apply a custom format to those cells of "mmm-yy", then copy the formula from C1 across the row to avoid any confusion.

I'll take a look at the rest of it now.

Hope this helps.

Pete