microsoft excell merge multiple sheets

hi i have 15 sheets and i want to merge them in one sheet , how can i do that. also i want to filter out all duplicate entries.
Note: all sheets have same no of columns but different no of rows.
i cannot use any third party tools.
Techie solutionVMware SpecialistAsked:
Who is Participating?
 
Techie solutionConnect With a Mentor VMware SpecialistAuthor Commented:
I used vlook up formulas to sort out all duplicates and used move and copy for copying all sheets in one location.
0
 
Saqib Husain, SyedEngineerCommented:
Can you upload an excel file with, say, two or three sheets and a few lines of data in each sheet showing the duplicates so that a working solution is provided?
0
 
Bharat BhushanSolution ManagerCommented:
There is only data or you have formulas?
0
 
Bharat BhushanConnect With a Mentor Solution ManagerCommented:
You can do this by these two options:
1. VBA
2. Consolidation

by VBA

Sub Combine()
    Dim n
    Sheets.Add bEFORE:=Sheets(1)
    For n = 2 To Sheets.Count
        Sheets(n).Cells.CurrentRegion.Copy
        Sheets(1).Cells(2 ^ 20, 1).End(xlUp).Offset(1, 0).PasteSpecial
    Next
End Sub

Open in new window


by Consolidation
Please check the following link: https://excelchamps.com/blog/consolidate-data-from-multiple-worksheets-in-a-single-worksheet/
0
 
Techie solutionVMware SpecialistAuthor Commented:
I used vlook up formulas to sort out all duplicates and used move and copy for copying all sheets in one location.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.