Solved

Using Excel to Automatically Retreve Data

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

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

724 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