Posts by darkyam

  • Calculate Break Even Point Of Ticket Sales

    Re: Solver - To Set Ticket Prices


    I'm a little confused why you feel you can't use formulas. If you know the number of member and non-member tickets, then the formula is =expenses/(<lower of 200 or # of member tickets>*.75+200-that number of tickets). This will get you the price of a full ticket. 75% of this number would, of course, be the cost of a member ticket.
    For example, if expenses of $5000 are entered in A1, and 150 member tickets are available (with this number in B1), then =A1/(B1*.75+200-B1) gets you $30.77 per non-member ticket. 30.77*.75*150+30.77*50=$5000.

  • Extract Text After Specific Keyword

    Re: Extract A Text After A Specific Keyword


    I assumed the column of data you had is in column A and the formula I suggested was in column B. The cell would be blank if REFDES is not found, but would have R3099 or U079, etc., if it was. Bill's formula also works, but requires you to take the same steps after you've used it to get the data into one row.


    For both of our formulas, you will have to drag the formula down the length of the array. Highlight the cell, move the mouse to the lower right-hand corner of it until it becomes a black cross, then click, hold, and drag down as far as necessary.

  • Extract Text After Specific Keyword

    Re: Extract A Text After A Specific Keyword


    Don't know the macro way, but you could do =IF(ISERROR(FIND(A1,"REFDES")),"",Right(A1,Len(A1)-FIND(A1,"="))). That will kick out the value you need in B. After that, you could use filters to not show blanks in B, highlight it, click Ctrl+;, copy and Paste Special (selecting Transpose and Values) where you need the info to be.

  • Change Formula Reference Each Month

    Re: Range To Increase Based On Critiera


    Not sure how to improve the formula itself (aside from removing the unnecessary + at the beginning), but you could just highlight it, Ctrl+H, F in the first box, I in the second, and click Replace. You would just do this every month.

  • Remove Trailing Commas

    Re: Removing Trailing Commas


    Did the formula I give you not work? You could add another IF loop if there are cells that don't end with a comma, but I think what I put in should do the job for you if they all end in at least one comma.

  • Group Every Nth Row

    Re: Increment By 1 The Variable in Every 503 Rows


    1. Yes, it is normal. It will return either True or False. You would drag this down through all 503,000 rows.
    2. On the Home tab, toward the right is a sort and filter button. Hit Ctrl+A, then this button, then select Filter. In B1, filter to TRUE.
    3. Step 2 will give you 1,000 rows as the formula will be true only 1000 times.
    4. This is not a macro; a macro would do this all automatically. You did not request this text to be entered in your original post. If you need that as well, I could suggest another couple of steps, but you would probably be better off going for a macro at this point. I'd help, but I'm not good enough to do the second part of what you want yet.

  • Group Every Nth Row

    Re: Increment By 1 The Variable in Every 503 Rows


    As a macro-free solution, in column B, you can put =MOD(ROW(),503)=0 and drag that down. Apply filters to the sheet and filter to TRUE. Highlight all 1000 rows, hit Ctrl+; to select only visible cells, then Alt, I, R to insert rows. Then delete column B.

  • Retain Values Common In 2 Column Lists

    Re: Match Values In Two Columns


    If your first list is in A1:A4000 and the second is in B1:B300, C1 could have =VLOOKUP(B1,A$1:A$4000,1,False). That will return an error message for every item in B that doesn't show up in A. In D1, =VLOOKUP(A1,B$1:B$300,1,False). Then you can just filter to the error messages and delete.

  • Hourly Rate Divided By Total Time

    Re: Currency Divided By Time


    No, I mean if you post in another forum, add a link to the thread on that forum here and vice versa. If the problem is solved somewhere else, people here should know so that they aren't wasting their time on it.


    As for your problem, please post a workbook if at all possible. If none of the things you have tried have worked, then the problem will most easily be solved by seeing the workbook that is causing the issue.