+ Reply to Thread
Results 1 to 6 of 6

Copying data from an array to a vector

  1. #1
    Registered User
    Join Date
    12-06-2012
    Location
    Morpeth, England
    MS-Off Ver
    Excel 2010
    Posts
    4

    Copying data from an array to a vector

    Hi – long-time user of Excel Forum for information gleaning purposes, but this is the first time I’ve ever posted a request for help.

    I am working on an Excel spreadsheet that will be used for our clients to record each transaction they undertake for a certain activity. I had originally been asking the clients to record their activity on a transaction by transaction basis, but they were concerned that this would mean a lot of manual data entry, as some of them can have around 150 of these transactions per day. Not oblivious to their concerns I asked them how they would prefer to record their activity. Their suggestion was a form of matrix, where, at the end of the month, they could pick the client from a drop-down in the first column and then indicate, underneath the days of the particular month in question, whether that client had accessed the service or not. There are two potential types of service access (either "S" for Single or "M" for Multiple) - so they would just type "S" or "M" in to show, for each given client, whether they had accessed the service on each day of the month, and whether their access was a "S" type of access or an "M" type. This data would be entered onto the “Data Entry” worksheet.

    I'd like to automatically translate this information into a line by line activity record, which would be recorded on the “Data as a Vector” worksheet. I would be very grateful if someone could help me with what I presume would be a piece of VBA code to go through the matrix on the “Data Entry” worksheet, and, whenever it shows that a specific client had accessed the service for either a "S" transaction or a "M" transaction on a certain day (i.e. the matrix would indicate a ClientID, a date, and a service access type), it would write that information to the activity record on the “Data as a Vector” worksheet as a new record. So as well as there being one record for a client on the “Data Entry” worksheet, which might show that the client had used the service, for example 18 times within that month on 18 different days, I’d like to see those 18 records individually listed on the “Data as a Vector” worksheet too.

    As you can see I then have a set of VLOOKUPS on “Data as a Vector” that will search for the Client ID on the “Client data” worksheet and return all the other relevant information I need to complete the activity information. I’m presuming that the VBA could be attached to a big button somewhere on the “Data Entry” worksheet that the user would press to run the transfer process.

    I'm trying to teach myself VBA, which is why I don't think that this is something that can be done with an Excel formula, but I'm not very confident with VBA yet, so I'd be very grateful for someone's help with the VBA element to this project. Many thanks in advance for your help!
    Attached Files Attached Files
    Last edited by Humpster; 04-04-2013 at 11:08 AM. Reason: Changing version of uploaded file

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Copying data from an array to a vector

    A sample file with “Data as a Vector” worksheet filled as you want would be helpful
    If solved remember to mark Thread as solved

  3. #3
    Registered User
    Join Date
    12-06-2012
    Location
    Morpeth, England
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Copying data from an array to a vector

    Hi patel45,

    Yes, you are absolutely correct, it would have been better to have done that. I've amended my original post to put a second version of the file on there. The purple coloured cells in the "Data as a vector" worksheet is the information I'd like to have automatically taken across from the "Data entry" worksheet - the grey coloured cells will just auto-complete via a set of VLOOKUPs.

    Thanks for taking a look at the problems for me!

    Humpster

  4. #4
    Registered User
    Join Date
    12-06-2012
    Location
    Morpeth, England
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Copying data from an array to a vector

    Hi patel45,

    I was wondering whether you had been able to look at this problem for me? If not, should I bump it as unsolved? Is there any other information I can supply that might help?

    Kind regards,

    Humpster

  5. #5
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Copying data from an array to a vector

    Please Login or Register  to view this content.
    The output currently populates Sheet1, so you can check results.
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  6. #6
    Registered User
    Join Date
    12-06-2012
    Location
    Morpeth, England
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Copying data from an array to a vector

    Hi Andy,

    That's fantastic!! I ran the code, switched back to the test sheet and it was all there instantaneously!!! This is perfect, and will be incredibly more efficient for my clients, who I know were really concerned about having to enter data row by row by row by interminable row, so thank you on their behalf too!!

    Kind regards,

    Humpster

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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