Extract data excluding duplicate

JERRY89
4StarLounger
Posts: 516
Joined: 21 Feb 2016, 02:52

Extract data excluding duplicate

Post by 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.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78351
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Extract data excluding duplicate

Post by 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
Best wishes,
Hans

JERRY89
4StarLounger
Posts: 516
Joined: 21 Feb 2016, 02:52

Re: Extract data excluding duplicate

Post by 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.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78351
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Extract data excluding duplicate

Post by HansV »

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

S2476.png
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

JERRY89
4StarLounger
Posts: 516
Joined: 21 Feb 2016, 02:52

Re: Extract data excluding duplicate

Post by 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. :scratch:

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.
You do not have the required permissions to view the files attached to this post.