CODEWITHSUNDEEP
excelexcel-formulavba
JP29
JP29

Reputation: 625

Unique Values from multiple spreadsheets

I have 5 different spreadsheets storing telephone numbers and names. They all look like this:

Name     Telephone
--------------
name1    551
name2    552
name1    551
name5    555

(There are thousands of records in each spreadsheet)

I would like a sixth spreadsheet to bring all of these together, showing no duplicated values (so 1* name 1) and 1*name 5. However duplicates can be in several spreadsheets, for example name 1 could occur in all 5 spreadsheets.

Any suggestions??

Upvotes: 1

Views: 2499

Answers (1)

Kumarapush
Kumarapush

Reputation: 21

Here is the VBA method. Code is not that difficult, in case it is ok to use VBA for you. Insert a new sheet before the first sheet. Press Alt + F11 and copy paste the below code.

and Press F5 to merge all contacts in 5 sheets to sheet1 and remove duplicates. Imp Note: Take a backup of your excel workbook before executing this code.

Sub Combine_and_Remove_Duplicates()
Output_Row = 2

For Sheet_Index = 2 To 6
    Input_Row = 2
    While Sheets(Sheet_Index).Cells(Input_Row, 1) <> ""
        Sheets(1).Cells(Output_Row, 1) = Sheets(Sheet_Index).Cells(Input_Row, 1)
        Sheets(1).Cells(Output_Row, 2) = Sheets(Sheet_Index).Cells(Input_Row, 2)
        Output_Row = Output_Row + 1
        Input_Row = Input_Row + 1
    Wend
Next Sheet_Index

Sheets(1).Columns("A:B").RemoveDuplicates Columns:=Array(1, 2), Header _
    :=xlYes

End Sub

Upvotes: 1

Related Questions