Counting and filtering duplicates

[DavidS]

Hello, I have a spreadsheet with 8 columns - a list of names in column A and
dates in column B (other columns contain data that I don't need). The
spreadsheet is appended each day with the same or a new name and another
data so there are about 100 rows for each day. I am trying to find a way of
scanning the spreadsheet and producing columns of unique name, number of
times the name has ocuured in total and number of times it's ocurred in the
last 30 days. Would you please point me in the right direction. I'm not sure
if VBA would be better than using formulas and whichever way is better,
would you mind showing me the main steps in terms of the most appropriate
approach or formula. Thanks very much for your help. David

 

[Pete_UK]

First of all, you can obtain a list of unique names by means of
advanced filter - highlight your list of names (including a header to
the list) and copy them, say, into column A of a new sheet. With the
data still highlighted, click on Data | Filter | Advanced Filter, and
in the pop-up panel you should click on Unique Records Only, and Copy
to Another Location, and enter C1 in the Copy To panel. Click OK and
you will have your unique list in column C - you can delete columns A
and B in this new sheet.

Then in B2 you can enter this formula:

=COUNTIF(Sheet1!A:A,A2)

and copy it down your list of names to give you a totals count of them
from the other sheet.

In C2 you can enter this formula to obtain the count of the last 30
days:

=SUMPRODUCT((Sheet1!A$2:A$2000=A2)*(Sheet1!B$2:B$2000>=TODAY()-30))

For this I have assumed you have 2000 rows of data in the main sheet -
adjust the ranges if you have more, but you can't have a complete
column range with Sumproduct (unless you have XL2007). Copy this down
as required.

The solution is partly dynamic, in that the counts will change if you
add more data (as long as the ranges cover the new data), but new
names will not be added to the unique list automatically - you will
have to do this manually.

If you want a macro solution, you can record a macro while you do the
above once, and then you might have to edit it slightly to ensure that
it can be re-run when you need it.

Hope this helps.

Pete