Self-Defining Unique Values

lifeonathread

New Member
Joined
Oct 5, 2010
Messages
12
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! :rolleyes:
Thanks very much in advance.:pray:
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,784
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

New Member
Joined
Oct 5, 2010
Messages
12
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

New Member
Joined
Oct 5, 2010
Messages
12
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,714
Office Version
2010
Platform
Windows
Put all the information on one sheet; then you have access to all kinds of tools.
 

lifeonathread

New Member
Joined
Oct 5, 2010
Messages
12
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,714
Office Version
2010
Platform
Windows
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

New Member
Joined
Oct 5, 2010
Messages
12
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. :confused::(
 

lifeonathread

New Member
Joined
Oct 5, 2010
Messages
12
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. >_<
 

Forum statistics

Threads
1,081,748
Messages
5,361,057
Members
400,611
Latest member
ThebigG

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top