Got to be a better way than a Pivot Table to create this Latency Report

[sweetmetrics]

Hey all you wonderful people out there in the world. It's Friday again and I find myself cranking away on some analysis and thinking there has to be a better way to do this in Excel rather than the way that I am doing it trying to do crazy filters and sorts in a Pivot Table and then do a much of functions and manual work.

I have a table with the following columns:

OrderNumber | OrderDate | CustomerID

In an ideal world, I'd run a function that would create two more columns:

Transaction # | Days Since Previous Purchase

Where Transaction # would be the 1,2,3,4,etc transaction the customer has had based on the last date they purchased something and Days Since Previous Purchase would be the number of days since the last transaction occurred.

Asking too much of Excel? Seems like a doable problem to solve.

Any direction would be much appreciated. I am using Excel 2007.

I love figuring this stuff out so that I can learn, just don't know where to start.

 

[oldrelia23]

Assuming your data is in columns A to C, and first transaction entry is in row 2, and that the transactions are sorted chronologically with the oldest entry being the first entry, calculations in row 2 are:

Transaction # (column C): =SUM(--($C$2:C2=$C2))
Days Since Previous Purchase (column D): {=IF(ISERROR(INDEX($B$2:B2,MATCH(1,($C$2:C2=$C2)*($D$2:D2=($D2-1)),0))),0,$B2-INDEX($B$2:B2,MATCH(1,($C$2:C2=$C2)*($D$2:D2=($D2-1)),0)))}

Then fill down.

 

[sulakvea]

hi metrics, here's one more - and sorry i didnt get the first piece of the question (# of transactions since last purchase - wouldnt that be 0 all the time? by definition, the last transaction means the customer hasnt bought anything since then)

for the second piece, here it is. assumes A=Order #, B=Date, C=Customer ID
formula entered as array, i.e. with ctrl+shift+enter. start in D2 and copy down

=IF(COUNTIF($C$1:C1,C2)=0,"First Time Purchase",B2-INDEX($B$1:B1,MAX(IF($C$1:C1=C2,ROW($C$1:C1),FALSE))))

Excel Workbook
	A	B	C	D
1	*	12/16/09	1	*
2	*	12/17/09	1	1
3	*	12/19/09	6	First Time Purchase
4	*	12/23/09	1	6
5	*	12/23/09	3	First Time Purchase
Sheet1

 

[sweetmetrics]

Even though I did a bad job explaining, despite it all you guys were able to provide some great direction. I'll work through both of these tonight or tomorrow and post either some clarifications or let you know what I was able to get working.

Thank you.

Shilo

 

[sweetmetrics]

Pushing the limits of my abilities, but after an hour or so if breaking apart the functions to figure out what was going on (and finally remembering how to properly copy down an array formula) I was able to get sulakvea's to work.

Here is the workbook that shows both attempts (separate worksheets). I wasn't able to get oldrelia23's to work so I must be missing something obvious.

http://www.mediafire.com/file/oigyq8mjndy5gjx/Sample_Worksheet.xlsx

Sulakvea, sorry for the confusion with my poorly written description. Here is a better way for me to word this. TransactionNumber = which transaction is this for the customer i.e. is it their 1st, 2nd, 3rd, 4th, etc. After working with your formula I realized that this is a simple COUNTIF function and that gets me the answer. The other one is maybe better labeled DaysSincePreviousPurchase, but you clearly figured this out because your function got me that answer.

Thank you both for your guidance on solving this. Have a great weekend.

Sincerely,

Shilo