Solved

Excel Macro Solution to Repetitive Tasks

Posted on 2013-01-11
3
771 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 500 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
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…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

634 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