Show only last row before value changes

  • Thread starter Thread starter Monica
  • Start date Start date
M

Monica

In Excel 2002 I have a looooong worksheet that records
account status for customers. Each customer can have, but
need not have, multiple records, which are created by
virtue of changes made to the accounts, and the records
are sorted first by account number and then by date. For
example, part of the worksheet might look like this:

Acct # Date Status
A00001 01/01/01 A
A00001 04/01/02 A
A00001 06/01/03 T <----
A00435 12/01/98 A
A00435 01/01/03 A <----
A12345 04/01/02 A <----
A24490 02/01/03 A
A24490 07/01/03 C <----

I want to show only the last row (pointed out above) for
each account number. I feel certain that I used to know
how to do this and that it had to do with using advanced
filter, but for the life of me I cannot come up with the
answer now.

Thanks in advance!
 
You can do this with normal autofilter. Assuming your data is in Col A, insert a new column (or
put it at the end of your columns), and then assuming your data starts in B2 and goes down, in A3
put =B3<>B4 and copy down. Then just autofilter on the TRUEs. If you don't like seeing the
TRUE/FALSEs then format the font as white and shrink the size of the column.

If you prefer you can always use a formula such as:-

=IF(B3<>B4,1,"")

which will put a 1 against the last of each set of records. Again just autofilter on the 1.
 
Back
Top