How to automate a complex process that is different everytime?

[johnt_83]

Hi everyone,

I am wondering how I could turn a current tedious task that I perform every 1-2 weeks @ work into a process that can be done automatically (or at least somewhat automatic?). Basically, what I do right now is organize a bunch of prize codes with people's email addresses manually and send out prize codes to various users based on how they are performing - and every 1-2 weeks these users accumulate more points and then I send them these codes that they getif they have moved up a level. If they haven't they don't get a code. I then do a mail-merge >> Excel >> Word >> Outlook sends the emails to all the users with their prize codes.

I have found that this manual process (taking codes and assigning them to various users) is very time consuming doing it one user by one user all the time...and was hoping the experts here would be able to assist in some way.

Is this even possible in Excel or does one need to be a Guru in Excel VBA or some other programming language (which I am not)?

I have been trying to think of ways I could do this but since I am not a software engineer/programmer it's hard for me to really move forward. I'm hoping you guys could help.

The code data is basically in a spreadsheet and I would then have to copy/paste those codes to match to a user who has earned them...and I then have to determine if they have moved up a level in points or not - this too is manual but am hoping this can too be done automatically or semi-automatic.

Please let me know if you need clarification and I will do the best I can.

Thanks.

 

[venkat1926]

suggest post a truncated data sheet and explain clearly what you want. one or two example and a sheet showing how you want the result will be useful.

 

[johnt_83]

Thank you for the response venkat1926 - I will see what I can do and re-post the information. Out of curiosity, how do we go about posting attachments on here (i.e. images / excel files?)

 

[venkat1926]

You cannot send attachment in this newsgroup.l But there is an add in (see sticky postings at the top of the list of threads) which help you in sending small amount of data.

other wise just copy the data from your sheet and paste it on the message. formatting may be not good but it is possible to retrieve the data to excel . do not send gif or other images.

 

[johnt_83]

Thanks! I will get the data and post it on here (formatted) and explain as best as I can - please note the data will be x'd out for security purposes.

 

[johnt_83]

Here are the details - there are 3 files that I primarily work with - the main one being #3 as shared below:

1) The Masterlist where user information is (i.e. email info and the codes that have been sent to users via the mail merge in the past - I fill the codes into this spreadsheet manually)

2) The codes - these are the user prize codes that are sent to a user based on the prize they have earned - total of 8 different prizes for their efforts

3) The prize determination file - this is where users who have earned a prize are determined if they have moved up a rank/level since prizes were last sent out - if they did move up, then they get a new prize code which will be emailed to them via the mail merge. I calculate this by doing a simple IF formula in Excel seeing whose score is higher than 2 weeks ago and if they crossed a certain level of points - only then would they get a new prize code.

(hope this makes sense so far)

Here is the formatting for the 3 excel files without disclosing any confidential information:

1) Masterlist: (these are all horizontally based rows)

Prize 1 (codes which have been sent to users in the past since they earned a prize - manually inserted)
XXXXX-XXXXX-XXXXX-XXXXX-XXXXX
XXXXX-XXXXX-XXXXX-XXXXX-XXXXX
XXXXX-XXXXX-XXXXX-XXXXX-XXXXX

Prize 2 etc
XXXXX-XXXXX-XXXXX-XXXXX-XXXXX
XXXXX-XXXXX-XXXXX-XXXXX-XXXXX
XXXXX-XXXXX-XXXXX-XXXXX-XXXXX

2) The codes (this file is where the codes are stored and where I have to grab them from - these codes I currently manually copy/paste into a cell in file #3 below only if a user has crossed a certain points level to earn a prize

Prize Codes to be sent out (sample)
XXXXX-XXXXX-XXXXX-XXXXX-XXXXX
XXXXX-XXXXX-XXXXX-XXXXX-XXXXX
XXXXX-XXXXX-XXXXX-XXXXX-XXXXX
XXXXX-XXXXX-XXXXX-XXXXX-XXXXX
XXXXX-XXXXX-XXXXX-XXXXX-XXXXX
XXXXX-XXXXX-XXXXX-XXXXX-XXXXX
XXXXX-XXXXX-XXXXX-XXXXX-XXXXX
XXXXX-XXXXX-XXXXX-XXXXX-XXXXX
XXXXX-XXXXX-XXXXX-XXXXX-XXXXX
XXXXX-XXXXX-XXXXX-XXXXX-XXXXX
XXXXX-XXXXX-XXXXX-XXXXX-XXXXX
XXXXX-XXXXX-XXXXX-XXXXX-XXXXX
XXXXX-XXXXX-XXXXX-XXXXX-XXXXX

3) The prize determination file -this is the file where all the magic happens (tracking of who gets a prize code or not)

If Level Up = Yes then the following happens:

In the Prize code columns, I perform a VLOOKUP function from within the Masterlist to see if a user has already received a prize code for Prize #1, 2 and so forth. I do this for all users regardless of whether or not they moved up a level - this gives me insight on who will get a prize if 'Yes' and then I filter those 'No's out. If they have received a Prize #1/2 etc code before, then I put "Already received code" for that prize - that way they don't get it twice, and if they haven't received a code (meaning the Vlookup function didn't find anything for them in the Masterlist) then they get assigned a new code in the following format:

XXXXX-XXXXX-XXXXX-XXXXX-XXXXX from the Codes file (#2)

So here is the issue for me - I find myself having to do this manually all the time - and it is very time consuming to go through and determine who has already received a code and who hasn't. I can't do a vlookup for all the users in the Masterlist because not all users are applicable to receiving a code - since they didn't move up a level (and some are not even on the points Leaderboard to be considered for a code because they are not 'Active' in my program).

So basically - I am trying to find out if there is a way I could automate this bi-monthly prize code sending that I do to users who participate in our program instead of doing it manually one user by one user...the mail-merge stuff is easy as I can just grab the data from Excel columns but the actual determining of who gets a code and then assigning the prize code to each individual user based on whether or not they have received one in the past is where it gets very tedious and manual...

I hope this helps...I know it may be a tad confusing but let me know if you can envision what I am explaining or need any clarification. Thank you so much for your help!!! I really appreciate it.

Regards,
John

 

[Smitty]

John,

Look at the link in my sig to the Mr Excel HTML Maker, which will let you post a shot of your sheets.

 

[johnt_83]

Smitty,

That didn't seem to work for me - plus I can't take a screenshot of my data as it is confidential information. I am hoping the information I gave in my previous post is sufficient. Please let me know if you need any clarification or further information - thank you!

Cheers,
John

 

[venkat1926]

do not sent print screen. copy the truncated data in another sheet and change the data to fictitious data. then highlight, copy and paste it in the reply message along with column headings.

 

[johnt_83]

Hi venkat1926 - I will do this once I return to work this week. Did the information I share in my long post make sense as far as what I do on a bi-monthly basis?

Thanks,
John