Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combine two Column without Duplicate
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combine two Column without Duplicate
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combine two Column without Duplicate
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 "Ashish Mathur" wrote: 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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combine two Column without Duplicate
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 "Ashish Mathur" wrote: 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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combine two Column without Duplicate
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combine two Column without Duplicate
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 "Jacob Skaria" wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combine two Column without Duplicate
"Hardeep kanwar" wrote:
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 --- |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
[Solved] Combine two Column without Duplicate
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" wrote in message ... 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
[Solved] Combine two Column without Duplicate
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 --------------- Jacob Skaria "QuickLearner" wrote: 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" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
[Solved] Combine two Column without Duplicate
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... "Jacob Skaria" wrote in message ... 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 --------------- Jacob Skaria "QuickLearner" wrote: 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" wrote in message ... 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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combine two Column without Duplicate
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 "Hardeep kanwar" wrote in message ... 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 "Ashish Mathur" wrote: 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" wrote in message ... 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 |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Combine two Column without Duplicate
What is your question - be clear
-- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Hardeep kanwar" wrote in message ... 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 "Ashish Mathur" wrote: 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" wrote in message ... 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 |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
[error] Combine two Column without Duplicate
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 "QuickLearner" wrote in message ... 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... "Jacob Skaria" wrote in message ... 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 --------------- Jacob Skaria "QuickLearner" wrote: 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combine duplicate records in one row - In desperate need!!! | Excel Discussion (Misc queries) | |||
delete duplicate then combine text | Excel Discussion (Misc queries) | |||
How do I combine two Excel Workbooks and delete the duplicate colu | Excel Discussion (Misc queries) | |||
How do i combine duplicate column headings | Excel Worksheet Functions | |||
Can I compare 2 lists to combine duplicate entries in new list? | Excel Worksheet Functions |