Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-01-2018, 03:37 PM
Dave T Dave T is offline Comparing two lists and extracting values missing from both lists Windows 7 64bit Comparing two lists and extracting values missing from both lists Office 2013
Advanced Beginner
Comparing two lists and extracting values missing from both lists
 
Join Date: Nov 2014
Location: Australia
Posts: 66
Dave T is on a distinguished road
Default Comparing two lists and extracting values missing from both lists

Hello All,

I had originally posted this question on another forum (I have since deleted the post as I suspect it was not the appropriate forum).

I have been using the two macros I found on the stackoverflow site (source link within attached code).

Using an InputBox to select the two columns to compare is exactly what I am after and works very well. Currently the results are hard coded to return the results to cell F5, but I would like to be able to use another InputBox to select the output cell.



Code:
Sub ExtractUniqueItems()
  'https://stackoverflow.com/questions/49115014/comparing-two-lists-in-excel-and-extracting-values-missing-from-2nd-list-canno
  
  Dim v1, v2, v3(), i As Long, j As Long
  
  On Error GoTo InputCancel
  
  v1 = Application.InputBox("First list", "VALUES TO COMPARE", Type:=8)
  v2 = Application.InputBox("Second list", "VALUES TO BE COMPARED WITH", Type:=8)
  
  ReDim v3(1 To UBound(v1, 1))
  
  For i = LBound(v1) To UBound(v1)
    If IsError(Application.Match(v1(i, 1), v2, 0)) Then
      j = j + 1
      v3(j) = v1(i, 1)
    End If
  Next i
  
  Range("F5").Resize(j) = Application.Transpose(v3)
  
InputCancel:
  
End Sub
How can the currently hard coded output cell of F5 be converted to an InputBox user selected cell.

Regards, Dave T
Reply With Quote
  #2  
Old 11-01-2018, 03:59 PM
jeffreybrown jeffreybrown is offline Comparing two lists and extracting values missing from both lists Windows Vista Comparing two lists and extracting values missing from both lists Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

Hi Dave,

Maybe...

Code:
Sub ExtractUniqueItems()
  'https://stackoverflow.com/questions/49115014/comparing-two-lists-in-excel-and-extracting-values-missing-from-2nd-list-canno
  
    Dim v1, v2, v3(), i As Long, j As Long
    Dim iCell As Range
    Dim r     As Range
    On Error Resume Next
    Set r = Application.InputBox("Click on a cell for the output", Type:=8)
    On Error GoTo 0
    If r Is Nothing Then Exit Sub
  
    On Error GoTo InputCancel
    
    v1 = Application.InputBox("First list", "VALUES TO COMPARE", Type:=8)
    v2 = Application.InputBox("Second list", "VALUES TO BE COMPARED WITH", Type:=8)
    
    ReDim v3(1 To UBound(v1, 1))
    
    For i = LBound(v1) To UBound(v1)
      If IsError(Application.Match(v1(i, 1), v2, 0)) Then
        j = j + 1
        v3(j) = v1(i, 1)
      End If
    Next i
    
    Range(r.Address).Resize(j) = Application.Transpose(v3)
    
InputCancel:
  
End Sub
Reply With Quote
  #3  
Old 11-01-2018, 05:21 PM
Dave T Dave T is offline Comparing two lists and extracting values missing from both lists Windows 7 64bit Comparing two lists and extracting values missing from both lists Office 2013
Advanced Beginner
Comparing two lists and extracting values missing from both lists
 
Join Date: Nov 2014
Location: Australia
Posts: 66
Dave T is on a distinguished road
Default

Hello Jeffrey,

I really appreciate your quick reply.

For some reason it is not working, but it is definitely heading in the right direction.
I now have an InputBox to select the cell for the output but there is no output being pasted.

Just out of curiosity why have you used Dim iCell as Range when it is not used within the rest of the code?

Regards,
David
Reply With Quote
  #4  
Old 11-01-2018, 05:28 PM
jeffreybrown jeffreybrown is offline Comparing two lists and extracting values missing from both lists Windows Vista Comparing two lists and extracting values missing from both lists Office 2007
Expert
 
Join Date: Apr 2016
Posts: 673
jeffreybrown has a spectacular aura aboutjeffreybrown has a spectacular aura about
Default

Dim iCell as Range can be removed. I had it there as I was testing.

If it's not working for you, then a small sample workbook would be helpful.

I tested this on a small sample before posting and it worked for me, but not sure how you have it setup to work for you.
Reply With Quote
  #5  
Old 11-01-2018, 11:51 PM
Dave T Dave T is offline Comparing two lists and extracting values missing from both lists Windows 7 64bit Comparing two lists and extracting values missing from both lists Office 2013
Advanced Beginner
Comparing two lists and extracting values missing from both lists
 
Join Date: Nov 2014
Location: Australia
Posts: 66
Dave T is on a distinguished road
Default

My apologies Jeffrey,

Yes it does work.

For some reason the formatting or something changed in my data columns.
I found an array formula to check the output of the macro and for some reason even the array formula would not work. I found various online comments about whether the column was formatted as Text or General.
It was only after I copied the ranges from the original workbook and pasted them back that everything worked.

Thanks for your help.
Regards, Dave
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Extracting data from multi dropdown lists Idkevin Excel 1 07-28-2018 02:21 AM
Creating global contact lists (distribution lists) Outlook 2011 Mac pengyou Outlook 0 09-10-2016 02:18 PM
Comparing two lists and extracting values missing from both lists From .doc file lists to .html file lists xuki Word VBA 1 02-23-2016 01:40 PM
Comparing two lists and extracting values missing from both lists I want to create a lists within lists (nested merges) AndyS Mail Merge 10 12-11-2015 03:05 PM
Comparing two lists and extracting values missing from both lists comparing values from a range struct Excel 1 04-01-2011 07:17 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:18 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft