remove dublicate rows

J

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

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

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.
 
L

LEB

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

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)))
 
L

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.
 
J

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.
 
J

johnfli

Works great!!!
Thank you very much.

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

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

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