Solved

Using Excel to Automatically Retreve Data

Posted on 1998-04-23
1
146 Views
Last Modified: 2006-11-17


I have a divice that collects information from vending and using our
device a reading is taken of various GUM machines. A collector
placed in one machine can read data from one to 10 machines.  In the
example I am enclosing one collector is reading information from 5
different machines.  Here's how it works.  A person goes to a machine
and plugs our Reader into the collector that and the following
information is collected---- 1. The Reader Number (assigned when Reader
is set up) 2. The Collector Number (assigned when the Collector is set
up and usually either the Account Number or The Asset Number 3. The Date
(the Date an activity took place. 4. Time (the time an activity
occurred. 5. Item (The Item Number - which ever machine was activated
and tied to a given product)  I.E. Machine one contains Gum Balls,
Machine 2 contains M&M's etc. 6. The Action that took place (The machine
Vended a product, the machine was Opened or the machine was Closed or a
Test Vend took place - All Vends that take place when the machine is
Opened are shown as Test and not counted as revenue)

After our Reader collects the information the Collector is reset for new
inputs.  The person moves on to the next machine(s) and proceeds to
download into our Reader all the information about those machine(s)
activity (up to 400 machines).

When the person returns to his office he plugs the Reader into the
Printer Port using a special cable.  He brings up our Software and
downloads all the information that is in the reader.  He can view each
account in the sequence he collected the information and and see how
much was collected the accounts and how much product was vended as well
as the price by product and the total.  He can also view the detail that
gives all of the information pertaining to the six items indicated
above.  When he is through viewing all the he hits a KEY as directed by
our Menu screen and the file is saved as a TXT format into the Directory
that contains our Software.  The file is saved as a D1.txt D2.txt D3.txt
etc. depending on the Reader number.  If he were to go thorough the
process again using the same reader then the txt file for that reader
would be over written.

All this is enough so far and really gives the operator all he needs but
to enhance the information I would like to provide a little additional
utility.

NOW HERE IS THE PROJECT:

I would like to take the information as demonstrated  in the first six
columns of the D1 file which is attached and place that information on a
sheet like that indicated on the ExcelExperiment.xls Sheet 1 example
which is also attached.  As you can see on the D1 file beyond column F
it can be done albeit the long way around.

After the information is downloaded I would like to be able to click on
an ICON and have the information displayed as shown on Sheet 1.
Initially the operator would have to put in the Name, Address, Route
Number (not shown but would be determined by the Reader Number), Product
Number, Product Name, Vend Price, % of Commission  Paid, Product Cost,
Cost of Opns, all other calculations should be done by Excel.  Since
some Collectors collect anywhere from one to ten units or columns if you
prefer it would be appropriate if only those units or columns that
actually contained information were listed as example on Sheet 1.  For
example if there were only 4 units then only 4 would be displayed if
there were 6 then of course 6 would be shown, however say that no
activity took place on unit or column 3 in a 6 unit or column collector
then it would still be listed but with no activity taken place for this
unit or column.

Well, that's it in a nut shell let me know what you think.

Regards
C. King

1      5      5/24/96      10:20      2      VEND       0      0      0      0      0
1      5      5/24/96      10:31      4      VEND       0      0      0      0      0
1      5      5/24/96      10:31      3      VEND       0      0      0      0      0
1      5      5/24/96      10:33      3      VEND       0      0      0      0      0
1      5      5/24/96      10:33      4      VEND       0      0      0      0      0
1      5      5/24/96      10:33      3      VEND       0      0      0      0      0
1      5      5/24/96      10:37      1      VEND       0      0      0      0      0
1      5      5/24/96      13:52      2      VEND       0      0      0      0      0
1      5      5/24/96      14:19      2      VEND       0      0      0      0      0
1      5      5/24/96      14:20      2      VEND       0      0      0      0      0
1      5      5/24/96      15:42      1      VEND       0      0      0      0      0
1      5      5/24/96      15:50      1      VEND       0      0      0      0      0
1      5      5/24/96      17:07      4      VEND       0      0      0      0      0
1      5      5/24/96      19:52      4      VEND       0      0      0      0      0
1      5      5/24/96      20:36      3      VEND       0      0      0      0      0
1      5      5/24/96      21:32      3      VEND       0      0      0      0      0
1      5      5/25/96      9:05      1      VEND       0      0      0      0      0
1      5      5/25/96      9:48      1      VEND       0      0      0      0      0
1      5      5/25/96      15:46      1      VEND       0      0      0      0      0
1      5      5/26/96      8:14      1      VEND       0      0      0      0      0
1      5      5/26/96      12:44      4      VEND       0      0      0      0      0
1      5      5/26/96      17:28      3      VEND       0      0      0      0      0
1      5      5/26/96      17:33      3      VEND       0      0      0      0      0
c      5      5/26/96      17:33      3      VEND       0      0      0      0      0
1      5      5/26/96      17:35      2      VEND       0      0      0      0      0
1      5      5/26/96      17:37      2      VEND       0      0      0      0      0
1      5      5/26/96      17:50      3      VEND       0      0      0      0      0
1      5      5/26/96      17:53      4      VEND       0      0      0      0      0
1      5      5/26/96      18:02      3      VEND       0      0      0      0      0
1      5      5/26/96      18:21      3      VEND       0      0      0      0      0
1      5      5/26/96      18:21      3      VEND       0      0      0      0      0
1      5      5/26/96      20:02      1      VEND       0      0      0      0      0
1      5      5/26/96      20:02      1      VEND       0      0      0      0      0
1      5      5/27/96      11:20      1      VEND       0      0      0      0      0
1      5      5/27/96      11:34      1      VEND       0      0      0      0      0
1      5      5/27/96      13:04      3      VEND       0      0      0      0      0
1      5      5/27/96      13:04      1      VEND       0      0      0      0      0
1      5      5/27/96      14:05      2      VEND       0      0      0      0      0
1      5      5/27/96      14:07      1      VEND       0      0      0      0      0
1      5      5/27/96      14:13      2      VEND       0      0      0      0      0
1      5      5/27/96      15:51      1      VEND       0      0      0      0      0
1      5      5/27/96      18:24      4      VEND       0      0      0      0      0
1      5      5/27/96      18:26      4      VEND       0      0      0      0      0
1      5      5/27/96      18:28      4      VEND       0      0      0      0      0
1      5      5/28/96      9:03      4      VEND       0      0      0      0      0
1      10      5/24/96      11:52      2      VEND       0      1      0      0      0
1      10      5/24/96      13:10      2      VEND       0      1      0      0      0
1      10      5/24/96      13:12      4      VEND       0      0      0      1      0
1      10      5/24/96      13:13      4      VEND       0      0      0      1      0
1      10      5/24/96      13:14      2      VEND       0      1      0      0      0
1      10      5/24/96      17:52      5      VEND       0      0      0      0      1
1      10      5/24/96      18:13      5      VEND       0      0      0      0      1
1      10      5/24/96      18:16      2      VEND       0      1      0      0      0
1      10      5/24/96      18:16      5      VEND       0      0      0      0      1
1      10      5/24/96      18:16      5      VEND       0      0      0      0      1
1      10      5/24/96      18:18      1      VEND       1      0      0      0      0
1      10      5/24/96      18:30      3      VEND       0      0      1      0      0
1      10      5/24/96      18:30      3      VEND       0      0      1      0      0
1      10      5/24/96      18:32      3      VEND       0      0      1      0      0
1      10      5/24/96      18:32      3      VEND       0      0      1      0      0
1      10      5/24/96      18:37      3      VEND       0      0      1      0      0
1      10      5/24/96      18:37      3      VEND       0      0      1      0      0
1      10      5/24/96      18:41      5      VEND       0      0      0      0      1
1      10      5/24/96      18:41      5      VEND       0      0      0      0      1
1      10      5/24/96      18:48      5      VEND       0      0      0      0      1
1      10      5/24/96      18:48      5      VEND       0      0      0      0      1
1      10      5/24/96      18:50      5      VEND       0      0      0      0      1
1      10      5/24/96      18:58      5      VEND       0      0      0      0      1
1      10      5/24/96      18:59      5      VEND       0      0      0      0      1
1      10      5/24/96      18:59      5      VEND       0      0      0      0      1
1      10      5/24/96      18:59      5      VEND       0      0      0      0      1
1      10      5/24/96      19:01      5      VEND       0      0      0      0      1
1      10      5/24/96      19:02      5      VEND       0      0      0      0      1
1      10      5/24/96      19:02      5      VEND       0      0      0      0      1
1      10      5/24/96      19:03      5      VEND       0      0      0      0      1
1      10      5/24/96      19:36      2      VEND       0      1      0      0      0
1      10      5/24/96      19:42      2      VEND       0      1      0      0      0
1      10      5/24/96      19:45      5      VEND       0      0      0      0      1
1      10      5/24/96      19:45      5      VEND       0      0      0      0      1
1      10      5/24/96      19:46      5      VEND       0      0      0      0      1
1      10      5/24/96      21:06      5      VEND       0      0      0      0      1
1      10      5/24/96      22:35      4      VEND       0      0      0      1      0
1      10      5/24/96      22:36      1      VEND       1      0      0      0      0
1      10      5/25/96      17:31      5      VEND       0      0      0      0      0
1      10      5/25/96      17:39      2      VEND       0      0      0      0      0
1      10      5/25/96      18:50      3      VEND       0      0      0      0      0
1      10      5/25/96      18:51      3      VEND       0      0      0      0      0
1      10      5/25/96      22:04      5      VEND       0      0      0      0      0
1      10      5/26/96      13:43      5      VEND       0      0      0      0      0
1      10      5/26/96      14:03      4      VEND       0      0      0      0      0
1      10      5/26/96      17:33      3      VEND       0      0      0      0      0
1      10      5/26/96      17:44      3      VEND       0      0      0      0      0
1      10      5/26/96      17:48      3      VEND       0      0      0      0      0
1      10      5/26/96      17:59      2      VEND       0      0      0      0      0
1      10      5/26/96      18:37      1      VEND       0      0      0      0      0
1      10      5/26/96      19:03      5      VEND       0      0      0      0      0
1      10      5/26/96      19:06      5      VEND       0      0      0      0      0
1      10      5/26/96      19:06      1      VEND       0      0      0      0      0
1      10      5/26/96      19:07      5      VEND       0      0      0      0      0
1      10      5/26/96      19:08      1      VEND       0      0      0      0      0
1      10      5/26/96      19:08      3      VEND       0      0      0      0      0
1      10      5/26/96      19:42      3      VEND       0      0      0      0      0
1      10      5/26/96      21:18      5      VEND       0      0      0      0      0
1      10      5/26/96      21:18      5      VEND       0      0      0      0      0
1      10      5/26/96      23:35      2      VEND       0      0      0      0      0
1      10      5/26/96      23:35      2      VEND       0      0      0      0      0
1      10      5/26/96      23:36      5      VEND       0      0      0      0      0
1      10      5/26/96      23:36      2      VEND       0      0      0      0      0
1      10      5/26/96      23:39      2      VEND       0      0      0      0      0
1      10      5/27/96      11:50      1      VEND       0      0      0      0      0
1      10      5/27/96      12:53      5      VEND       0      0      0      0      0
1      10      5/27/96      12:54      5      VEND       0      0      0      0      0
1      10      5/27/96      12:54      5      VEND       0      0      0      0      0
1      10      5/27/96      13:16      3      VEND       0      0      0      0      0
1      10      5/27/96      13:17      1      VEND       0      0      0      0      0
1      10      5/27/96      13:17      3      VEND       0      0      0      0      0
1      10      5/27/96      18:21      1      VEND       0      0      0      0      0
1      10      5/27/96      18:51      5      VEND       0      0      0      0      0
1      10      5/27/96      18:52      4      VEND       0      0      0      0      0
1      10      5/30/96      18:52      4      VEND       0      0      0      0      0

SANTORA'S PIZZA                                                                                                                                                                                                                  
5271 MAIN STREET                                                                                                                                                                                                                  
WILLIAMSVILLE, NY 14221                                                                                                                                                                                                                  
                                                                                                                                                                                                                  
                                                                                                                                                                                                                  
                                                                                                                                                                                                                  
            MONTH:      MAY                                                                                                                                                                                          MONTHLY      
                                                                                                                                                                                                            TOTAL      
                                                                                                                                                                                                                  
            DAY:      W      T      F      S      S      M      T      W      T      F      S      S      M      T      W      T      F      S      S      M      T      W      T      F      S      S      M      T      W      T      F            
PRODUCT      PRODUCT      DATE:      1      2      3      4      5      6      7      8      9      10      11      12      13      14      15      16      17      18      19      20      21      22      23      24      25      26      27      28      29      30      31            
NUMBER:      NAME:      UNIT                                                                                                                                                                                                      
21      CHICKLETTS      1                                                                                                                                                2      6      5      4      2      3      5      8      35      
3999      FRUIT TARTES CHEWS      2                                                                                                                                                6      8      5      3      4      7      4      5      42      
38      GUMBALLS      3                                                                                                                                                6      10      7      5      4      7      3      9      77      
22      M&M      4                                                                                                                                                3      6      5      6      3      2      5      6      36      
37      SUPER BALLS      5                                                                                                                                                21      18      8      5      4      6      12      18      92
                                                                                                                                                                                                            
DAILY                                                                                                                                                                                                            
TOTAL                                                                                                                                                            38      48      30      23      17      25      29      46      282
                                                                                                                                                                                                            
      VEND PRICE  .25                                                                                                                                                                                                      
                                                                                                                                                                                                            
                                                                                                                                                                                                EQUIPMENT OPENED   6/4/96 10:15 AM            
      TOTAL COLLECTED THIS ACCOUNT                                                                                                                                                                                           EQUIPMENT CLOSED   6/4/96 10:35 AM            
             $70.50                                                                                                                                                                                                 
                                                                                                                                                                                                            
      COMMISSION PAID 26%                                                                                                                                                                                                      
             $18.33                                                                                                                                                                                                 
                                                                                                                                                                                                            
      NET DUE COMPANY                                                                                                                                                                                                      
             $52.17                                                                                                                                                                                                 
                                                                                                                                                                                                                              
                                                                                                                                                                                                                              
                                                                                                                                                                                                                              
                                                                                                                                                                                                                              
      LESS:                                                                                                                                                                                                                        
      PRODUCT COST:    $8.16                                                                                                                                                                                                                        
      COST OF OPS.:       $6.90                                                                                                                                                                        EXAMPLE 3                                                 
                                                                                                                                                                                                                              
                                                                                                                                                                                                                              
      NET PROFIT:           $8.25      $8.62                                                                                                                                                                                                                   
                                                                                                                                                                                                                              
                                                                                                                                                                                                                              
0
Comment
Question by:watchdog
1 Comment
 
LVL 3

Accepted Solution

by:
frazer earned 500 total points
ID: 1602915
Hi,

I can do this for yuou no problem,

can you mail me directly the txt file and the xls file so I can see clearly the two states you want to deal with.

send to:-

frazer@flying-worm.demon.co.uk

Frazer
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Some time ago I was asked to create a VBA function that would calculate a check digit for an input number, using the following procedure: First, sum up all the individual digits in the number If that sum value has more than one digit, then sum up …
Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
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 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: …

760 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

16 Experts available now in Live!

Get 1:1 Help Now