Posts by Royzer

  • Identify rows where adjacent cells don't match a list of acceptible combinations

    Re: Identify rows where adjacent cells don't match a list of acceptible combinations




    Thanks Alan!

  • Identify rows where adjacent cells don't match a list of acceptible combinations

    [INDENT] My boss just gave me a file with over 300,000 rows to verify, so any help you can lend would really be appreciated! Here's an example of the data:



    Acct Number-----------------Description1---------------Description2

    137-000-980-98100-0000------BUILDING------------------EXPENSE
    138-000-980-98100-0000------BUILDING------------------EXPENSE
    137-000-400-40100-0000------LOCAL SALES--------------REVENUE
    138-000-400-40100-0000------LOCAL SALES--------------REVENUE


    And so forth. There should be around fifty of each Description1 and there are a ton of Desc1's.


    It works like this:


    Here is the section of the account number I have to use "980-98100". Every row with 980-98100 must also have BUILDING (descr1) and EXPENSE(descr2) with it as shown above. I have to verify that all account numbers are matched with the correct Desc1 and Desc2 for all of our companies and accounts. If I have a list of the account numbers paired with the correct descriptions to use for reference, is there a formula I can use to verify these? I can upload spreadsheet sample, if needed.


    Thanks!
    [/INDENT]

  • Need code snippet to ignore cell that is not empty

    Hi. The code below matches data from a list to a deposit schedule and when there is a match, the macro adds a date in one column and the matched amount in an adjacent column. However, there will be times when an amount has already been applied manually (but did not zero the balance) and there is a match. If this occurs I need the macro to skip this row and move on to the next matching attempt.


    Please take a look at the image I've attached to get an idea of what I am talking about. Here is the code I'm using:


    [ATTACH=CONFIG]68778[/ATTACH]

  • VBA to compare Station amounts and paste values into corresponding cells

    Re: VBA to compare Station amounts and paste values into corresponding cells


    Quote from PCI;768317

    It means the way to search is the destination range is to check column A and B, if it does, the value in column B must be copied in column D and deleted in the source data range for the corresponding set of values station & AMT
    Is it ?



    The most recent code you gave me worked great with one exception: the only cells in Column N that should be cleared are the ones where the value was pasted into Column D. Right now it is clearing the entire source range. The unmatched amounts should remain. **EDIT** That's why I wondered if a Cut and Paste would work.

  • VBA to compare Station amounts and paste values into corresponding cells

    Re: VBA to compare Station amounts and paste values into corresponding cells


    Sorry, I realize that this is asking a lot and if you don't want to continue, I understand.


    I did not think it was relevant until I was testing the file and discovered that if the same station call letters appear more than once in the Source data (column M),even with different amounts, the macro will not put the correct one into the destination range (column D). It appears to just skip the station and move on to the next one. This is a big deal because the same station may have multiple appearances with different amounts in the Source Data. If one of them matches the station in the destination range, it needs to go over there.

  • VBA to compare Station amounts and paste values into corresponding cells

    Re: VBA to compare Station amounts and paste values into corresponding cells


    forum.ozgrid.com/index.php?attachment/68730/I just tried to run the macro after expanding the data in the spreadsheet to more closely match what I will be working with, and for some reason it is putting the date on ALL the rows instead of just the ones that had the data added. On the example you sent me it worked perfectly. Please take a look at this file to see what I mean.

  • VBA to compare Station amounts and paste values into corresponding cells

    Re: VBA to compare Station amounts and paste values into corresponding cells


    Quote from PCI;768308

    See next code
    In your example, I don't see why station E don't fit



    It works perfectly! I cannot thank you enough. One thing I just thought of, though. Can the Copy command for amounts in column N be changed to Cut instead, so the source cell is left blank if used?

  • VBA to compare Station amounts and paste values into corresponding cells

    Hi.


    I have a daily report that I have to do manually which requires me to match sales amounts for each station to a list of deposits that came in the day before. If the deposit matches the station and expected amount, I have to input the deposit amount and the date. Every entry I make on a given day will require the use of the previous day's date (except for Mondays, when I have to use the previous Friday's date). I am comfortable changing the date within the code each day if I need to.


    I would really appreciate it if you would take a look at the file I've attached and see if this can be done using VBA.


    Thank you for your help!forum.ozgrid.com/index.php?attachment/68714/f

  • Compare list to master list (text) and insert rows in place of missing items

    Re: Compare list to master list (text) and insert rows in place of missing items


    Each month I run a product report which lists the revenue earned by each television station in each of 14 product categories, then I export the report and have to put that month's data into an existing template. The template has the stations listed, each with a complete set of the 14 product categories. The stations and products in the template have to be "fixed" in nature, that is, each station in the template must always have all product codes available for use. The obstacle is that the product report structure is NOT fixed. Rather than show a product as having $0 revenue in the report month, it skips the product altogether. If the report data column is pasted to the template (fixed structure), all data below the missing product line will be out of sync with the product names on the template. When that data is pulled by the other worksheets none of it will be correct.


    The last thing I was going to do was find code to delete any row in the report that had the word "Total" in it and tack it onto the end of the solution to this problem. That would complete the matching process.


    I've attached a copy of the actual template I need to paste to. Please let me know if you need anything else.



    forum.ozgrid.com/index.php?attachment/64549/

  • SUMIF pulling data correctly for all criteria except one

    After a few hours of staring at this spreadsheet I am ready to admit defeat. Please take a look at the attached file. My SUMIF works for every criteria except "Automotive". When other categories have numbers in them, the numbers show up in their respective cells like they are supposed to, but the total of all numbers will show up in the Automotive cell as well. Also, any numbers added to an Automotive cell will be doubled in the SUMIF formula cell.


    Any help you could give me would be greatly appreciated!
    forum.ozgrid.com/index.php?attachment/64546/