I have an excel spreadsheet with the names of Companies in Column A and names
of Contacts in Column B, and additional contact information up to and
including Column H .

Can somebody give me a formula that will delete the entire row if the
company and the contact name are the same (A=B) and they are in the
spreadsheet more than once? Example:
Col A Col B
XYZ Company John Smith
XYZ Company John Smith
XYZ Company Jane Doe

In this example, I would like Row 1 to be deleted, and rows 2 and 3 to be
left in the database. Thanks

One way ..

Assuming source data is in sheet named: X,
cols A to H, from row1 down

Col A Col B
XYZ Company John Smith
XYZ Company John Smith
XYZ Company Jane Doe

In another sheet: Y (say),

Put in I1: =X!A1&"#"&X!B1
Put in J1: =IF(I1="#","",IF(COUNTIF($I$1:I1,I1)1,"",ROW()))
Select I1:J1, fill down to cover the extent of data in X

Put in A1:
=IF(ISERROR(SMALL($J:$J,ROW())),"",
INDEX(X!A:A,MATCH(SMALL($J:$J,ROW()),$J:$J,0)))
Copy A1 across to H1, fill down until blank rows appear,
signalling exhaustion of uniques data extracted from X

Freeze the values by selecting cols A to H, then do an "in-place":
Copy Paste special check "Values" OK
Delete cols I & J to clean up
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"chris" wrote in message
...
I have an excel spreadsheet with the names of Companies in Column A and
names
of Contacts in Column B, and additional contact information up to and
including Column H .

Can somebody give me a formula that will delete the entire row if the
company and the contact name are the same (A=B) and they are in the
spreadsheet more than once? Example:
Col A Col B
XYZ Company John Smith
XYZ Company John Smith
XYZ Company Jane Doe

In this example, I would like Row 1 to be deleted, and rows 2 and 3 to be
left in the database. Thanks

I'd add two helper columns:

In C1, I'd put:
=a1&char(10)&b1
and drag down

In D1, I'd put:
=countif(c:c,c1)
and drag down

Now apply data|filter|autofilter to column D.

Show the cells that are larger than 1

Delete those visible rows.

Delete columns C and D.


chris wrote:

I have an excel spreadsheet with the names of Companies in Column A and names
of Contacts in Column B, and additional contact information up to and
including Column H .

Can somebody give me a formula that will delete the entire row if the
company and the contact name are the same (A=B) and they are in the
spreadsheet more than once? Example:
Col A Col B
XYZ Company John Smith
XYZ Company John Smith
XYZ Company Jane Doe

In this example, I would like Row 1 to be deleted, and rows 2 and 3 to be
left in the database. Thanks

--

Dave Peterson

Freeze the values by selecting cols A to H, then do an "in-place":
Copy Paste special check "Values" OK
Delete cols I & J to clean up

The last 3 lines above is of course, optional <g
The formulas set-up would auto-return unique lines from X into Y
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---