Results 1 to 5 of 5

Thread: Extracting Unique Data

  1. #1
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    12

    Extracting Unique Data

    I have data in Column A and in Column E from row 2 onwards.

    1) I would like to set up a formula in column F compare the data in Column E to Column A and where data exists in Column E, but not in Column A, I would like this extracted
    2) I would like to set up a formula in column G compare the data in Column A to Column E and where data exists in Column A, but not in Column E, I would like this extracted
    3) I would like to set up a formula in Column H or VBA code to compare column A to Column E and extract the numbers that are unique to both

    your assistance is most appreciated
    Attached Files Attached Files

  2. #2
    Member littleiitin's Avatar
    Join Date
    Aug 2011
    Posts
    90
    Rep Power
    13
    Hi,

    Welcome to the board....

    Plaese find the below solution...


    PASTE Below formula in F2 and Drag it down:
    Code:
    =IF(COUNTIF($A$2:$A$18,E2)>0,E2,"")
    PASTE Below formula in G2 and Drag it Down:
    Code:
    =IF(COUNTIF($E$2:$E$18,A2)>0,A2,"")
    for Unique : You can use below formula H2 nd drag it down in:...

    Code:
    =IF(COUNTIF($A$2:$A$18,E2)=0,E2,IF(COUNTIF($E$2:$E$18,A2)=0,A2,""))

    and Below Code is for Unique One:Below Code Will work if you have 2007 and above version..
    Code:
    Sub FindUnique()
        
        Dim lngLastR    As Long
        Dim rngAutoFil  As Range
        
        With ThisWorkbook.Worksheets("sheet1")
            lngLastR = .Range("A" & .Rows.Count).End(xlUp).Row
            .Columns("I:I").Insert
            .Range("A2:A" & lngLastR).Copy
            .Range("H2").PasteSpecial xlPasteValues
            .Range("E2:E" & .Range("E" & .Rows.Count).End(xlUp).Row).Copy
            .Range("H" & lngLastR + 1).PasteSpecial xlPasteValues
            Set rngAutoFil = .Range("H2:H" & .Range("H" & .Rows.Count).End(xlUp).Row)
            rngAutoFil.Select
            rngAutoFil.RemoveDuplicates Columns:=1, Header:=xlNo
        End With
    End Sub
    HTH
    -----------------------------
    Last edited by littleiitin; 07-01-2012 at 08:58 PM.

  3. #3
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    12
    Hi

    It would be appreciated if you could help me again.

    I now need to compare column A & E, and need to compare col E to col A and to extract data in Col A that does not appear in Col E

    Your assistance in this regar is most appreciated

  4. #4
    Moderator
    Join Date
    Jul 2012
    Posts
    156
    Rep Power
    12
    Code:
    IF(COUNTIF($E$2:$E$18,A2)=0,A2,"")

  5. #5
    Senior Member
    Join Date
    Apr 2012
    Posts
    193
    Rep Power
    12
    Thanks for the help, much appreciated

Similar Threads

  1. Move data from rows into columns for every unique value
    By mahmoud-lee in forum Excel Help
    Replies: 4
    Last Post: 06-13-2013, 03:02 AM
  2. Replies: 6
    Last Post: 06-01-2013, 03:24 PM
  3. Replies: 17
    Last Post: 05-22-2013, 11:58 PM
  4. Extracting Numeric Values From Alphanumeric Text
    By Safal Shrestha in forum Excel Help
    Replies: 3
    Last Post: 03-21-2013, 12:04 PM
  5. Replies: 2
    Last Post: 01-07-2012, 12:11 AM

Posting Permissions

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