+ Reply to Thread
Results 1 to 4 of 4

Advice on creating spreadsheet to track large amount of data?

  1. #1
    Registered User
    Join Date
    03-06-2014
    Location
    BC, Canada
    MS-Off Ver
    Excel 2010
    Posts
    2

    Advice on creating spreadsheet to track large amount of data?

    Hi, I'm looking for advice on how best to manage a lot of data. Here's what my situation:

    I need to create a spreadsheet to track visitor's contact info, date of visit, and what sites they're visiting. This is to track tourism information for our area.

    Visitors are given a pass to fill out with the date and their contact information. On the pass is also a list of 30 tourism sites in the area and a space for up to 10 stamps to be stamped by the visited sites. If a visitor hands in a fully stamped (10 stamps) pass, they are entered in to a draw for a prize.

    I have between 7,000-10,000 passes to input.

    I know the very basics of excel and have so far created one big spreadsheet with a column each for Name, Address etc for the contact info, and one column for each of the 30 possible sites to visit. When entering a pass, I fill it out as required, inputting a '1' for a visit to any of the sites. This, I figured, was the easiest way to tackle this project. It allows me to easily track how many visitors visited each site, and I think I can figure out how many sites the average visitor went to.

    I have about 500 entries input into the database and I'm finding it very cumbersome. For instance, I tried to sort the spreadsheet in ascending order by last name, but Excel won't complete the task.

    I think there must be a better way than my 'simple' spreadsheet but don't know enough of excel to figure it out on my own. I'd likely be able to implement suggestions easily enough, so I'm asking if anyone can help with a more appropriate design. I also wonder if Access might be a better way to go.

    I would appreciate any and all help on this situation, and I do hope my explanation is clear enough!

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 365 Win 11 Home 64 Bit
    Posts
    20,947

    Re: Advice on creating spreadsheet to track large amount of data?

    Access is the perfect way to do this. It is a relational data base. If you have not had any experience with Access, then there are two links I would like you to look at. The first is on setting up a RDBMS and the second is a tutorial on building a db.

    http://sbuweb.tcu.edu/bjones/20263/A...sDB_Design.pdf

    http://www.accessmvp.com/strive4peace/
    Alan


    Have you debugged your code?
    Database Normalization
    What is Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Guru Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,252

    Re: Advice on creating spreadsheet to track large amount of data?

    A database would be more efficient.

    Three tables:
    - Visitors
    - Sites
    - Visits (which links Visitor key with Site key, along with visit date)


    It would then be straightforward enough to set up a data entry form, for recording visitor details and their site visits, and then to query this data to answer all your questions.

    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  4. #4
    Registered User
    Join Date
    03-06-2014
    Location
    BC, Canada
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Advice on creating spreadsheet to track large amount of data?

    alansidman and Olly, thank you!

    I do have some experience with Access but it's all self taught and I feel like I'm simply muddling my way through when I use it. But I suppose I feel the same way about Excel. I will see if I can work it out in Access then.

    Many thanks!

    alansidman, thank you for the links - I look forward to reading through the information

    Olly, quick question - in regards to your suggestion, in the 'visits' table would you list the sites in either a list box or combo box and be able to simply select up to 10 sites?
    Last edited by Samnella; 03-06-2014 at 05:57 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Creating a Spreadsheet to track PTO accrual
    By beetlehound in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-23-2013, 01:47 PM
  2. Replies: 6
    Last Post: 09-27-2012, 10:45 AM
  3. Advice for auto-formatting large spreadsheet
    By sgordon25 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-26-2011, 03:14 PM
  4. creating a function that maps to a large spreadsheet
    By KChristman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-26-2008, 12:41 AM
  5. [SOLVED] $ large amount of Data.
    By confused man in forum Excel General
    Replies: 5
    Last Post: 02-08-2005, 09:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1