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

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

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

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