Results 1 to 3 of 3
  1. #1
    martinrrrr
    Guest

    Caomparing two databases (XL2000SR1)

    I have two spreadsheets and I need to compare the 1st against the 2nd. These DBs have one column in common and the other columns have different data. I need to extract a consolidated duplicate value from the 1st and 2nd. I know this is simple, but I am drawing a blank.

    Thanks for any help,

    Roger
    Excel 2000
    Win98

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Caomparing two databases (XL2000SR1)

    I'm not sure what you mean by "consolidated duplicate value." However, if what you want to do is just flag the rows in one of the sheet where the value in one column is duplicated in the other, then you could probably use VLOOKUP in a formula that could be filled down an empty column to look up each value in the column in the other workbook. The formula could probably put the word Duplicate in the cells where the value is duplicated in the other sheet, or blank if it is not.

    If the data is in column A in Sheet1 and Sheet2, then something like this could be filled down an empty column:

    [pre]
    =IF(ISERROR(VLOOKUP(Sheet1!A1,Sheet2!$A$1:$A$9,1,F ALSE)),"","Duplicate")
    ]/pre]
    Legare Coleman

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Caomparing two databases (XL2000SR1)

    Equivalent to Legare's suggestion (here I assume the first range is on Sheet2 from A1 to A20 and you want to compare the data on Sheet1 in cells A1 down to A... Just put this formula in B1 on Sheet1 and fill down

    =IF(COUNTIF(Sheet2!$A$1:$A$20;A1)>=1;"Duplicate";" ")

    (Replace the semicolons by commas if needed >> depends on your regional settings listseparator)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •