Solved

Excel Macro Solution to Repetitive Tasks

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

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 92

Expert Comment

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

As we all know Counter Strike is a very popular computer game. Usually it is played over a network for which people create a server and users join it but it is interesting to know that one can creates a dedicated server which not only hosts the game…
PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
This video shows the viewer how to set up and create Footnotes in their document. Click on the References tab: Select "Insert Footnote": Type in desired text:
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.

749 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