Tuesday, 26 March 2013

How to Combine or Merge or Consolidate Data From Multiple Columns Into a Dynamic Column and Remove Duplicates

Excel VBA Macro To Combine or Merge or Consolidate Data From Multiple Columns into a Dynamic Column and Remove Duplicates
This is a very user friendly macro that Search for a Dynamic Column and Consolidate the data from multiple columns into a Dynamic Column and shows the Unique data in the Next Column.


Sub Consol_Get_Unique()
Dim I As Integer
Dim J As Integer

Dim TC As Long

Dim Col_Search As String

Dim TargetColumn As Range

Col_Search = "Consol_Data" 'Dynamic Column Name In Which We Consolidate Data


Set TargetColumn = ActiveSheet.Rows(1).Find(What:=Col_Search, LookIn:=xlValues, _

    LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

If Not TargetColumn Is Nothing Then

        MsgBox "Target Column Found At  " & TargetColumn.Address & _
        " and the Targe Column Number is " & TargetColumn.Column
Else:
MsgBox "TargetColumn Not Found"
End If

TC = TargetColumn.Column

Uniq_Data = TC + 1 'Unique Data Column Is Next To Consol Data Column

Z = 1 'Counting Variable

For I = 1 To 4 'Columns having data
For J = 2 To 100 'Rows having data
If Cells(J, I) = "" Then Exit For
Z = Z + 1
Cells(Z, TC) = Cells(J, I) 'Dynamic Column In Which We Consoldate Data
Next J
Next I

ActiveSheet.Columns(TC).Select

Selection.Copy
ActiveSheet.Columns(Uniq_Data).Select
ActiveSheet.Paste
Range(Selection, Selection.End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlYes
ActiveSheet.Cells(1, Uniq_Data) = "Unique_Data"
Application.CutCopyMode = False

End 


Thanks.,
Tamatam

No comments:

Post a Comment

Follow Me by Email

ExcelKingdom-Popular Posts

ExcelKingdom-Random Posts