(...Could I have been any more cryptic?)

Hi All,

I'm currently trying to find a way to sort through several thousand customer
records (rows) in a sheet, filter in only the initial entries for each unique
customer (by date) and sum each unique initial record in each month/qtr/year
(the purpose is to determine in what month/year their initial purchase was).
I couldn't think of any remotely simple way to do this, any ideas on how to
do this minus VBA?

Thanks,

Jamie

what you have to do is add an auxillary column That contains an indication
which is the first customer record. this is pretty simple with a countif
statement

if you are looking for unique records in column A then in B1 put

=countif(A$1:A1,A1) Notice the $1

The copy this formula down column B. Column B will contain a 1 for the
first record for each unique name. Then test for a one in column b for the
first record.

An easier way for seeing the results is to add an if to the above approach

if(countif(A$1:A1,A1) = 1, "X","")
This will put an X in the the row with the 1st occrance and nothing in the
other rows. It makes the results easier to see.
"MJW" wrote:

(...Could I have been any more cryptic?)

Hi All,

I'm currently trying to find a way to sort through several thousand customer
records (rows) in a sheet, filter in only the initial entries for each unique
customer (by date) and sum each unique initial record in each month/qtr/year
(the purpose is to determine in what month/year their initial purchase was).
I couldn't think of any remotely simple way to do this, any ideas on how to
do this minus VBA?

Thanks,

Jamie

When you say, "determine in what month/year their initial purchase was", you
mean month OF THE year?
If you want to know in what month of what year the first purchase of every
customer was why do you need to sum?

If you are using Excel 2007:
a) sort by date
b) remove duplicates columns customers
You get the first purchase of every customer

Excel 2003:
a) sort by customer than by date
b) If customer names are in column B and starts at row 2 then
paste this formula in cell A2:
=EXACT(B2,B1)
c) copy down column A
d) copy column A and paste special values
e) sort by column A.
All FALSE are new records.


"MJW" wrote:

(...Could I have been any more cryptic?)

Hi All,

I'm currently trying to find a way to sort through several thousand customer
records (rows) in a sheet, filter in only the initial entries for each unique
customer (by date) and sum each unique initial record in each month/qtr/year
(the purpose is to determine in what month/year their initial purchase was).
I couldn't think of any remotely simple way to do this, any ideas on how to
do this minus VBA?

Thanks,

Jamie

Hi Tevuna,

Firstly, thank you for the response. Yes, I mean the month and year in
which the initial purchase for each customer was made. The intent of using a
count function (such as what Joel suggested in the first reply) is to
determine how many new customers existed for each month in each year--a
customer base trendline and baseline for future forecasting. It appears your
solution does work, but it's a little more tasked and a little less maleable
than the one Joel suggested--only because of something which I hadn't
considered when I made my inital post, which is by using Joel's solution, I
can also trend subsequent customer purchases as well. Thanks for the help, I
appreciate the response.

Thanks

Jamie

"Tevuna" wrote:

When you say, "determine in what month/year their initial purchase was", you
mean month OF THE year?
If you want to know in what month of what year the first purchase of every
customer was why do you need to sum?

If you are using Excel 2007:
a) sort by date
b) remove duplicates columns customers
You get the first purchase of every customer

Excel 2003:
a) sort by customer than by date
b) If customer names are in column B and starts at row 2 then
paste this formula in cell A2:
=EXACT(B2,B1)
c) copy down column A
d) copy column A and paste special values
e) sort by column A.
All FALSE are new records.


"MJW" wrote:

(...Could I have been any more cryptic?)

Hi All,

I'm currently trying to find a way to sort through several thousand customer
records (rows) in a sheet, filter in only the initial entries for each unique
customer (by date) and sum each unique initial record in each month/qtr/year
(the purpose is to determine in what month/year their initial purchase was).
I couldn't think of any remotely simple way to do this, any ideas on how to
do this minus VBA?

Thanks,

Jamie