Delete rows with "C"
Good day to all, I get a daily spreadsheet report that contains transaction numbers on one of the columns. I need to delete transactions that ends with the letter "C". For example, W00121212C or T00004578C or D00000023C The normal transaction number starts with a letter then 8 numbers. If the transaction was corrected for error then our software adds a "C" at the end of the transaction number to indicate that correction was done. However I need to generate a report on all the transactions but not the corrected ones. -- ledzepe ------------------------------------------------------------------------ ledzepe's Profile: http://www.excelforum.com/member.php...o&userid=25207 View this thread: http://www.excelforum.com/showthread...hreadid=495262 |
Delete rows with "C"
Assuming your transactions column is A, create a column with this formula:
ISERROR(FIND("C",A1,1)) This will return "False" in every case where a C appears, and "True" everywhere else. Then, sort by this column and delete all records that contain a "False" -- Anne Murray "ledzepe" wrote: Good day to all, I get a daily spreadsheet report that contains transaction numbers on one of the columns. I need to delete transactions that ends with the letter "C". For example, W00121212C or T00004578C or D00000023C The normal transaction number starts with a letter then 8 numbers. If the transaction was corrected for error then our software adds a "C" at the end of the transaction number to indicate that correction was done. However I need to generate a report on all the transactions but not the corrected ones. -- ledzepe ------------------------------------------------------------------------ ledzepe's Profile: http://www.excelforum.com/member.php...o&userid=25207 View this thread: http://www.excelforum.com/showthread...hreadid=495262 |
Delete rows with "C"
Apply data|Filter|Autofilter to that column.
Then Custom filter to show: Ends with (type c) Then delete the visible rows. (I like to keep the data, but use a filter hide those rows ("does not end with" with c).) ledzepe wrote: Good day to all, I get a daily spreadsheet report that contains transaction numbers on one of the columns. I need to delete transactions that ends with the letter "C". For example, W00121212C or T00004578C or D00000023C The normal transaction number starts with a letter then 8 numbers. If the transaction was corrected for error then our software adds a "C" at the end of the transaction number to indicate that correction was done. However I need to generate a report on all the transactions but not the corrected ones. -- ledzepe ------------------------------------------------------------------------ ledzepe's Profile: http://www.excelforum.com/member.php...o&userid=25207 View this thread: http://www.excelforum.com/showthread...hreadid=495262 -- Dave Peterson |
Delete rows with "C"
Hi,
Le's say that your data are in A2:A1001. Create a helper column (maybe in a remote location, say Z) as follows. In Z2, enter the following formula and autofill to Z1001. =IF(RIGHT(A2,1)="C","C",ROW(A1)) This formula will create C's in Column Z for the all the rows containing corrected entries in column A. Select the entire data range including Column Z (i.e., A2:Z1001) and sort by Column Z ascending. This action would clump up all the unwanted rows at the bottom. If you can afford to permanently delete those rows, simply delete that section. On the other hand, if you just want to generate a report in which those rows do not show up, but want to keep those rows for your records, do the following. Copy the top section and paste it onto another sheet, OR BETTER, use the following formula which would automatically update whenever the main sheet is sorted after corrections are made. In A1 of Sheet2 enter the following formula, and autofill down the rows and acorss the columns (to transfer all the relevant data from Sheet1) =IF(OR(Sheet1!$Z1="C",ISBLANK(Sheet1!$A1)),"",IF(I SBLANK(Sheet1!A1),"",Sheet1!A1)) Regards. B. R. Ramachandran "ledzepe" wrote: Good day to all, I get a daily spreadsheet report that contains transaction numbers on one of the columns. I need to delete transactions that ends with the letter "C". For example, W00121212C or T00004578C or D00000023C The normal transaction number starts with a letter then 8 numbers. If the transaction was corrected for error then our software adds a "C" at the end of the transaction number to indicate that correction was done. However I need to generate a report on all the transactions but not the corrected ones. -- ledzepe ------------------------------------------------------------------------ ledzepe's Profile: http://www.excelforum.com/member.php...o&userid=25207 View this thread: http://www.excelforum.com/showthread...hreadid=495262 |
All times are GMT +1. The time now is 03:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com