Re: Search Cell for Specific Characters
Scratch that. I made an error typing in the formula. Thanks for your help. :thumbcoo:
Re: Search Cell for Specific Characters
Scratch that. I made an error typing in the formula. Thanks for your help. :thumbcoo:
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?
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.
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.
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/
Re: Formula Based on Status Code.
Thanks, Max. That works.
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.
Re: delete entire row if cell value is the same as the cell above
Mumps,
How can your macro be modified to delete the row based on date...for instance, delete the rows with the older dates while keeping the most recent?
Re: Counting by Max Date.
Would I be able to achieve this with a formula, or would it require VBA?
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"]
Account #
Date
[/td]Include Exclude
Active Inactive
[/td]Fees
[/td]1234
[/td]3/18/2011
[/td]Include
N/A
[/td]$50 Count
1234
9/9/2010
[/td]Include
Inactive
$50
4567
4/6/2010
[/td]Include
N/A
[/td]$90
4567
3/11/2012
[/td]Include
[/td]Inactive
[/td]$90 Count
[/TABLE]
Re: Formula to Determine Current Record.
Thanks for the response. The code really helped.
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.
Re: Use Specific Dates to Adjust Formula.
Batman,
Yes, that is exactly what I am looking for. Thank you so much!
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.
Re: Use List of Codes to Determine Score of Another Cell.
Thanks so much, NBVC, for the help.
Re: Use Date Column to Determine Percentage in Another Column.
GER and Batman,
Thank you both for your help. Both formulas work great. I really appreciate it.
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.
Re: Use List of Codes to Determine Score of Another Cell.
Quote from NBVC;729716Try:
=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.
Re: Use List of Codes to Determine Score of Another Cell.
Thanks for your help. The formula works just fine.
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.