Numbering Unique Dates by Customer

[Charles651]

Hi, all. First time posting to this site but long-time lurker. I need a formula to add a column to my data that numbers each customer's orders based on the purchase date. The table contains a separate row for each item ordered by a customer, so there may be several rows with the same order date for an individual customer, but all order dates that are the same for an individual customer would be assigned the same number. The first (earliest) order date for each customer would be numbered "1", the second order date numbered "2", and so on until all unique order dates for each customer are numbered.

The columns in my spreadsheet are shown in example columns A-C below. The order number I want to add will be in column D.

Thanks for any and all guidance as I have no idea about how to do this!

A	B	C	D
CustomerID	OrderDate	ItemID	Order
John Doe	06/01/2014	55555	1
John Doe	06/01/2014	12345	1
John Doe	10/12/2014	XYZ99	2
Jane Doe	07/19/2014	AZ123	1
Jane Doe	09/01/2014	99999	2
Jane Doe	10/05/2014	77777	3

<tbody>
</tbody>

 

[Aladin Akyurek]

D2, control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF($A$2:A2=A2,$B$2:B2),$B$2:B2),1))

 

[Charles651]

Aladdin, thank you for the prompt reply to my question. I entered the formula exactly as you suggested, including using control-shift-enter to fill the cells. However, I get the same result, the number 1, for all cells. Do you have any suggestions on how to troubleshoot this? Thanks again!

 

[Aladin Akyurek]

Aladdin, thank you for the prompt reply to my question. I entered the formula exactly as you suggested, including using control-shift-enter to fill the cells. However, I get the same result, the number 1, for all cells. Do you have any suggestions on how to troubleshoot this? Thanks again!

Are the records not sorted on CustomerID and the on OrderDate?

Note. Control+shift+enter means: Press down the control and the shift keys at the same time while you hit the enter key.

 

[Charles651]

Are the records not sorted on CustomerID and the on OrderDate?

Note. Control+shift+enter means: Press down the control and the shift keys at the same time while you hit the enter key.

Yes, the records are sorted by both CustomerID and on OrderDate. I also held down the control and shift keys while pressing the enter key as you do for entering array formulas.

 

[Aladin Akyurek]

Yes, the records are sorted by both CustomerID and on OrderDate. I also held down the control and shift keys while pressing the enter key as you do for entering array formulas.

Would you check this workbook which implements the suggestion: https://dl.dropboxusercontent.com/u/65698317/Charles651 Numbering Unique Dates by Customer.xlsx.

 

[Charles651]

Would you check this workbook which implements the suggestion: https://dl.dropboxusercontent.com/u/65698317/Charles651 Numbering Unique Dates by Customer.xlsx.

Aladin,
Thank you for your help. After viewing your spreadsheet, I realized my cell references were not updating from cell to cell because I had selected the entire column before entering the array formula instead of entering it in the first cell then copying it down. Embarrassing!

You have helped me to save many hours of work, so I'm very grateful.

Charles

 

[Aladin Akyurek]

Aladin,
Thank you for your help. After viewing your spreadsheet, I realized my cell references were not updating from cell to cell because I had selected the entire column before entering the array formula instead of entering it in the first cell then copying it down. Embarrassing!

You have helped me to save many hours of work, so I'm very grateful.

Charles

You are welcome.