1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel Question - Autopopulate Cells

Discussion in 'Business Applications' started by Logit, Jun 27, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. Logit

    Logit Thread Starter

    Joined:
    Nov 20, 2005
    Messages:
    41
    I have a .csv file that can be dropped into Excel (see attached - "January Example for Email"). Look at column "F - Study Date Time". I need to auto populate that column of information, separated by total number of entries for a particular hour, into a second spreadsheet (see attached - "CT Exam Hourly Matrix").

    Looking at "January Example for Email", column "F", the first entry is "1/1/2012 1:24". On that date, from the 1:00 - 2:00 hour (using 24 hr military time), there is one (1) entry to autopopulate into "CT Exam Hourly Matrix" B:3. The second entry in "January Example for Email", column "F" is "1/1/2012 10:43". So there is one (1) entry to autopopulate into "CT Exam Hourly Matrix" B:12.

    Column/Row F:19 & 20 in "January Example for Email" has two (2) entries for that hour to be autopopulated into "CT Exam Hourly Matrix" C:18.

    Therefore, the total number of entries for a specific hour will be tallied (sum) and transferred to the second spreadsheet in the appropriate column & row.

    Is there a means to automate this process using only Excel or VBA or ??? I apologize for being completely Excel "challenged" and would be most grateful for your assistance. I have no idea what I am doing or how to do it. This is strictly a job related / personal need project to make my daily life sane again.

    Thank you !
     

    Attached Files:

  2. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    I have formatted row one for you.
    Will do the macro when I have time over the weekend.
     

    Attached Files:

  3. Logit

    Logit Thread Starter

    Joined:
    Nov 20, 2005
    Messages:
    41
    Thank you SO MUCH for your assistance.
     
  4. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    Done:D
     

    Attached Files:

  5. Logit

    Logit Thread Starter

    Joined:
    Nov 20, 2005
    Messages:
    41
    The file generates a security warning indicating the macro is corrupt and has been deleted. What do you advise ?
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    5,946
    download the file again, I had the same issue with the first download
     
  7. Logit

    Logit Thread Starter

    Joined:
    Nov 20, 2005
    Messages:
    41
    The error persists. Is there something I should be doing or should not be doing when opening the file?

    This is the recovery log comments:

    <?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
    - <recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
    <logFileName>error093840_01.xml</logFileName>
    <summary>Errors were detected in file 'C:\Documents and Settings\My.MY-D4B893EDCA37\Desktop\CT Exam Hourly Matrix.xlsm'</summary>
    - <removedParts summary="Following is a list of removed parts:">
    <removedPart>Removed Part: /xl/vbaProject.bin part. (Visual Basic for Applications (VBA))</removedPart>
    </removedParts>
    </recoveryLog>
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    5,946
    I have no idea, probably a system restriction on your system.
    It works perfect here (opening the file an so) using Excel 2010
    Have you saved the file to disk before nopening it?
     
  9. Logit

    Logit Thread Starter

    Joined:
    Nov 20, 2005
    Messages:
    41
    The file is saved on my desktop. I've tried moving the file to the folder where the source excel file is located also (the file from which the data is obtained to be sorted). No go.

    I wonder if it would make a difference to have the source code typed in on my system ? That would require step by step instructions for me to complete.
     
  10. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,447
    Hi there,

    About your original question, we need some information. How are you wanting to start the process of moving ("autopopulating") the information? The term "autopopulate" would incur some action is needed to start this process. We can make this any way you want. Should this happen automatically? If so, what should start it? You hitting a button? When the file is closed?

    Some additional questions:
    • Will this file always be the same, or will it be a different source file?
    • Will the data lawyas be in column F? If not, will the header always be the same?
    • Will the data format always be the same, date & time?
    • Are there any other restrictions, i.e. Status, etc?
    • Will it always be the first worksheet in the source file? If not, what name or position will it be?
    • Will the destination file always be the same? What about different months/years?
    • Will 2007 be the lowest version this will be used on?

    As for the security, make sure the file is in a trusted location (Office button [top-left round button], Options, Trust Center, Trust Center Settings..., Trusted Locations), and on your Macro Settings (in Options), you have the checkbox checked of Trust access to the VBA project object model.
     
  11. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1058850