#1
|
|||
|
|||
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 Regards, Dave T |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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 |
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 |
From .doc file lists to .html file lists | xuki | Word VBA | 1 | 02-23-2016 01:40 PM |
I want to create a lists within lists (nested merges) | AndyS | Mail Merge | 10 | 12-11-2015 03:05 PM |
comparing values from a range | struct | Excel | 1 | 04-01-2011 07:17 PM |