Posts by Yeppy12

  • Search Cell for Specific Characters

    Re: Search Cell for Specific Characters


    Thanks, NBVC, for your response. I have a question for you. I tried your formula on the column, and it returned all values as true. Does your formula indicate whether or not one of the letters is in the cell?

  • Search Cell for Specific Characters

    I have a spreadsheet that contains a column with 4 numbers/characters. For example, "AB2C", "2222", "YYYY", etc. I want to check each cell in the column to see if the cell contains any of these letters: B,C,D,E,F,G,H,J,K,L,M,N,O,P,R,S,T,U,V,W,X. Is there a formula that can do that? If so, what do I need to do? Any help is greatly appreciated.

  • Importing Text without Delimiters.

    Re: Importing Text without Delimiters.


    gijsmo,


    Thank you for your response. I checked out the file you attached, and it works just fine. It will definitely save me a lot of time. I just need to figure out to separate the data into columns now. Thanks again. :)

  • Importing Text without Delimiters.

    I receive a text file each day that has no delimiters separating the records. As far as I can tell, though, each record ends with "CURRENT TIME ##:##:##"



    What I would like to do is import this text file into Excel each day and separate the records into rows and columns. I have tried using text to columns, but it
    does not work that well because there are no delimiters, so I end up having to try to line up the columns manually. If I could just get the
    file into Excel with one record per row and the data in columns, that would save so much time for me. Any idea as to how I can do that?



    I have attached a sample of what the file looks like. Any help is greatly appreciated.
    forum.ozgrid.com/index.php?attachment/71118/

  • Formula Based on Status Code.

    For each unique ID, I want to perform the following calculation: Balance - (all of the transactions for that ID). The tricky part is I want to add some of the transactions to the balance if the status is a certain number and subtract it from the balance for certain status numbers.


    For example, for ID A, the balance is $61, but it has three transactions for $2, $1, and $20. The status codes for those transactions are 50, 60, and 70, respectively. I want to subtract the transactions with codes 50 and 60 but add the transaction for status 70. The resulting balance would be $78. How can I go about doing this calculation? Any help is greatly appreciated.

  • Counting by Max Date.

    I have a spreadsheet that has lots of records. I want to keep all of the records, but I only want to count certain ones. One column, called Active Inactive, contains the values 'Inactive' or 'N/A'. I want to count the records based on the value in this column based on the most recent date in the Date column. For example, if a record is repeated twice, where only the dates and values in column Active Inactive differ, I want to count the record with the most recent date. I have attached a file to better illustrate what I need. Any help is greatly appreciated.


    [TABLE="width: 465"]

    [tr]


    [td]

    Account #

    [/td]


    [td]

    Date

    [/td]


    [td]

    Include Exclude

    [/td]


    [td]

    Active Inactive

    [/td]


    [td]

    Fees

    [/td]


    [/tr]


    [tr]


    [td]

    1234

    [/td]


    [td]

    3/18/2011

    [/td]


    [td]

    Include

    [/td]


    [td]

    N/A

    [/td]


    [td]

    $50 Count

    [/td]


    [/tr]


    [tr]


    [td]

    1234

    [/td]


    [td]

    9/9/2010

    [/td]


    [td]

    Include

    [/td]


    [td]

    Inactive

    [/td]


    [td]

    $50

    [/td]


    [/tr]


    [tr]


    [td]

    4567

    [/td]


    [td]

    4/6/2010

    [/td]


    [td]

    Include

    [/td]


    [td]

    N/A

    [/td]


    [td]

    $90

    [/td]


    [/tr]


    [tr]


    [td]

    4567

    [/td]


    [td]

    3/11/2012

    [/td]


    [td]

    Include

    [/td]


    [td]

    Inactive

    [/td]


    [td]

    $90 Count

    [/td]


    [/tr]


    [/TABLE]

  • Formula to Determine Current Record.

    I have a spreadsheet that has a lot of dupes. There are 4 columns: Account Number, Current Code, Previous Code, and Transfer Code. For each account number, I only want to keep the record where current code = transfer code. In some cases, the current code will have a ~ instead of an actual code, so when that happens, I need to keep the record where previous code = transfer code. Any ideas on how to do this? I have attached a file with examples of what I need. Any help is appreciated.

  • Use Specific Dates to Adjust Formula.

    I have a file with dates in columns A and B. Column C just calculates the number of days between the 2 date columns, and Column D scores the values in Column C using an if statement. Column A is called Help Date, and it has a range from the 21st to the 20th...for example, 10/21/14 to 11/20/14. What I want to change is the formula in D to say if the Help Date is the 16th, 17th, 18th, 19th, or 20th of the month, then the value in D should be 1. If the Help Date is not one of those values, then the original if formula based on the value in Column C should be used.


    Two questions: 1. how can I do this? 2. is it possible for the formula to be generic enough so that I do not have to change it monthly (e.g., to look for the 16th, 17th, etc. instead of 11/16/14, 11/17/14, etc.)?


    I have attached a file with an example. Any help is greatly appreciated. Thanks.

  • Use Date Column to Determine Percentage in Another Column.

    I have this file with three date columns. The first date column is Decision Date, and that column will either have a date or be zero.
    I use the other two date columns to calculate the number of days between them in another column, column D. What I want to do in column E is the following:
    if the Decision Date is 0 and the number in column D is between 0 and 90, then E2 should be 75%; if the number in D is between 91 and 150, E2 should be 50%;
    if the number in D is >150, then E2 should be 25%.


    I want to do the same thing when the Decision Date column has dates in it, with the following changes: if the number in D is between 0 and 210, E2 is 75%; if D is between 211 and 335, E2 is 50%; if D is >336, E2 is 25%.


    I have attached a file with an example. Any help is greatly appreciated.

  • Use List of Codes to Determine Score of Another Cell.

    Re: Use List of Codes to Determine Score of Another Cell.


    Quote from NBVC;729716

    Try:


    =IF(OR(AND(OR(B7={"PEN","LEN","YEN"}),N7>=75%),AND(OR(B7={"GOQ","AOQ","ZOQ"}),N7>=50%),AND(OR(B7={"LHJ","BVW","DKU"}),N7>=35%)),1,0)


    you can wrap IFERROR() around to remove the DIV/0 error.



    Is there a way to use the formula above with a range instead of actual codes? For example, instead of B7 ={"PEN", "LEN","YEN"}, I want to replace the codes with a range of numbers...B7 ={0-90}. In other words, I want to alter the formula to say if B7 has a number between 0 and 90, then (rest of formula)...


    If there isn't a way to alter the formula above, then is there another way to achieve what I want? Again, any help is greatly appreciated.

  • Use List of Codes to Determine Score of Another Cell.

    I have a list of codes that I want to incorporate into an Excel spreadsheet. Column N currently has a formula that divides 2 numbers to get percentages, and column O uses an If statement to score column N values >= 40%. For example, if cell N8 is 55%, then cell O8 will be 1. Column B contains codes that I want to use to change the scoring. I want to do the following: if B8 has one of the codes PEN, LEN, or YEN, then O8 should be 1 if N8 >= 75%; if B8 has one of the codes GOQ, AOQ, or ZOQ, then O8 should be 1 if N8 >= 50%; if B8 has one of the codes LHJ, BVW or DKU, then O8 should be 1 if N8 >= 35%. How can I do this? Any help is greatly appreciated. I attached a file to show the current formulas.