Solved

Using Excel to Automatically Retreve Data

Posted on 1998-04-23
1
151 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
[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
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Have you ever had the experience that you had to follow 10 steps over and over again every time when you need to nicely forward an important email to your manager? Fear no more! With the help of the Quick Steps feature in Outlook 2010, your old chor…
Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
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 walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…

730 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