Need to compare two columns of data for duplicates

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Biff (biffinpitt_at_comcast.net)
Date: 08/12/04 

Date: Wed, 11 Aug 2004 19:25:08 -0700

Hi Sandie!

Try this:

=INDEX(Sheet2!A$1:A$10,SMALL(IF(ISNUMBER(MATCH(Sheet2!
A$1:A$10,Sheet1!A$1:A$10,0)),ROW(A$1:A$10)),ROW(1:1)))

Entered as an array - CTRL,SHIFT,ENTER

Copy down until you get #NUM! errors.

This will return "data" that is common (duplicated) to
both sheet1 col A and sheet2 col A.

Biff

>-----Original Message-----
>I have 2 worksheets with rows of data. I need to compare
the data in column
>1, worksheet 1, to the data in column 1, worksheet. I
need to find out if
>there is any of the data in column 1, worksheet 1 that is
duplicate to column
>1, worksheet 2, but I need the duplicate information to
appear in yet another
>column.
>Do I need to start a new worksheet with both columns from
worksheet 1 and
>worksheet 2? Does anyone have a formula that I can use to
get this
>information??
>.
>

Relevant Pages

  • RE: "Paste Special" does not work??
    ... The best way is to duplicate the worksheet. ... That will get you a duplicate copy of the worksheet with all the formatting. ... Copy the range of cells on Sheet1, ...
    (microsoft.public.excel.programming)
  • RE: Find duplicate, save in a list, delete duplicate using macro
    ... worksheet; and by having a blank worksheet for the second worksheet. ... Dim lngCnt As Long ... Dim lngCntArr As Long ... 'build the unique key for duplicate test (could just ...
    (microsoft.public.excel.programming)
  • Re: combining excel worksheets with common columns
    ... use the vlookup worksheet function in Sheet1. ... where one column is common to both worksheets. ... Sheet1 headings: job_no, name, date, address ...
    (microsoft.public.excel.misc)
  • RE: Adding additional worksheets to an existing template
    ... Click and hold on the sheet tab at the bottom, press and hold the ctrl key ... Rename the worksheet to the next users name. ... This template is onlt one worksheet and I would like to have each emploee as ... duplicate the template on each worksheet I add to the Excel Spreadsheet? ...
    (microsoft.public.excel.misc)
  • Re: Combining data from two columns
    ... 'If you want to remove duplicate names or entries, ... worksheet and select View code. ... Click General dropdown and select Worksheet. ... longer column combining all the names from each column. ...
    (microsoft.public.excel.misc)