1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

needed simple excel macro

Discussion in 'Business Applications' started by ak1, May 31, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. ak1

    ak1 Thread Starter

    Joined:
    May 31, 2007
    Messages:
    8
    I need a simple excel macro for my work. I have tried looking it up online but I cannot get how to build one and I am in dire need of one:) I would love it as well if the person who gives me the macro could explain something about it.
    Here is what I need. I need a macro that I can change as needed to write coins in Excel.
    Example: you have A1, A2, A3, A4 as 0.25, 0.10 , 0.05, 0.01. These are of course quarters, nickels, dimes...
    You have B1-B4 as the number of each type of coins that you currently have. I want a macro that when you put in B1-B4 the number of coins you have to tell you in C1-C4 how many whole rolls of coins(different coins have different rolls, quarters in 40's, nickels in 40's, pennies in 50's..etc) you have 0 to however many and in D1-D4 how many loose coins you have.
    Again any help would be greatly appreciated and I would very much so contribute to y'alls site as well
     
  2. ak1

    ak1 Thread Starter

    Joined:
    May 31, 2007
    Messages:
    8
    It might be that I don't need a macro I just need something else. The it support guy at my work said I need a macro but didnt offer anything else helpfull. Basically as I enter the number of coins I want the result in whole rolls and any remaining number of loose coins to show up as I enter data without me doing anything else.
    I tried functions such as C1=B1/40 to display rolls of coins but of course it just displays if you have 20 quarters that you have 0.5 rolls. I want something that if I put in 20 quarters in A1 it will tell me I have 0 rolls and 20 quarters and that I can customize for any coins,. $,nickels, dimes..etc in any cell of the table.
     
  3. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Welcome to TSG. :) As far as I can tell, you don't need a macro -- either your IT guy is wrong or you're not giving us the full picture.

    Let's take "quarters come in rolls of 40" and B1 = "number of quarters that you currently have". Try this in C1:

    =INT(B1/40)

    and this in D1:

    =B1-(C1*40)

    These give you the following results (B1 -- C1 -- D1, or # of quarters you have -- # of rolls needed -- # of loose needed):

    39 -- 0 -- 39
    40 -- 1 -- 0
    41 -- 1 -- 1

    Post back if this isn't what you want.
     
  4. ak1

    ak1 Thread Starter

    Joined:
    May 31, 2007
    Messages:
    8
    I used a different function . rounddown which I happened to find while searching through things.
    you know rounddown(quarters number/40,0) gives you the whole rolls of quarters.
    Anyways.. I donated some to your guys's site already and I got another question this time I definately(think) need a macro for.
    Say that you got a huge table (think 3-500 entries like this:
    Invoice cardtype cardnumber ammount charged
    The invoices represent the cashregisters but the table is all jumbled in the order the transactions take place.
    What is the quickest way macro or no macro to get the table ordered by register and by card type so like:
    Register 1 AX AX AX AX DS DS MC MC MC MC MC VS
    Register 2 AX DS DS DS DS DS MC VS VS VS VS VS..etc
    while at the same time putting in 4 spaces between each block of cash register receipts automatically and doing the following as an example of what I need.
    Register 1 VS 4555XXXXX8485 212.45
    space AX space and then the sum of all amex ammounts for reg 1
    space DS space and then the sum of all discover ammounts for reg 1
    space MC space and then the sum of all mastercard ammounts for reg 1
    space VS space and then the sum of all visa ammounts for reg 1
    Register 2 AX 3717XXXXXX1001 45.54
    At my work the IT department is rather flimsy on this and how I do this at the moment takes me a lot longer than it should(i sort, arrrange copy paste manually).
    Any suggestion would be appreciated hoping for a complete solution to this cute problem
     
  5. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    You may be able to get away with a pivot table -- or at least a pivot table with a macro to do the final formatting. Subtotals might be another option.

    I understand your example mostly, but not this part:

    Register 1 VS 4555XXXXX8485 212.45
    ...
    ...
    ...
    space VS space and then the sum of all visa ammounts for reg 1


    by which I mean I don't understand why VS (Visa?) should feature in the top row of the group and in the bottom row. Nor do I understand why card number appears in the top row but not in the others -- are the top rows something "special"?

    Please upload a sample workbook containing (a) a fake table (10-20 made-up entries) (b) the final layout you require for those entries.
     
  6. ak1

    ak1 Thread Starter

    Joined:
    May 31, 2007
    Messages:
    8
    Basically the cash registers have transaction numbers as you see the first ones would be register 0 some would be 3, some 8, some 10 , got a total of 12.
    pretty easy to figure out if you analyze it which is which cash register, very hard to explain to excel which belong to which cash register in ranges like 00034545 which is in my table is cash register 3 so would however be 00035599 .
    There is nothing special about that row what i want basically is for the cards to be sorted first of all in their individual cash registers by type from that mumbo jumbo there I uploaded i want to have you know:
    cash register 0 AX
    cash register 0 AX
    cash register 0 AX......
    down to cash register 0 VS(this being a visa transaction and alphabetically VS comes after AX,MC and NS(which stands for discover cards:)) and this last visa transaction being the last in my table for cash register 0. you know you have your visas and then the table for that cash register ends.
    In the end of the tables down at the very bottom being of course if I had any sales
    cash registers 12 VS .........
    In between each cash register what I want to do is to have the ammounts on the cards tallied up and written up.
    so:
    AX 4324.44
    MC 44322.04
    NS 32432.44
    VS 43432.33
    those would say be for cash register 0
    and then the very next line being
    cash register 1 AX 3615XXX9432 432.44 as an example.
    hope that help.s
     

    Attached Files:

  7. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Your upload included some made-up entries but not the final layout you require for those entries. Let me concentrate on one part of your post.

    Basically the cash registers have transaction numbers as you see the first ones would be register 0 some would be 3, some 8, some 10 , got a total of 12. pretty easy to figure out if you analyze it which is which cash register, very hard to explain to excel which belong to which cash register in ranges like 00034545 which is in my table is cash register 3 so would however be 00035599.

    The made-up entries in your upload had data for "Invoice" but not for "cash register". If you wish to summarise at all by "cash register" you'll need to create an additional field for that. You ought to be able to use VLOOKUP function to generate cash register field data from "Invoice" but, once again, it's all a bit vague I'm afraid. :(
     
  8. ak1

    ak1 Thread Starter

    Joined:
    May 31, 2007
    Messages:
    8
    how I would like to have the sample sheet look like in the end when finished. If it confuses you a lot , I tried to explain it by I am explaining it again. There are 12 cash registers 1-12. The program I use is to let me compare credit cards versus what I have on z our reports to see if all of them went through or not. By doing it manually it takes me like 1 hour each big table. Coming back to the cash registers. The program does not simply list the cash registers when transactions happen it assigns a particular invoice number. That is that first column. The invoice number is directly corellated to the cash register like 000854532 would be register 8 8 being the leading number whereeas 000013245 would not be register 1 but 0 as you see in the place where there was a 8 before now there is a 0.
    When transactions occur the program registers them as they occur so one may occur on register 6 the next one on 3 and so on. Hope this helps if not I will post the sample table solved on Tuesday as I am still at work everyday and solving it by hand takes time:).
    Cheers.
     
  9. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    I believe we are getting somewhere, ak1. (y)

    I do understand "The program does not simply list the cash registers when transactions happen it assigns a particular invoice number". But if you wish to summarise or even analyse your entries by cash register, you will need a method to identify the "parent" register for each entry.

    Which brings me to "The invoice number is directly corellated to the cash register like 000854532 would be register 8 8 being the leading number whereas 000013245 would not be register 1 but 0 as you see in the place where there was a 8 before now there is a 0."

    Now I kind of understand that, but one thing is that 000854532 (from register 8) has 8 digits yet 000013245 (from register 0) has 9 digits. So that's one thing that would have to be looked at. Another thing that would have to be looked at is for (say) register 12, would the invoice numbers start 0012 or 00012?

    I look forward to seeing your sample table. :)
     
  10. ak1

    ak1 Thread Starter

    Joined:
    May 31, 2007
    Messages:
    8
    It's kinda hard creating a sample table from memory.:)
    The actual table has all the numbers in proper form meaning that yes, a 3 number at the end only would be register 0 . How the actual rules go is that you add a number to each cash register you go to. cash register 1 is a 4 digit number starts with 1 register 2 is a 5 digit number starts with 2..etc. up to register 12.
    Heres the sample table finished.
     

    Attached Files:

  11. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Thanks for that.

    Please see the attached. I've added a sheet named "Summary", and some dummy register numbers in column E on "Entries". I've also added some formulas in Entries!H1:H4 to sum by card type depending on register number in G5. The G5 value updates sequentially as the macro runs, but you can test this/the formulas manually by changing G5 to any number from 1 to 6 (as per column E). G6 is a plain COUNTA, this is used to dimension the ranges in the H1:H4 formulas.

    You can see that Summary is blank to begin with. Select Entries, then run Macro1. This should parse the entries to Summary in the format you require.

    The only missing piece is a method -- a formula perhaps -- for column E to create register number by analysis of invoice number.

    Post back when you've had a chance to try it out.
     

    Attached Files:

  12. ak1

    ak1 Thread Starter

    Joined:
    May 31, 2007
    Messages:
    8
    I opened up your excel and I have a very high security level in excel.. any idea about that? It does not allow me to see macros.
     
  13. bomb #21

    bomb #21

    Joined:
    Jul 1, 2005
    Messages:
    8,546
    Set it to Medium via Tools > Macro > Security.
     
  14. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/579524

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice