Hi Hardeep

Try the below macro which will insert a new sheet after sheet2 and generate
a sorted distinct list. You need to have headers assigned to both the lists
in sheet1 and sheet2. Incase you are new to macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()

Sub AutofilterTwoRanges()
Dim ws1 As Worksheet, ws2 As Worksheet, ws As Worksheet, lngRow As Long
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")

Set ws = Worksheets.Add(After:=ws2)
ws1.Columns(1).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ws.Range("A1"), Unique:=True
lngRow = ws.Cells(Rows.Count, "A").End(xlUp).Row + 1
ws2.Columns(1).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ws.Range("A" & lngRow), Unique:=True
ws.Rows(lngRow).Delete
ws.Columns(1).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ws.Range("B1"), Unique:=True
ws.Columns(1).Delete
ws.Columns(1).Sort Key1:=Range("A2"), Order1:=xlAscending, header:=xlYes
End Sub


If this post helps click Yes
---------------
Jacob Skaria


"Hardeep kanwar" wrote:

Hi Experts

I have data in Two Sheets,

For Example:
Sheet1

1
2
3
4
5
6
7
8
9
10
3
2
5
6


Sheet2

10
9
8
20
25
26
24
58
26
3
4
5
3
78
79
3
3
3


Expected Result Should be
1
2
3
4
5
6
7
8
9
10
20
25
26
24
58
78
79

Which Formula i use to get the Expected Result

Thanks in Advance

Hardeep Kanwar