Solved

Excel Macro Solution to Repetitive Tasks

Posted on 2013-01-11
3
710 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 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
Comment Utility
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
Comment Utility
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 92

Expert Comment

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

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Surprisingly, there is a lot to Gym battles, and I thought it would be helpful to share knowledge about all the ins and outs of this feature!
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now