generate list from 2 separate sheet

[Yvan L]

Hi all wondering if someone could help me out. What im trying to do seem simple but i was unable to figure it out with either a formula or a macro any help would be greatly appreciated
here's my problem
I would like to have value from Sheet(1) column C and value from Sheet(2) Column E generate a list on Sheet(3) A1 to be used for a dropdown list.

example
result wanted
|Sheet 1 Col "C"| |Sheet (2) Col "E"| |Sheet (3) Col "A"|
| | | | | |
| Jack | | Daniel | | Jack |
| Mickey | | Mouse | | Mickey |
| | | | | Daniel |
| | | | | Mouse |

Or if there's a better way maybe with a formula in the Data validation?

Thanks

 

[Yvan L]

That didn't work here's what it's supposed to look like.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Sheet (1) Col "C"[/TD]
[TD]Sheet (2) Col "E"[/TD]
[TD]Sheet (3) Col "A"[/TD]
[/TR]
[TR]
[TD]Jack[/TD]
[TD]Daniel[/TD]
[TD]Jack[/TD]
[/TR]
[TR]
[TD]Mickey[/TD]
[TD]Mouse[/TD]
[TD]Mickey[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Daniel[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Mouse[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Hi & welcome to the board.
How about

Sub CopyCompare()
   Dim Cl As Range
   Dim Ws1 As Worksheet
   Dim Ws2 As Worksheet

Application.ScreenUpdating = False
   Set Ws1 = Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
   Set Ws2 = Sheets("[COLOR=#ff0000]Sheet2[/COLOR]")
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws1.Range("C2", Ws1.Range("C" & Rows.Count).End(xlUp))
         If Not .Exists(Cl.Value) Then .Add Cl.Value, Nothing
      Next Cl
      For Each Cl In Ws2.Range("E2", Ws2.Range("E" & Rows.Count).End(xlUp))
         If Not .Exists(Cl.Value) Then .Add Cl.Value, Nothing
      Next Cl
   Sheets("[COLOR=#ff0000]Sheet3[/COLOR]").Range("A1").Resize(.Count).Value = Application.Transpose(.keys)
   End With
End Sub

Change sheet names to suit

 

[Yvan L]

Thank you so much Fluff works like a charm

 

[Yvan L]

One more question if I may.
Is there a way I could get the code to also make it with unique value and sorted ascending

 

[Fluff]

How about

Sub CopyCompare()
   Dim Cl As Range
   Dim Ws1 As Worksheet
   Dim Ws2 As Worksheet
   Dim Lst As Object, Ky As Variant

Application.ScreenUpdating = False
   Set Lst = CreateObject("System.Collections.ArrayList")
   Set Ws1 = Sheets("pcode")
   Set Ws2 = Sheets("Sheet2")
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Ws1.Range("C2", Ws1.Range("C" & Rows.Count).End(xlUp))
         If Not .Exists(Cl.Value) Then .Add Cl.Value, Nothing
      Next Cl
      For Each Cl In Ws2.Range("E2", Ws2.Range("E" & Rows.Count).End(xlUp))
         If Not .Exists(Cl.Value) Then .Add Cl.Value, Nothing
      Next Cl
      For Each Ky In .keys
         Lst.Add Ky
      Next Ky
      Lst.Sort
   Sheets("Sheet3").Range("A1").Resize(.Count).Value = Application.Transpose(Lst.toarray)
   End With
End Sub

 

[Yvan L]

Sorry for the delay I finally got a chance to try and its perfect

Thanks so much again

 

[Fluff]

Glad to help & thanks for the feedback