Delete rows with "C"

L

ledzepe

Good day to all,

I get a daily spreadsheet report that contains transaction numbers o
one of the columns. I need to delete transactions that ends with th
letter "C". For example, W00121212C or T00004578C or D00000023C

The normal transaction number starts with a letter then 8 numbers. I
the transaction was corrected for error then our software adds a "C" a
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 th
corrected ones
 
F

FinRazel

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"
 
D

Dave Peterson

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).)
 
B

B. R.Ramachandran

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(ISBLANK(Sheet1!A1),"",Sheet1!A1))

Regards.
B. R. Ramachandran
 
Top