software

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

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

In another sheet: Y (say),

Put in I1: =X!A1amp;quot;#quot;amp;X!B1
Put in J1: =IF(I1=quot;#quot;,quot;quot;,IF(COUNTIF($I$1:I1,I1)gt;1,quot;quot;,ROW()))
Select I1:J1, fill down to cover the extent of data in X

Put in A1:
=IF(ISERROR(SMALL($J:$J,ROW())),quot;quot;,
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 quot;in-placequot;:
Copy gt; Paste special gt; check quot;Valuesquot; gt; OK
Delete cols I amp; J to clean up
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;chrisquot; gt; wrote in message
...
gt; I have an excel spreadsheet with the names of Companies in Column A and
names
gt; of Contacts in Column B, and additional contact information up to and
gt; including Column H .
gt;
gt; Can somebody give me a formula that will delete the entire row if the
gt; company and the contact name are the same (A=B) and they are in the
gt; spreadsheet more than once? Example:
gt; Col A Col B
gt; XYZ Company John Smith
gt; XYZ Company John Smith
gt; XYZ Company Jane Doe
gt;
gt; In this example, I would like Row 1 to be deleted, and rows 2 and 3 to be
gt; left in the database. Thanks
I'd add two helper columns:

In C1, I'd put:
=a1amp;char(10)amp;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:
gt;
gt; I have an excel spreadsheet with the names of Companies in Column A and names
gt; of Contacts in Column B, and additional contact information up to and
gt; including Column H .
gt;
gt; Can somebody give me a formula that will delete the entire row if the
gt; company and the contact name are the same (A=B) and they are in the
gt; spreadsheet more than once? Example:
gt; Col A Col B
gt; XYZ Company John Smith
gt; XYZ Company John Smith
gt; XYZ Company Jane Doe
gt;
gt; In this example, I would like Row 1 to be deleted, and rows 2 and 3 to be
gt; left in the database. Thanks

--

Dave Peterson

gt; Freeze the values by selecting cols A to H, then do an quot;in-placequot;:
gt; Copy gt; Paste special gt; check quot;Valuesquot; gt; OK
gt; Delete cols I amp; J to clean up

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