Okay, this one I can’t figure out at all. I need to identify customers in a list that have more than one entry (i.e. sold to more than once – resales/upsales etc.) and then I need excel to compare those multiple entries and identify the earliest invoice date. Any ideas?
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
ffice
ffice" /><o
></o
>
The purpose of this is to categorise invoices as either ‘new business’ or ‘retention business’.
I’ve got far enough to work out which ones we’ve resold to but the very first sale to that customer doesn’t count as a renewal so I need excel to work out which one is the first invoice so I can add that to the new business totals. This all needs to feed into a pivot table to show how close the retention team are to target and how close the sales people are.<o
></o
>
<o
></o
>
Example:<o
></o
>
<o
></o
>
1) Naughty Boys’ School buy in Jan 2007 for the first time – this is New Business<o
></o
>
2) Naughty Boys’ School renew in Jan 2008 – this is retention business<o
></o
>
3) Naughty Boys’ School add 10 more students in March 2008 – this is retention business<o
></o
>
<o
></o
>
Entry 1) will go into one pot of money and entries 2) and 3) into another. Each pot will have its own pivot table for tracking targets.<o
></o
>
=COUNTIF(D192:D1630,D221) is the formula I already have in the frequency column, that just shows how many entries there are with multiple purchases. So I can see that all those with a ‘1’ are New Business but that doesn’t tell me which invoice of the repeat customers was the first one.
Any help at all would be greatly appreciated! Thanks...
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com




The purpose of this is to categorise invoices as either ‘new business’ or ‘retention business’.
I’ve got far enough to work out which ones we’ve resold to but the very first sale to that customer doesn’t count as a renewal so I need excel to work out which one is the first invoice so I can add that to the new business totals. This all needs to feed into a pivot table to show how close the retention team are to target and how close the sales people are.<o


<o


Example:<o


<o


1) Naughty Boys’ School buy in Jan 2007 for the first time – this is New Business<o


2) Naughty Boys’ School renew in Jan 2008 – this is retention business<o


3) Naughty Boys’ School add 10 more students in March 2008 – this is retention business<o


<o


Entry 1) will go into one pot of money and entries 2) and 3) into another. Each pot will have its own pivot table for tracking targets.<o


=COUNTIF(D192:D1630,D221) is the formula I already have in the frequency column, that just shows how many entries there are with multiple purchases. So I can see that all those with a ‘1’ are New Business but that doesn’t tell me which invoice of the repeat customers was the first one.
Any help at all would be greatly appreciated! Thanks...