Is it possible to create a macro which generates a new list based on a criteria list?

[NewbieNoob]

https://imgur.com/5GlXgm2

Assuming I have my list of cars in column A. I choose a certain type of cars in column C, would it then be possible to generate a list of the remaining cars in column E, which I did not choose?

I found something like this: http://www.excelfunctions.net/vba-filter-function.html I think Example 2 is what I exactly want, however I'm not sure how I assign this macro to a button to make it generate the list?

 

[NewbieNoob]

 

[Fluff]

Hi & welcome to the board.
How about

Sub ListOthers()

   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      .comparemode = vbTextCompare
      For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then .Add Cl.Value, Nothing
      Next Cl
      For Each Cl In Range("C1", Range("C" & Rows.Count).End(xlUp))
         If .exists(Cl.Value) Then .Remove (Cl.Value)
      Next Cl
      Range("E1").Resize(.Count).Value = Application.Transpose(.keys)
   End With
End Sub

 

[NewbieNoob]

Hi & welcome to the board.
How about

Sub ListOthers()

   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      .comparemode = vbTextCompare
      For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then .Add Cl.Value, Nothing
      Next Cl
      For Each Cl In Range("C1", Range("C" & Rows.Count).End(xlUp))
         If .exists(Cl.Value) Then .Remove (Cl.Value)
      Next Cl
      Range("E1").Resize(.Count).Value = Application.Transpose(.keys)
   End With
End Sub

Thank you! But how do I activate the macro to make it generate the list? Should I assign it to some kind of button where when I click it, it generates the list in column E?

 

[NewbieNoob]

Hi & welcome to the board.
How about

Sub ListOthers()

   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      .comparemode = vbTextCompare
      For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then .Add Cl.Value, Nothing
      Next Cl
      For Each Cl In Range("C1", Range("C" & Rows.Count).End(xlUp))
         If .exists(Cl.Value) Then .Remove (Cl.Value)
      Next Cl
      Range("E1").Resize(.Count).Value = Application.Transpose(.keys)
   End With
End Sub

Wait, I think I got it! I created an ActiveX button and assigned it the following macro and it seems to generate the list now when I click the button!

Private Sub CommandButton1_Click()
Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      .comparemode = vbTextCompare
      For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then .Add Cl.Value, Nothing
      Next Cl
      For Each Cl In Range("C1", Range("C" & Rows.Count).End(xlUp))
         If .exists(Cl.Value) Then .Remove (Cl.Value)
      Next Cl
      Range("E1").Resize(.Count).Value = Application.Transpose(.keys)
   End With
End Sub

Thank you very much for the help. I will now study the code more thoroughly to understand each part.

 

[Fluff]

Glad to help & thanks for the feedback

For help with dictionaries have a look here
https://excelmacromastery.com/vba-dictionary/

 

[Peter_SSs]

If your data has headings, as most data does, then another option either manually or macro might be to use Advanced Filter. For example, assuming columns D:E are initially empty.

Sub ExcludedList()
  Application.ScreenUpdating = False
  Range("D2").Formula = "=COUNTIF(C$2:C$" & Range("C" & Rows.Count).End(xlUp).Row & ",A2)=0"
  Range("A1", Range("A" & Rows.Count).End(xlUp)).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("D1:D2"), CopyToRange:=Range("E1"), Unique:=True
  Range("D2").ClearContents
  Application.ScreenUpdating = True
End Sub

Book1
	A	B	C	D	E
1	List		Exclude		List
2	Audi		Audi		Mercedes
3	Audi		Ford		Fiat
4	Mercedes
5	Fiat
6	Audi
7	Mercedes
8	Ford
9
New List

 

[NewbieNoob]

Glad to help & thanks for the feedback

For help with dictionaries have a look here
https://excelmacromastery.com/vba-dictionary/

Thanks! I have a small question regarding the code that I'm not quite sure about. It's regarding this line:

[COLOR=#333333]         If Not .exists(Cl.Value) Then .Add Cl.Value, Nothing[/COLOR][CODE][COLOR=#333333]

[/COLOR]I'm trying to figure out why the Nothing argument is needed at the end? In the line with .Remove, we don't seem to have a similar syntax. I have also noticed that I can literally write anything in place of Nothing in that code and still get the same result.

 

[shg]

Unlike a Collection, which only requires only an Item (a Key is optional), a Dictionary requires both an Item and a Key. If you don't need the Item, it can be anything (and Nothing is as good as anything), but you'll get a compile error if you don't include one.

 

[NewbieNoob]

Unlike a Collection, which only requires only an Item (a Key is optional), a Dictionary requires both an Item and a Key. If you don't need the Item, it can be anything (and Nothing is as good as anything), but you'll get a compile error if you don't include one.

But why do I not get a compile error in the other code when I don't define an item?

[COLOR=#333333]         If .exists(Cl.Value) Then .Remove (Cl.Value)

[/COLOR]