Combine two Column without Duplicate

[Hardeep kanwar]

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

 

[Ashish Mathur]

Hi,

Follow these steps:

1. Type a heading for the range on sheet1, say Numbers
2. Select the range of data (including the heading given in 1 above) on
sheet1 and assign it a name, say dummy1;
3. Select the range again and press Ctrl+L;
4. Type a heading for the range on sheet2, say Numbers (same heading as on
sheet1)
5. Select the range of data (including the heading given in 4 above) on
sheet2 and assign it a name, say dummy2;
6. Select the range (on sheet2) again and press Ctrl+L;
7. Select any blank cell;
8. Save the file on the desktop and name it try.xls
9. Go to Data > Import External Data > New Database query
10. Select Excel files
11. In the folder hierarchy on the right, select Desktop and click on the
try.xls on the left had panel
12. Click on OK
13. Click on dummy1 and then press the greater then symbol to get the
numbers column on the right hand side
14. Click on dummy2 and then press the greater then symbol to get the
numbers column on the right hand side
15. Click on OK
16. On the next message box, click on OK. The Microsoft Query windows will
open up
17. Click on the SQL button
18. Delete whatever you see in that box
19. Type the following in that box

Select * from dummy1 union select * from dummy2

20. Click on OK
21. This will combine the two columns (will display the repeated numbers
only once)
22. Go to File > Return data to MS office Excel
23. In the Properties box, select the cell where you want the output.
24. The unique list of numbers will appear as desired;

Now you may add or edit number in dummy1 and dummy2. All you have to do is
right click anywhere in the output and click on Refresh.

Hope this helps.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Hardeep kanwar]

Hi Ashish

Thanks for the Reply

I am Sorry to told you that i am Using Excel 2007, And i couldn't Find the
"Import External Data" In Data Menu.

Thanks

 

[Hardeep kanwar]

Hey Ashish

I try Solution After 12th Step I Stuck.

In 13th Step You Said"Click on dummy1 and then press the greater then symbol
to get the numbers column on the right hand side"

After Instruct the 12th Step i Got a New Window " Select Table"

It Show Two Table 1st is Dummy 1 and 2nd is Dummy2

Then i select Dummy1 and Press Ok

I got a New Window Called " Import Data"

1 Table
2 Pivot Table Report
3 PivotChart and Pivot table Report

And Also

Where do you want to Put the Data?

1 Existing Worksheet
2 New Worksheet

I Select Table and Existing Worksheet Cell =$H$1 and Press "OK"

Now i got the 1st Table i.e. Dummy1 in H1.

I haven't Seen any "Greater Then Symbol" to get the Numbers Column on the
Right hand side

And Also

The MICROSOFT QUERY WINDOWS WILL OPEN UP

 

[Jacob Skaria]

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

 

[Hardeep kanwar]

Thanks Jacob

Thanks for this Superb Macro Its Very Fast



Is it Possible with Formula Because I Only Know how to Use MACROS but Don't
Know how to Modifiy.

So, Thats why I am Looking for a Formula.

Thanks Again

 

[Max]

Is it Possible with Formula ..

Copy n paste over the data from Sheet2 below data in Sheet1 (make it 1 long
col) Then in Sheet1, with data running in A1 down:
In B1: =IF(A1="","",IF(COUNTIF(A$1:A1,A1)>1,"",A1-ROW()/10^10))
In C1:
=IF(ROW()>COUNT(B:B),"",INDEX(A:A,MATCH(SMALL(B:B,ROW()),B:B,0)))
Copy B1:C1 down to cover the max expected extent of data. Hide away/minimize
col B. Col C will return the required list of unique numbers, all neatly
packed at the top, and sorted in ascending order. Any good? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---

 

[QuickLearner]

Working for me after a bit of Debugging and removing deletion of column B
THANKS JACOB

Sub AutofilterTwoRanges()
Dim ws1 As Worksheet, ws2 As Worksheet, ws As Worksheet, lngRow As Long
Set ws1 = Sheet3 // check this
Set ws2 = Sheet12// check this
Set ws = Sheet18//check this

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).Sort Key1:=Range("A2"), Order1:=xlAscending, header:=xlYes
End Sub

QUESTION

I have different Headers in both sheets 3&12 in column A i.e CustomerID and
SupplierID How to change them as ONLY IDs when Macro runs? At the Moment it
return
CustomerID

Rachid

 

[Jacob Skaria]

Hi "QuickLearner"

Nice to hear that it helped you..Modified to generate the unique list in
Sheet18. Try and feedback.

Sub AutofilterTwoRanges()
Dim ws1 As Worksheet, ws2 As Worksheet, ws As Worksheet, lngRow As Long
Set ws1 = Sheets("Sheet3")
Set ws2 = Sheets("Sheet12")
Set ws = Sheets("Sheet18")

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.Range("A1") = "IDs"
ws.Columns(1).Sort Key1:=Range("A2"), Order1:=xlAscending, header:=xlYes
End Sub


If this post helps click Yes

 

[QuickLearner]

thanks Again
I have added the IDs line only as I am getting Error "91" for Set ws1
=Sheets("Sheet3") when change it to Set ws1.Sheet3 then it works for me.
"OfficeXp2002 SP3"
My final macro is

Sub AutofilterTwoRanges()
Dim ws1 As Worksheet, ws2 As Worksheet, ws As Worksheet, lngRow As Long
Set ws1 = Sheet3
Set ws2 = Sheet12
Set ws = Sheet18

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.Range("A1") = "IDs"
ws.Columns(1).Sort Key1:=Range("A2"), Order1:=xlAscending, header:=xlYes
End Sub

Rachid
PS: I do not know where to CLICK YES I am using Microsoft Communities News
Server via Windows Live Mail...

 

[Ashish Mathur]

Hi,

In Excel 2007, it is is Data > Get External Data > From Other Sources >
Microsoft Query

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Ashish Mathur]

What is your question - be clear

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[QuickLearner]

oops I think i spoken too soon

When added new Customers in sheet3 and run the macro again I get error "1004
missing or illegal field name" and this code below is highlighted
"
ws1.Columns(1).AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=ws.Range("A1"), Unique:=True
"
Now when I removed the new added customer from from sheet3 the error is
still here..

Any idea what I am doing wrong Jacob.

Thanks in advance