Return multiple values in one cell that are not found in a reference list

[Excel_Newbie_Guy]

Hi,

I have searched multiple times and could not find a solution to returning multiple values in a single cell that are not a match, meaning, I would like to return multiple values reported from a single column range that are not a match to a reference list/table.

I have pasted in the cells I am trying to solve. Note that I did use a LOOKUP formula, but now know that it can only return one value.

The data is in Column I and the reference list/table is in Column O and the values that don't match are also in Column O.

Any help provide is greatly appreciated.

 

[Excel_Newbie_Guy]

Try this:-
To Save and Run Code:-
Copy code from Thread
In Your Data sheet , Click "Alt+F11",:- Vb Window appears.
From the VBWindow toolbar, Click "Insert" ,"Module":- New VBwindow appears .
Paste Code into this window.
Close Vbwindow.
On sheet Click "Developer tab", Click "Macro". Macro dialog box appears.
Select Macro (with same name) from List.
On the right of Dialog box Click "Run"
The Sheet should now be updated in "O22".
Regrds Mick

Thank you so much Mick for your help, yes, that works perfectly.

Is there a way to not show duplicate results? There could be hundreds to thousands of data returned and it would be nice not to show duplicate returns that do not match the reference list/table.


Thank you.

 

[Excel_Newbie_Guy]

Thank you so much Mick for your help, yes, that works perfectly.

Is there a way to not show duplicate results? There could be hundreds to thousands of data returned and it would be nice not to show duplicate returns that do not match the reference list/table.


Thank you.

And an additional question, how about not returning blank rows from Column I? there will also be rows of data in Column I that are blank.

The data is coming from a Pivot table and some of Column I may be blank.


Thank you

 

[Rick Rothstein]

Is there a way to not show duplicate results?

And an additional question, how about not returning blank rows from Column I?

Give this UDF a try...

[table="width: 500"]
[tr]
	[td]Function Missing(Descriptions As Range, CheckList As Range) As String
  Dim X As Long, Combined As String, Check As Variant
  Check = CheckList.Value
  Combined = Replace("|" & Join(Application.Transpose(Descriptions), "|") & "|", "||", "|")
  For X = 1 To UBound(Check)
    Combined = Replace(Combined, "|" & Check(X, 1) & "|", "|")
  Next
  Missing = Replace(Mid(Combined, 2, Len(Combined) - 2), "|", ", ")
End Function[/td]
[/tr]
[/table]

Note: The first argument is the range containing the descriptions and the second argument is the range containing the list to check; so for the example you posted, the formula you would use is this...

=Missing(I5:I26,O5:O19)

 

[Excel_Newbie_Guy]

Give this UDF a try...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function Missing(Descriptions As Range, CheckList As Range) As String
  Dim X As Long, Combined As String, Check As Variant
  Check = CheckList.Value
  Combined = Replace("|" & Join(Application.Transpose(Descriptions), "|") & "|", "||", "|")
  For X = 1 To UBound(Check)
    Combined = Replace(Combined, "|" & Check(X, 1) & "|", "|")
  Next
  Missing = Replace(Mid(Combined, 2, Len(Combined) - 2), "|", ", ")
End Function
[/TD]
[/TR]
</tbody>[/TABLE]

Note: The first argument is the range containing the descriptions and the second argument is the range containing the list to check; so for the example you posted, the formula you would use is this...

=Missing(I5:I26,O5:O19)

Thank you Rick, it sort of worked, please see the results of the code and formula when I used it on my sample worksheet:


It is still duplicating some of the data, showing one blank and also some data that is in the list.

Any idea why?


Thank you

 

[Excel_Newbie_Guy]

Give this UDF a try...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function Missing(Descriptions As Range, CheckList As Range) As String
  Dim X As Long, Combined As String, Check As Variant
  Check = CheckList.Value
  Combined = Replace("|" & Join(Application.Transpose(Descriptions), "|") & "|", "||", "|")
  For X = 1 To UBound(Check)
    Combined = Replace(Combined, "|" & Check(X, 1) & "|", "|")
  Next
  Missing = Replace(Mid(Combined, 2, Len(Combined) - 2), "|", ", ")
End Function
[/TD]
[/TR]
</tbody>[/TABLE]

Note: The first argument is the range containing the descriptions and the second argument is the range containing the list to check; so for the example you posted, the formula you would use is this...

=Missing(I5:I26,O5:O19)

Thank you very much Rick, that sort of worked. I have included a screen grab of the results of using the UDF.

As you can see there is duplicated data as well as a blank result returned, plus it also returns some data that is in the list/table.

Any ideas why?




Thank you.

 

[Rick Rothstein]

Thank you very much Rick, that sort of worked. I have included a screen grab of the results of using the UDF.

As you can see there is duplicated data as well as a blank result returned, plus it also returns some data that is in the list/table.

Yeah, I know what I did wrong, but before attempting to correct it, let me find out if there might be an easier bit of code... will all of the text your Description list always be single words without any spaces?

 

[Excel_Newbie_Guy]

Yeah, I know what I did wrong, but before attempting to correct it, let me find out if there might be an easier bit of code... will all of the text your Description list always be single words without any spaces?

Thanks Rick, yes, it will always be single words without any spaces.

 

[Excel_Newbie_Guy]

Yeah, I know what I did wrong, but before attempting to correct it, let me find out if there might be an easier bit of code... will all of the text your Description list always be single words without any spaces?

I was able to solve the issue with not reporting blanks by simply adding that criteria to the "List", but I still have the issue of reporting duplicates that I would like to resolve.


Thanks,
Guy

 

[Rick Rothstein]

I was able to solve the issue with not reporting blanks by simply adding that criteria to the "List", but I still have the issue of reporting duplicates that I would like to resolve.

Given that none of the cells will have spaces within their text, this UDF should handle both blank cells and duplicates...

[table="width: 500"]
[tr]
	[td]Function Missing(Descriptions As Range, CheckList As Range) As String
  Dim X As Long, Combined As String, Desc As Variant, Check As Variant
  Check = CheckList.Value
  Desc = Descriptions
  With CreateObject("Scripting.Dictionary")
    For X = 1 To UBound(Desc)
      .Item(Desc(X, 1)) = 1
    Next
    Combined = " " & Join(.Keys) & " "
  End With
  For X = 1 To UBound(Check)
    Combined = Replace(Combined, " " & Check(X, 1) & " ", Space(2))
  Next
  Missing = Replace(Application.Trim(Combined), " ", ", ")
End Function[/td]
[/tr]
[/table]

 

[Excel_Newbie_Guy]

Given that none of the cells will have spaces within their text, this UDF should handle both blank cells and duplicates...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Function Missing(Descriptions As Range, CheckList As Range) As String
  Dim X As Long, Combined As String, Desc As Variant, Check As Variant
  Check = CheckList.Value
  Desc = Descriptions
  With CreateObject("Scripting.Dictionary")
    For X = 1 To UBound(Desc)
      .Item(Desc(X, 1)) = 1
    Next
    Combined = " " & Join(.Keys) & " "
  End With
  For X = 1 To UBound(Check)
    Combined = Replace(Combined, " " & Check(X, 1) & " ", Space(2))
  Next
  Missing = Replace(Application.Trim(Combined), " ", ", ")
End Function
[/TD]
[/TR]
</tbody>[/TABLE]

Thank you very much Rick, the code and formula work perfectly, I really appreciate your help!


Thanks,

Guy