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

Excel_Newbie_Guy

New Member
Joined
Jan 15, 2018
Messages
14
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.

 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Try this for results in "O22"
Code:
[COLOR="Navy"]Sub[/COLOR] MG15Jan53
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nStr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("O5"), Range("O5").End(xlDown))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng: .Item(Dn.Value) = Empty: [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("I5"), Range("I" & Rows.Count).End(xlUp))
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] Not .exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            nStr = nStr & IIf(nStr = "", Dn.Value, ", " & Dn.Value)
        [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] Dn
            Range("O22").Value = nStr
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 

Excel_Newbie_Guy

New Member
Joined
Jan 15, 2018
Messages
14
Sorry, new to forums...

I thought that this might be a better example to show all the variables that I am dealing with:



Thanks for your help.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,317
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi & welcome to the board
Beaten 2 it
 
Last edited:

Excel_Newbie_Guy

New Member
Joined
Jan 15, 2018
Messages
14
Try this for results in "O22"
Code:
[COLOR=navy]Sub[/COLOR] MG15Jan53
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] nStr [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range(Range("O5"), Range("O5").End(xlDown))
[COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng: .Item(Dn.Value) = Empty: [COLOR=navy]Next[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range(Range("I5"), Range("I" & Rows.Count).End(xlUp))
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
        [COLOR=navy]If[/COLOR] Not .exists(Dn.Value) [COLOR=navy]Then[/COLOR]
            nStr = nStr & IIf(nStr = "", Dn.Value, ", " & Dn.Value)
        [COLOR=navy]End[/COLOR] If
        [COLOR=navy]Next[/COLOR] Dn
            Range("O22").Value = nStr
[COLOR=navy]End[/COLOR] [COLOR=navy]With[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
Regards Mick

Thanks Mick, sorry I added my additional reply before I noticed that you had replied.

I am not that strong with VBA, is there a formula that I need to add in "O22" to get the UDF to work?


Thank you
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841

ADVERTISEMENT

If you want a UDF try this:-
To Save and Run UDF:-
Copy Function 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.

In sheet, Add the "function" in a cell as :- =Notin(O5:O18,I5:I26)
NB:- Once you've add the first bracket you can select the range from the sheet

Then click "Enter"
The cell should now show the result

Code:
Function NotIn(rng1, rng2) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nStr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("O5"), Range("O5").End(xlDown))
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] rng1: .Item(Dn.Value) = Empty: [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] rng2
        [COLOR="Navy"]If[/COLOR] Not .exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            nStr = nStr & IIf(nStr = "", Dn.Value, ", " & Dn.Value)
        [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] Dn
    NotIn = nStr
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] Function
Regards Mick
 

Excel_Newbie_Guy

New Member
Joined
Jan 15, 2018
Messages
14
If you want a UDF try this:-
To Save and Run UDF:-
Copy Function 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.

In sheet, Add the "function" in a cell as :- =Notin(O5:O18,I5:I26)
NB:- Once you've add the first bracket you can select the range from the sheet

Then click "Enter"
The cell should now show the result

Code:
Function NotIn(rng1, rng2) [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] nStr [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range(Range("O5"), Range("O5").End(xlDown))
[COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] rng1: .Item(Dn.Value) = Empty: [COLOR=navy]Next[/COLOR]
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] rng2
        [COLOR=navy]If[/COLOR] Not .exists(Dn.Value) [COLOR=navy]Then[/COLOR]
            nStr = nStr & IIf(nStr = "", Dn.Value, ", " & Dn.Value)
        [COLOR=navy]End[/COLOR] If
        [COLOR=navy]Next[/COLOR] Dn
    NotIn = nStr
[COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] Function
Regards Mick

Thank you very much again Mick.

How does the first solution work that you provided me? Where do I place that code and get the results in cell "O22"?


Thank you
 

Excel_Newbie_Guy

New Member
Joined
Jan 15, 2018
Messages
14
If you want a UDF try this:-
To Save and Run UDF:-
Copy Function 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.

In sheet, Add the "function" in a cell as :- =Notin(O5:O18,I5:I26)
NB:- Once you've add the first bracket you can select the range from the sheet

Then click "Enter"
The cell should now show the result

Code:
Function NotIn(rng1, rng2) [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range, n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] nStr [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]
[COLOR=navy]Set[/COLOR] Rng = Range(Range("O5"), Range("O5").End(xlDown))
[COLOR=navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] rng1: .Item(Dn.Value) = Empty: [COLOR=navy]Next[/COLOR]
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] rng2
        [COLOR=navy]If[/COLOR] Not .exists(Dn.Value) [COLOR=navy]Then[/COLOR]
            nStr = nStr & IIf(nStr = "", Dn.Value, ", " & Dn.Value)
        [COLOR=navy]End[/COLOR] If
        [COLOR=navy]Next[/COLOR] Dn
    NotIn = nStr
[COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] Function
Regards Mick

Quick question, is there a way to modify the code where it does not return duplicate data from Column I?


Thanks again.
 

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,385
Messages
5,601,328
Members
414,444
Latest member
lionking15

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top