Merge multiple columns and remove duplicates automatically

[Sophia23]

Hi there,

The workbook that i currently have consist of 12 sheets being each month of the year and one summary sheet for the whole year. In the summary sheet i have a fabric column which consist of all the months fabric column data which i currently is copying and using the remove duplicate function.
Is there a way to merge the data and remove the duplicates automatically? because as of now every time i key in a new fabric in one of the months, i need to manually copy paste the whole column and remove duplicates in the summary sheet

 

[tonyyy]

Sophia23,

Welcome to the Board.

If you're comfortable with a vba approach you might consider the following...

Sub FabricDictionary_1026466()
Application.ScreenUpdating = False
Dim ws As Worksheet, ws1 As Worksheet, r As Range, dict As Object

Set ws1 = Sheets("Summary")
Set dict = CreateObject("Scripting.Dictionary")
ws1.Range("F2:F" & ws1.Cells(Rows.Count, "F").End(xlUp).Row).ClearContents

For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> "Summary" Then
        For Each r In ws.Range("F2:F" & ws.Cells(Rows.Count, "F").End(xlUp).Row)
            If Not dict.exists(r.Value) Then dict.Add r.Value, 1
        Next r
    End If
Next ws
ws1.Range("F2").Resize(UBound(dict.keys) + 1, 1).Value = Application.Transpose(dict.keys)
Application.ScreenUpdating = True
End Sub

Assumptions/Notes
All worksheets have the same column headings/structure
The Fabric column is Column F (F for Fabric; pretty clever, huh?)

The code will:
clear the fabric contents from the Summary sheet
loop through each of the sheets
if it doesn't exist in the dictionary collection, add each fabric from Column F to the dictionary (this eliminates duplicates)
writes the dictionary to the Summary sheet

Cheers,

tonyyy

 

[Sophia23]

Hi Tonyyy,

Thanks for your help,

I have actually made a code in excel worksheet. but the unique values cant seem to automatically update it self in the summary worksheet. It only copies the values when i click on unique value cell in the months.
Do you know why it does not update automatically?

My code in the month worksheet:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Private Sub Worksheet_SelectionChange(ByVal target As Range)
If target.Column = 9 Then
fabric = ActiveCell.Value
Module4.ChkFabric (fabric)
End If

End Sub</code>I have used a module to copy to the summary worksheet
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Sub ChkFabric(ByRef fabric As String)
Dim Rng, TgtC, ResC As Range
Dim PrePlan As Worksheet

Set PrePlan = Worksheets("Pre Master Plan")

With PrePlan
Set ResC = .Range("A:A")
endrow = .Cells(PrePlan.Rows.Count, "A").End(xlUp).Row
End With
With ResC
Set Rng = .Find(what:=Trim(fabric), LookIn:=xlValues, lookat:=xlWhole,
searchorder:=xlByRows, searchdirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then


Else
PrePlan.Cells(endrow + 1, 1) = fabric
End If
End With


End Sub</code></code></pre>

 

[Fluff]

Try using

Private Sub Worksheet_Change(ByVal target As Range)


If target.Column = 9 Then
    Call ChkFabric(target.Value)
End If

End Sub

Instead of the selectionChange

 

[Macropod]

Cross-posted at: https://www.excelforum.com/excel-pr...-does-not-automatically-appear-on-column.html
Please read Mr Excel's policy on Cross-Posting in rule 13: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

 

[Sophia23]

I have tried this but it gives me the error of Object variable or with block variable not set

it Highlighted the code:

Set Rng =.Find(what:=Trim(fabric), LookIn:=xlValues, lookat:=xlWhole,
searchorder:=xlByRows, searchdirection:=xlNext, _
MatchCase:=False)