Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel Macro Solution to Repetitive Tasks

Posted on 2013-01-11
3
Medium Priority
?
827 Views
Last Modified: 2013-11-12
I play an online multiplayer game which allows players to transport resources to one another.  Our team is sending resources to my account and there is a message confirmation which we receive when resources are successfully received in the following formats:

GamerDood1234 sent the following resources or soldiers to your town: Wood x 10000 Food x 10000 Iron x 10000 Marble x 10000 Gold x 10000.

GamerDood1234 sent the following resources or soldiers to your town: Wood x 10000 Marble x 10000 Gold x 10000.

Sometimes they will only send one type of the 5 total resources, but I only want a total of 6 columns (PlayerName + 5 Different Resources).  I would like to keep a log of all of these documents, but it takes FOREVER to manually copy the sender's name and each resource type and paste it into the corresponding column to keep calculations.

I need an Excel Macro that will extract specific text from my cliboard containing 100 or so of these reports simultaneously so that I can have these values calculated without so much manual work.  I have no idea where to start!

Which commands/functions/scripts will I be utilizing to achieve this goal in Excel?

Thank you for your time in advance.  250 Points awarded to each Response!
0
Comment
Question by:Hwy419
  • 2
3 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 38769123
Please see the attached file:

 Q-27992941.xlsm

The idea is that on the Data worksheet, you paste your message into the first available cell in Column A.  I have a table set up there which will automatically expand to cover your new row.

There is then a formula to extract the user name:

=LEFT([@Message],SEARCH(" ",[@Message])-1)

and formulae to extract the resource amounts:

=VALUE("0"&RegExpFindSubmatch([@Message],"("&Stats[[#Headers],[Wood]]&" x )(\d+)",1,2,FALSE))

<update for each resource type>

Note that RegExpFindSubmatch is a user defined function.  I describe it in my article here:
http://www.experts-exchange.com/Programming/Languages/Visual_Basic/A_1336-Using-Regular-Expressions-in-Visual-Basic-for-Applications-and-Visual-Basic-6.html

I also added a PivotTable that will automatically summarize your resource deliveries by user name (Pivot worksheet).  Please see this article for background on the techniques I used to ensure that the PivotTable automatically updates:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/A_3172-How-to-Automatically-Update-Your-PivotTables-and-PivotCharts.html
0
 

Author Closing Comment

by:Hwy419
ID: 38769415
My goodness, Mr. Matthews, I can't tell you how thankful I am that you have responded and did so as quickly as you did.   This has worked wonderfully and I am VERY impressed with your knowledge and capability where this  solution is concerned.  A+ Man, you are a tremendous asset to Experts Exchange.  Thank you again!
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 38770114
Glad you liked it, and thanks for the compliment :)
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Outlook for dependable use in a very small business   This article is about using the Outlook application (part of Microsoft Office) in a very small business, or for homeowners where dependability and reliability are critical requirements. This …
With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question