Posts by AFord66

  • Counting Unique Rows (FREQUENCY)

    Re: Counting Unique Rows (FREQUENCY)


    The photo was pretty much it skywriter, the graphs etc. make the workbook itself far to large for an attachment here, so I'll include a sample with correct formatting Column A - General and Column B - Percentage (0 decimal spaces) not much more to it, just trying to figure out how to count the percentages over 0% ignore blanks and only count PINs that repeat once.


    Thanks!forum.ozgrid.com/index.php?attachment/70157/

  • COUNTIFS that exclude multiple entries

    In the screenshot below you'll see several cells that contain similar and different information


    I'm trying to write a COUNTIFS formula that will include "SD: Ford" and "SD: Lindsey" but exclude "SD: Ford IU: Munsell", "PM: Elam IU: Munsell", "AD: Vedder SD: Ford IU: List", etc...


    I've tried several formulas and although this seems like it should work it's still not counting them correctly:


    Code
    =COUNTIFS('Sheet1'!A:A,"SD: Ford",'Sheet1'!A:A,"SD: Lindsey",'Sheet1'!A:A,"<>*IU: Munsell*",'Sheet1'!A:A,"<>*AD: Vedder*")



    [ATTACH=CONFIG]66823[/ATTACH]

  • Index match

    This is probably simpler than I'm making it and that might be adding to my confusion.
    I'm trying to write a formula that compares information from column A on sheet 1 with data from column A on sheet 2 and if it matches returns the value from the corresponding row in column C on sheet 2 to column C on sheet 1.
    [ATTACH=CONFIG]64604[/ATTACH][ATTACH=CONFIG]64605[/ATTACH]

  • Find info in one cell and copy it to another

    Re: Find info in one cell and copy it to another


    Pike,


    I've been testing this out and it appears it not working is related to the preceding paragraph:


    Entered on 10/20/2014 at 13:06:39 EST (GMT-0500) by Adam Ford:


    Hello Bob, We have completed the account set up for John Smith. Please keep in mind it may take up to 24 hours to access their email or the other accounts with these credentials. If this employee already exists, it may take an equal amount of time to sync with the email address as long as the names are spelled identically. To ensure the most effective training experience, please direct your new employee to the Quickstart Training online and have the trainer follow the Training Companion Guide. For LMS access please go to the following link:


    What would I add to skip past this or trim it from my search?


    Thanks

  • Find info in one cell and copy it to another

    Re: Find info in one cell and copy it to another


    Thanks Pike, but that doesn't work with all of the text before "BUSINESS\JohnS0000..." If the line was simply: BUSINESS\JohnS0000 password: Changeme14 e-mail address: &amp;amp;quot;[email protected]&amp;amp;quot;[/email]
    that works great. With all of the preceding text, the output is an empty cell.


    This paragraph precedes it:


    Entered on 10/10/2014 at 13:06:39 EST (GMT-0500) by Adam Ford:


    Hello Bob, We have completed the account set up for John Smith. Please keep in mind it may take up to 24 hours to access his email or the account with these credentials. If this employee already exists, it may take an equal amount of time to sync with the email address as long as the names are spelled identically. To ensure the most effective training experience, please direct your new employee to the Quickstart Training online and have the trainer follow the Training Companion Guide. For LMS access please go to the following link:


    Then a new line in the cell starts the original text a posted above.

  • Find info in one cell and copy it to another

    Re: Find info in one cell and copy it to another


    Column A contains:


    Entered on 11/03/2014 at 08:08:02 EST (GMT-0500) by Adam Ford:
    Hello Bob, We have completed the account set up for John Smith. Please keep in mind it may take up to 24 hours to access his email with these credentials. User ID: BUSINESS\JohnS0000 password: Changeme14 e-mail address: &amp;amp;quot;[email protected]&amp;amp;quot;[/email] You may reply to this e-mail if you have any questions about these accounts. Thank you, Adam Ford


    I want to find "e-mail address: &amp;amp;quot;[email protected]&amp;amp;quot;[/email]" and copy the email only "John.[email protected]" to the same row in column B


    Each row will have a different email address than the previous, but the same domain.

  • Find info in one cell and copy it to another

    What is the simplest way to find a string of text in one cell that is full of text and copy it to another?


    For ex. I have 200 words in a cell with "e-mail address: [email protected]" contained somewhere in it.
    Is there a way to make a formula find that line (amount of characters could vary with different names) and then copy it to another cell?

  • Finding specific cells and adding the number within from an Outline

    Re: Finding specific cells and adding the number within from an Outline


    Sometimes we so over think things! I'm sitting here trying to figure out why the latest still wasn't producing the desired results when an old philosophy I teach my children came to mind! K.I.S.S. Keep it Simple Stupid!


    I applied filers to columns G for filtering by zip code and column H for filtering out every remaining row but the Totals.


    Q10s formula then became =SUBTOTAL(109,L10:L3851)


    Produces the desired result every time! Now if I could just figure out adding the macro to hide the other filters and locking col H to show Totals all of the time.


    Thanks Luke!

  • Finding specific cells and adding the number within from an Outline

    Re: Finding specific cells and adding the number within from an Outline


    Absolutely brilliant Luke, thanks a million! I found why it wasn't working before your last post. My zip code column was formatted as a zip code instead of plain number.


    How would I make this formula repeat itself down the column to find the occurrence in every month as opposed to just January?

  • Finding specific cells and adding the number within from an Outline

    Re: Finding specific cells and adding the number within from an Outline


    Thanks Luke, I understand where you're going, but my amateur Excel status is still struggling with where I'm going wrong!


    =INDEX(L10:L3848,MIN(IF(H13=OFFSET(INDEX(G10:G3848,MATCH(Q10,G10:G3848,0)),0,1,100),ROW(OFFSET(INDEX(G10:G3848,MATCH(Q10,G10:G3848,0)),0,1,100)))))


    I've attached a shot of the spreadsheet. Looking at Col G, Zip code in Q10, Word in Cell H13 (Total), sum of numbers that appear in Col L


    [ATTACH=CONFIG]60284[/ATTACH]

  • Nested find within a range formula

    Re: Nested find within a range formula


    That works great, is there a trick to auto filling that type of formula.


    I removed the absolute references on the columns in the array, now the problem is that it pulls data from a cell between AH$1:AN$80 and then the next cell should be pulling from CZ$1:DF$80 with a break in columns that far apart for the rest of the workbook. It would be nice to just auto fill it instead of hunting for the 7 columns for each cell range through 14 sheets!