Analysing repeat customer orders

[spaceace]

Hi

I have a set of data in excel which consists of three columns:

Order Reference, Order Timestamp, CustomerID

This list of data tells me all customers who ordered a specific product from inventory.

What I need to provide as an output is the number of distinct customers who ordered in a specific month (Nov 2004) - which I can do - but then I also need to output the number of distinct customers who ordered in Nov 2004 who have ordered again since Nov 2004.

Its this second bit that I have no clue how to do. Can anybody give me some pointers in terms of where to start?

Thanks in advance to anybody who can shed some light on this.

 

[Harvey]

this formula:
paste it as a matrix formula (ctrl-shift-enter instead of just enter to confirm it)

=INDEX(C2:C9,INDEX(N(MATCH(N(IF(B2:B8,"<"&F2,B2:B8)),N(IF(B2:B8,">"&F2-1,B2:B8)))),ROW()-1,1),1)

it lists all distinct customers that have bought something before the time in F2, and also after F2. You have to drag it down in order to see all matches.
When there are no matches left, it returns 0.

Map2
	A	B	C	D	E	F	G	H
1	Reference	TimeStamp	CustomerID			date		matches
2		37269	1			1-10-2004		1
3		37664	2					2
4		37688	3					6
5		37694	4					5
6		37876	5					0
7		37999	6					0
8		38274	1					0
9		38275	2					#VERW!
10		38306	6					#VERW!
11		38333	5					#VERW!
Blad1

OCCUR.</FONT></CENTER>

 

[spaceace]

Hi

I am not quite sure I understand the answer as I do not understand what the values in column F represent.

I have approximately 1000 rows of data and wish to look at all 1000 rows and establish how many orders < 30/11/2004 and how many of those customer IDs ordered again after 30/11/2004.

Where is this shown in the example post above?

Also I made a small mistake - my dates are in the format dd/mm/yyyy in column B which I think perhaps makes a difference?

Thanks & Regards

 

[Harvey]

the actual date formatting doesnt make a difference, because indirectly the timestamp is used. cell F2 represents the date you want to use as starting point.
the values in column H in this example show the customers that have ordered BEFORE 1-10-2004, and have ordered AFTER 1-10-2004 again.
if you want to change the date, you only have to change cell F2.
If you want to show the same things for the products (ie. all products ordered before 1-10-2004 and after 1-10-2004 again, change the formula a little:

=INDEX(A2:A9,INDEX(N(MATCH(N(IF(B2:B8,"<"&F2,B2:B8)),N(IF(B2:B8,">"&F2-1,B2:B8)))),ROW()-1,1),1)

 

[spaceace]

OK now I understand. Column F is a list of all the people who have ordered before and after the date specified, right?

Then I presume I just do a formula to count how many non zero rows are in that column?

If that is correct then thanks very much for your help!