Extract data excluding duplicate

[JERRY89]

Dear All,

I having difficulty to copy the main part from Raw Data Sheet to Detail sheet based on Spart Part & Code as my primary key, is there any macro to automate this process with the function auto copy Main Part to Detail sheet & insert row if found more than 1 Main Part.

Usually i having more than 1K of Main Part to be manually copy & insert row , do hope anyone can help me on this.

[HansV]

Code: Select all

Sub ExtractData()
    Dim wshSource As Worksheet
    Dim wshTarget As Worksheet
    Dim lngSource As Long
    Dim arrSource As Variant
    Dim lngTarget As Long
    Dim MainPart, SparePart, Code
    Application.ScreenUpdating = False
    Set wshSource = Worksheets("Raw Data ")
    arrSource = wshSource.UsedRange.Value
    Set wshTarget = Worksheets.Add(After:=wshSource)
    wshTarget.Range("F1:G1").Value = Array("Spare Part", "Code")
    wshTarget.Range("V1").Value = "Main Part"
    lngTarget = 1
    For lngSource = LBound(arrSource, 1) + 1 To UBound(arrSource, 1)
        If Not IsEmpty(arrSource(lngSource, 1)) Then
            MainPart = arrSource(lngSource, 1)
        ElseIf Not IsEmpty(arrSource(lngSource, 7)) Then
            lngTarget = lngTarget + 1
            wshTarget.Range("F" & lngTarget).Value = arrSource(lngSource, 7)
            wshTarget.Range("G" & lngTarget).Value = arrSource(lngSource, 9)
            wshTarget.Range("V" & lngTarget).Value = MainPart
        End If
    Next lngSource
    With wshTarget.UsedRange
        .Sort Key1:=.Cells(1, 1), Key2:=.Cells(1, 2), Key3:=.Cells(1, 17), Header:=xlYes
        .RemoveDuplicates Columns:=Array(1, 2, 17)
        .EntireColumn.AutoFit
    End With
    Application.ScreenUpdating = True
End Sub

[JERRY89]

Hi Hans,

I have tried run the code but it only open another sheet n copy spare part & code but what i want is like the sample in my Detail Sheet Column V2 till V3, if i perform manually i need to copy & insert row based on Raw Data excluding duplicate.

[HansV]

This is the result of the macro. It extracts everything for you.

S2476.png

[JERRY89]

Hi Hans,

Can this function integrate to the Detail Sheet, for your info the Detail sheet is the master template provided by my HQ, as per the image below highlighted in Yellow contain all the sensitive Data which i have removed.

If the Macro can based on Detail Sheet Colum F & G to find the main part in raw data sheet & auto insert row if more than 1 part will be the best outcome.