Copying data from an array to a vector

[Humpster]

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!