Customer response time

[Bren.ie]

I have an Excel spreadsheet with a list of customers with a number o
transaction with dates listed for each customer.

One column lists the customer name, another column records th
transaction type while another column contains the transaction date
Unfortunately all the transaction dates appear in one column. The dat
format is dd/mm/yy i.e. 10/09/03.

I want to write a macro to subtract the initial contact date from th
final contact date (and identify the number of days between both dates
for each customer.i.e.

The difficulty is identifying the first and last transaction date fo
each customer as the total number of transactions may vary betwee
customers.


I need the macro to identify the first and last transaction dates fo
each customer and subtract them. The final result ideally would have
single line for each customer with the number of days displaye
indicating the difference between the first and last transaction dat
for that customer

 

[Dave Peterson]

I'd try this manually to see if it works.

Make sure your lists have a header row.

Select your list and then
Data|Pivottable
click next (we want the defaults)
click next again (we selected the range first)
click on layout

drag the Name header to the row field.
drag the date header to the data field.
double click on that date head and select Max

drag the date header to the data field (yes, again)
double click and select Min
ok
Finish

Now drag the Data "button" one cell to the right (over total)
format those dates as dd/mm/yy (or what you want)

put a formula in the adjacent cell that subtacts those two cells. Fromat those
cells General.

(You may have to adjust the formula if today - yesterday is 2 (not 1). If so,
just add a "+1" to your formula.)

If you really wanted a macro to do this, you could record one. But I don't
think you'll need one.

You can use a dynamic range name to make sure that any new data will be shown in
the pivottable (after refreshing the table).

Take a look at Debra Dalgleish's site for some dynamic range naming tips:

http://www.contextures.com/xlNames01.html#Dynamic

 

[Bren.ie]

Many Thanks Dave. It worked perfectly,

Many thank