Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel Macro Solution to Repetitive Tasks

Posted on 2013-01-11
3
Medium Priority
?
800 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 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

This article describes a serious pitfall that can happen when deleting shapes using VBA.
New style of hardware planning for Microsoft Exchange server.
This video shows where to find the word count, how to display it, and what it breaks down to in Microsoft Word.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

721 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