At 21:52 18/09/2007 +0100, Joseph "John" K wrote:
Given a sheet of about 2000 rows with the first column containing
numbers sorted into ascending order but with the same number
sometimes appearing in more than one row, what is the easiest way to
remove rows with the duplicated numbers leaving only one instance of
each number. Columns 2 onwards may differ row to row even if the
cell in column 1 is the same. I've tried all sorts of ways to do
this but seem to end up with something far too complex.
I have two suggestions.
If the values are in column A, put in row 1 of some other suitable column
=IF(COUNTIF(A$1:A$2000;A1)>1;"Duplicate!";"")
and copy this formula down that column. What does this do? It
searches the cells A1 to A2000 for the value in A1 (which becomes A2,
A3, etc. as you copy it down the column) and counts how many
instances there are. There will always be one, of course: the cell
itself. If there are others, the COUNTIF will find more than one,
and the warning message will appear; otherwise the cell will be left empty.
You can do a similar trick with conditional formatting. Select the
column with the values, and go to Format | Conditional Formatting...
. For Condition 1 select "Formula is" and then put
COUNTIF(A$1:A$2000;A1)>1
in the box to the right. (Again, the A1 becomes A2, A3, etc. for
cells down the column.) For Cell Style, choose something different
from your normal cell style. Now all duplicate values will be
displayed in a different style from the rest of the values. You
could create a style with a different font, size, colour, background
colour, and so on - or perhaps use the existing styles Heading (which
will show the values larger than the default size) or Result (which
will show them bold, italic, and underlined).
In each case, of course, you would then need to delete duplicate rows
manually. And again in each case, the marker would disappear from
the other duplicate as soon as you deleted one (or all but one) of
them. In the first case, if you wanted a double check you could
construct a cell somewhere to count the number of instances of
"Duplicate!", so that you could be sure when you had removed all of them.
As you will appreciate, both these techniques work even if the values
are not sorted. I've tested them with 2000 rows, and the process
does not seem unreasonably slow.
I trust this helps.
Brian Barker
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
