Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Dec 2007
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Greetings,

    I have list A and list B, some items in list A are the same as those in list B. How can I extract the ones that do not match?

    Thank you.

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [quote name='Wordnewby65' post='800396' date='28-Oct-2009 21:48']Greetings,

    I have list A and list B, some items in list A are the same as those in list B. How can I extract the ones that do not match?

    Thank you.[/quote]

    Do mean extract to a new location or just find out which ones do not have a match?

    You can use VLOOKUP with an IF to determine items in list A that are not in list B
    But it will not directly extract them to a new list with NO spaces, although
    you could then filter then down to Non Blanks


    Or do you want all Items that are NOT in Both?
    Andrew

  3. #3
    Star Lounger
    Join Date
    Dec 2007
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='AndrewKKWalker' post='800399' date='28-Oct-2009 23:27']Do mean extract to a new location or just find out which ones do not have a match?

    You can use VLOOKUP with an IF to determine items in list A that are not in list B
    But it will not directly extract them to a new list with NO spaces, although
    you could then filter then down to Non Blanks


    Or do you want all Items that are NOT in Both?[/quote]
    Yes, I do want all items that are not listed on both lists.

  4. #4
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [quote name='Wordnewby65' post='800400' date='28-Oct-2009 22:54']Yes, I do want all items that are not listed on both lists.[/quote]

    OK a few more questions.

    1. Are the lists the same size?
    2. Do you want then extracted to a new position
    3. Are you hoping to do this with a Formula Or Macro?
    Andrew

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [quote name='AndrewKKWalker' post='800404' date='28-Oct-2009 23:10']OK a few more questions.

    1. Are the lists the same size?
    2. Do you want then extracted to a new position
    3. Are you hoping to do this with a Formula Or Macro?[/quote]


    If both lists are the same size then you can do it with a couple of Array Formulas

    If Not you probably need a Macro

    The one below does the job I think

    Code:
    Sub BuildList()
    
    Dim rngIn As Range, strAddress As String
    Dim rng1 As Range, rng2 As Range, rng3 As Range, rngA As Range, rngB As Range
    Dim str1 As String, str2 As String, str3 As String
    Dim intC As Integer, fFound As Boolean
    
    Set rngIn = Selection
    strAddress = rngIn.Address
    intC = InStr(strAddress, ",")
    
    If intC = 0 Then
    	MsgBox "Need three Ranges"
    	Exit Sub
    End If
    
    str1 = Left(strAddress, intC - 1)
    strAddress = Mid(strAddress, intC + 1)
    
    intC = InStr(strAddress, ",")
    If intC = 0 Then
    	MsgBox "Need three Ranges"
    	Exit Sub
    End If
    
    str2 = Left(strAddress, intC - 1)
    strAddress = Mid(strAddress, intC + 1)
    
    intC = InStr(strAddress, ",")
    If intC <> 0 Then
    	MsgBox "Need three Ranges Only"
    	Exit Sub
    End If
    
    str3 = strAddress
    
    Set rng1 = Range(str1)
    Set rng2 = Range(str2)
    Set rng3 = Range(str3)
    
    intC = 0
    
    
    'First List 1 with 2
    For Each rngA In rng1
    	fFound = False
    	For Each rngB In rng2
    		If rngA = rngB Then
    			fFound = True
    			Exit For
    		End If
    	Next
    	If fFound = False Then
    		'Write it
    		rng3.Offset(intC, 0) = rngA
    		intC = intC + 1
    	End If
    Next
    'Then List 2 with 1
    For Each rngA In rng2
    	fFound = False
    	For Each rngB In rng1
    		If rngA = rngB Then
    			fFound = True
    			Exit For
    		End If
    	Next
    	If fFound = False Then
    		'Write it
    		rng3.Offset(intC, 0) = rngA
    		intC = intC + 1
    	End If
    Next
    
    
    End Sub
    Before this can be run, you must select the ranges to be processed.

    To do this

    Select Range 1
    Hold CTRL and select range 2
    Hold CTRL and select the First Cell For the Output.

    Then Run the macro

    It will generate a new list starting in the last selected

    Example Attached

    I am sure there is a better way to do this.
    Attached Files Attached Files
    Andrew

  6. #6
    Star Lounger
    Join Date
    Dec 2007
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='AndrewKKWalker' post='800404' date='29-Oct-2009 00:10']OK a few more questions.

    1. Are the lists the same size? No
    2. Do you want then extracted to a new position? yes
    3. Are you hoping to do this with a Formula Or Macro? formula since I don't know Macro.[/quote]

    Please see my attached file. Thankyou.
    Attached Files Attached Files

  7. #7
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [quote name='Wordnewby65' post='800424' date='28-Oct-2009 23:59']Please see my attached file. Thankyou.[/quote]

    I am not sure it can be done in a formula.
    See my last post

    If it can I'd be interested to see the solution as well.
    Andrew

  8. #8
    Star Lounger
    Join Date
    Dec 2007
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='AndrewKKWalker' post='800429' date='29-Oct-2009 01:17']I am not sure it can be done in a formula.
    See my last post

    If it can I'd be interested to see the solution as well.[/quote]

    Thank you for all your time and effort.

  9. #9
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='Wordnewby65' post='800424' date='28-Oct-2009 19:59']Please see my attached file. Thankyou.[/quote]Disregard this post. See the response from Bosco.
    [s]If the order of List A is important further work will be necessary, otherwise sorting A4:F10 on column F (descending) will do the trick as shown in the attached. [/s]
    Attached Files Attached Files
    Regards
    Don

  10. #10
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='Wordnewby65' post='800424' date='28-Oct-2009 19:59']Please see my attached file. Thankyou.[/quote]Disregard this post. See the response from Bosco.
    [s]If sorting is not an option, the attached file provides a two column solution. [/s]
    Attached Files Attached Files
    Regards
    Don

  11. #11
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='Wordnewby65' post='800424' date='28-Oct-2009 19:59']Please see my attached file. Thankyou.[/quote]Disregard this post. See the response from Bosco.
    [s]To maintain the same order as found in List A the formula in Column F is a little longer (see the attached). [/s]
    Attached Files Attached Files
    Regards
    Don

  12. #12
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='Wordnewby65' post='800396' date='28-Oct-2009 21:48']I have list A and list B, some items in list A are the same as those in list B. How can I extract the ones that do not match?[/quote]

    Two (2) ways :

    1] With helper column, Column E

    1.1) Way A

    1.1.1] Cell E4, enter the formula and copy down :

    =IF(COUNTIF($D$4:$D$10,B4),0,MAX(E$3:E3)+1)

    1.1.2] Cell F4, enter the formula and copy down :

    =IF(ROW(1:1)>MAX(E$4:E$10),"",INDEX(B$4:B$10,MATCH (ROW(1:1),E$4:E$10,0)))

    Or……….

    1.2) Way B

    1.2.1] Cell E4, enter the formula and copy down :

    =IF(COUNTIF($D$4:$D$10,B4),"",MAX(E$3:E3)+1)

    1.2.2] Cell F4, enter the formula and copy down :

    =IF(ROW(1:1)>MAX(E$4:E$10),"",LOOKUP(ROW(1:1),E$4: E$10,B$4:B$10))

    2] Without helper column

    2.1) Way A

    2.1.1] Cell F4, enter the array formula and copy down :

    {=INDEX(B:B,SMALL(IF(ISNA(MATCH(B$4:B$100,D$4$100,)),ROW(B$4:B$100),10000),ROW(1:1)))&""}

    Or……….

    2.2) Way B

    2.2.1] Cell F4, enter the array formula and copy down :

    {=INDEX(B:B,SMALL(IF(COUNTIF(D$4$100,B$4:B$100),10000,ROW(B$4:B$100)),ROW(1:1)))&" "}

    Regards
    Bosco

Posting Permissions

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