remove dublicate rows

[johnfli]

I have a spread sheet that has about 1500 rows.
several of these rows are duplicates.

The columns go from A to G

I already sorted everythign by column A

I would like a macro that can go through and find out the rows that are a
duplicate or the row above it.
For it to be a valid dublicate, it has to be a dublilcate of all the columns
too

Any ideas?

Doing this manually bites.

 

[Dave Peterson]

How about using a helper column with this formula (starting in H2):

=SUMPRODUCT(--((A1:G1)=(A2:G2)))

Then drag down.

Now apply Data|filter|autofilter on that column.

Show only those rows that equal 7 (a:g).

delete those visible rows.

 

[Dave Peterson]

And you could have a macro that does the same kind of thing (or even inspects
cell by cell), but I think it would take longer to find the macro than to do the
formula|filter|delete.

 

[Frank Kabel]

Hi
and in addition to Dave's suggestions you may have a look at
http://www.cpearson.com/excel/deleting.htm

 

[LEB]

What is the purpose of the two dashes in the formula? i
note that it doesn't work without them.

 

[Frank Kabel]

Hi
they coerce the boolean values to a number:
TRUE -> 1
FALSE -> 0

An alternative way would be
=SUMPRODUCT(1*((A1:G1)=(A2:G2)))
or
=SUMPRODUCT(0+((A1:G1)=(A2:G2)))

 

[LEB]

Is this documented somewhere? I understand the theory,
but I'd like to read more about it. I can't find any
reference to it in Excel Help.

 

[johnfli]

I must be doing something very very wrong.
I put that formula in there (Starts @ H2 and drag down.

I get some zeros, some ones, 3's, and 5's
There doesn't seem to be any reason, as it does it on duplicates and non
duplicats.

 

[johnfli]

Works great!!!
Thank you very much.

It really helped when I read ALL the posting and saw teh comment about '7'

 

[JanetW]

Why all the macros and formulas? Just use:

Data > Filter > Advance Filter...

Leave the criteria range blank and check "Unique records only". Als
select "copy to another location" if you want. Click OK and you'r
done! It will return a list of each unique row in your dataset.

K-I-S-S !

 

[Dave Peterson]

Try a little experimentation:

put 7 (in A1)
put =-A1 (in B1)
put =-B1 (in C1)
put =--A1 (in D1)

now change A1 to True or False and watch what you get in those other cells.

If A1 is a number, you'd be saying:
Give me the opposite of the opposite of that number.

With booleans (true/false), the first negative sign converts it to a numeric
value (-1 or 0) and the second negative sign changes the sign (--1 = 1 & --0=0).