building data model

Dear all,

I am seeking for some support on how to load raw data into a new structured workbook. I've attached two sample files for your convenience (Commerce hub export is the input file and the data model template is the output file), and basically what I want to achieve is threefold.

1. Loading data: The first four columns of the input file go to the "Contract_Fact" worksheet of the output file, whereas the "Tech Manager" field goes to the "Dim_Tech_Manager" worksheet and the "Contract Party" goes to the "Dim_Contract_Party" worksheet in the output file.

2. Sorting data: in the two "Dim" worksheets of the output file, keep the unique records only and give each a sequential ID

3. Matching data: Do a v-lookup in the "Contract_Fact" worksheet, to indicate for each record, the IDs of its tech manager and contract party.

Could you please help me out on this? Thank you very much in advance!!!

Regards,
Tuan

Hi

This Macro meets the first part of your requirement

Sub Import_Macro()
Dim ThisWorkbook As String

ThisWorkbook = ActiveWorkbook.Name
ThisWorksheet = ActiveSheet.Name
If Right(ThisWorkbook, 6) = "export" Then Exit Sub
LR = Range("A65536").End(xlUp).Row

    Workbooks.Add
    ActiveWorkbook.SaveAs Filename:="C:\Users\Mehmet\Desktop\" & "data_model_template " & Day(Now) & "-" & Month(Now) & "-" & Year(Now) & ".xlsx", _
        FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

    Sheets("Sheet1").Name = "Contract_Fact"
    Sheets("Sheet2").Name = "Dim_Tech_Manager"
    Sheets("Sheet3").Name = "Dim_Contract_Party"
    
    Workbooks("data_model_template " & Day(Now) & "-" & Month(Now) & "-" & Year(Now) & ".xlsx").Sheets("Contract_Fact").Range("A1:D" & LR) = Workbooks(ThisWorkbook).Sheets(ThisWorksheet).Range("A1:D" & LR).Value
    Workbooks("data_model_template " & Day(Now) & "-" & Month(Now) & "-" & Year(Now) & ".xlsx").Sheets("Dim_Tech_Manager").Range("A1:A" & LR) = Workbooks(ThisWorkbook).Sheets(ThisWorksheet).Range("E1:E" & LR).Value
    Workbooks("data_model_template " & Day(Now) & "-" & Month(Now) & "-" & Year(Now) & ".xlsx").Sheets("Dim_Contract_Party").Range("A1:A" & LR) = Workbooks(ThisWorkbook).Sheets(ThisWorksheet).Range("F1:F" & LR).Value

    Sheets("Dim_Tech_Manager").Range("A1:A" & LR).RemoveDuplicates Columns:=1, Header:=xlNo
    Sheets("Dim_Contract_Party").Range("A1:A" & LR).RemoveDuplicates Columns:=1, Header:=xlNo

End Sub