Self-Defining Unique Values

[lifeonathread]

OS: Mac OSX 10.5.8
Excel: Microsoft Excel 2008 for Mac, v12.2.6

I'm creating a series of spreadsheets to monitor the traffic on my
online store. Due to the technology I'm using, I have the advantage to
see the names of my visitors, which allows me to track repeat
customers.

I have spreadsheets set up for each month, with the visitors and the
dates visited listed. In a seperate spreadsheet, I want to track which
of these visitors are repeat visitors and automatically update with
new visitors. I'm currently attempting to write an IF statement but
am having a lot of trouble.

A quick example is:

May 2010
Bob Mcnamara, 5/15/10
Sue Burquest, 5/17/10

June 2010
Sally Jensen, 6/1/10
Jesse Smith, 6/4/10

Visitor Sheet
Bob Mcnamara
Sue Burquest
Sally Jensen
(Formula)

What I have so far in mind is:
IF SheetVisitor does NOT contain May2010 A1:A2 and June 2010 A1:A3,
then add missing name to Sheet Visitor.

But I'm having trouble writing it in Excel terms!
Thanks very much in advance.ray:

 

[mikerickson]

Have you looked at AdvancedFilter?

It will create a list of the unique Visitors, that can then be used (with COUNTIF) to see how many times a vistor has visited.

A Pivot Table might do somehthing similar.

 

[lifeonathread]

Hi Mike!
Thanks for the quick reply, I appreciate it!
I'm having trouble getting the advanced filter to work for more than one sheet, as the name data is spread across the sheets by month, and most repeat visitors come months apart.

I've tried pivot tables in the past and got completely lost in how to do it. I'll look it up again, but until then is there a way to make advanced filter work across all 12 sheets?

 

[lifeonathread]

I attempted to do the sorting via a pivot table, but it won't let me use data from just one column, no matter how many sheets it's across. When I had it use the names with their partnered dates, the table I got was... well.. unintelligible.

Maybe I'm doing something wrong? It also won't let me sort of unique data using the pivot table function.

 

[shg]

Put all the information on one sheet; then you have access to all kinds of tools.

 

[lifeonathread]

But then I have to update it in two places rather than just one.... The way I have data entry set up is by month. The whole point was to not have to transfer all the data on a daily basis to more than one sheet.

But thank you shg4421 for your suggestion!! If I can't get anything else to work I may have to, but I'm confident the solution exists.

 

[shg]

But then I have to update it in two places rather than just one....

You only have to put it in one place. Putting it on multiple sheets is a choice, right?

 

[lifeonathread]

No because the data is ongoing, I have the sheets made up and formulas entered for November and December, but they haven't happened yet. It's a lot easier to organize things via Month on separate tabs... but maybe you're right and it's my organization that's messed up but that means redoing the entire spreadsheet and charts.

I still think there is a way to have it do what I want.

 

[lifeonathread]

After a lot of research and trial and error I was able to get the pivot chart to do what I needed. Thank you everyone for the advice! Apparently I'm just really bad at pivot charts. >_<