• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Mapping the columns from 2 workbook and then merging the data

J

Jagdev Singh

Active Member
To match the columns from sheet1 of one workbook and sheet1 of another workbook. I have a these 2 workbooks and most of times I received columns not in proper sequence is the anyway to deal with it. Is it possible to map the columns first and then merge the data in new sheet.

Regards,

JD
 
Hi Marc

Thanks for looking into it. I have created 2 dummy sample data files and one final result output attached with the thread. I have amended few of the headings of data2 sheet. Is it possible to map such heading as well? I find such cases in my raw data.

We ideally placed data1 and data2 sheets in a common folder.

Please let me know if I am not crystal clear yet.
 

Attachments

  • Data1.xlsx
    8.8 KB · Views: 5
  • data2.xlsx
    8.9 KB · Views: 3
  • Result.xlsx
    9 KB · Views: 3

Very easy if columns names are equals and same columns number
between worksheets with an advanced filter with few codelines !
Columns order should not be an issue …

If not possible, you must create in result workbook Sheet2 for example
a titles conversion table but code could seem to be a gas factory !
​
 
Hi Jaggi ,

Please confirm the following :

1. Will there always be only 2 input files to be consolidated into 1 output file ?

2. Will the output file need to have all entries from both input files ? Or is any other kind of processing , such as removal of duplicates , to be done on the input data ? Will there be any erroneous data which will need to be excluded from the consolidation ?

3. What kind of changes / extensions to this basic starting point do you envisage in the future ?

4. Can the mapping of the columns between the 2 input workbooks be put inside the code ? You need to give this mapping.

Any other points you can think of which might have a bearing on the coding ?

Try to upload data files which have as much of the variations as possible.

Narayan
 
Working on my side with attached files,
paste this code into Result workbook Sheet1 module :​
Code: 
Sub Demo1()
            Dim Rg As Range
            FOLDER$ = ThisWorkbook.Path & "\Data\"
With Cells(1).CurrentRegion
  .Offset(1).Clear
  Application.ScreenUpdating = False
  TITL = Application.Index(.Rows(1).Resize(2).Value, 1)
  HDR$ = Join$(TITL)
End With
         F$ = Dir(FOLDER & "*.xlsx")
Do Until F = ""
    With Workbooks.Open(FOLDER & F).Worksheets(1).Cells(1).CurrentRegion.Rows
        If Join(Application.Index(.Item("1:2").Value, 1)) = HDR Then
            .Item("2:" & .Count).Copy Cells(Me.Rows.Count, 1).End(xlUp)(2)
        Else
            ReDim COL&(1 To .Columns.Count)

            For C& = 1 To UBound(TITL)
                Set Rg = .Item(1).Find(TITL(C), , xlValues, xlPart)
                 If Rg Is Nothing Then Exit For
                COL(Rg.Column) = C
            Next

            If Not Rg Is Nothing Then
                R& = Cells(Me.Rows.Count, 1).End(xlUp).Row + 1

                With .Item("2:" & .Count).Columns
                    For C = 1 To UBound(COL)
                        .Item(C).Copy Cells(R, COL(C))
                    Next
                End With
            End If
        End If

        .Parent.Parent.Close False
    End With
         F = Dir
Loop
    Set Rg = Nothing
End Sub
Do you like it ? So thanks to click on bottom right Like !
​
 
Hi Narayan

Will there always be only 2 input files to be consolidated into 1 output file?

JD - Nope there could be cases of more than 2 files to be consolidated as of now I encountered max count of 4 raw data files

2. Will the output file need to have all entries from both input files? Or is any other kind of processing, such as removal of duplicates, to be done on the input data? Will there be any erroneous data which will need to be excluded from the consolidation?

JD – Nope the output will be having fixed number of columns and the input files will have extra columns.

3. What kind of changes / extensions to this basic starting point do you envisage in the future?

JS – Say I have the list of fixed column heading in column A in the macro sheet, which needs to be considered as a base to map. Is it possible to first pull and mapped the columns from the input sheets to the macro sheet say in column B, C, D depends upon the count of input files before consolidating the sheets? In case I need to move columns as per the requirement and once that is done on the next click it should merge the columns one after the other base on the sequence of the columns in the macro sheet. I am not sure if this is possible just a thought

4. Can the mapping of the columns between the 2 input workbooks be put inside the code? You need to give this mapping.

JD – I am fine with anyway.

Any other points you can think of which might have a bearing on the coding?

JD – I am concerned about the column heading name in input sheets.

Ex – Name ID – correct name

Name Cause – Raw data name


Try to upload data files which have as much of the variations as possible.

JD – I will to create replica of one of the raw data for your better understanding. Could you please let me know if I am not able to clear anything in the above context?

Regards,

JD
 

Open result workbook, paste code to Sheet1 module
and amend the FOLDER variable (for data workbooks) …

​
 

Your bad ! If you put full path, remove ThisWorkbook.Path ‼ :rolleyes:

Check FOLDER variable into Local variables window in debug mode …
​
 
Hi Marc

The code is working fine, but it works when we have same numbers of column in both the raw_data sheets (data1, data2). Is it possible to describe the list of columns in the macro sheet in our case – Result sheet where we have added the code and on these bases it should pick the data from the RAW_data sheet say in our case data1 and data2? There were many columns in the data1 and data2 sheets which I do not want in Result sheet. Please let me know if this is possible?

Also, the count of raw_data sheets may vary I presume this will not be any concerned with the current set of code.

Regards,

JD
 
Try this !​
Code: 
Sub Demo2()
    FOLDER$ = ThisWorkbook.Path & "\Data\"
With Cells(1).CurrentRegion
    CARL = Application.Index(.Rows(1).Resize(2).Value, 1)
    HDR$ = Join$(CARL)
    .Offset(1).Clear
    Application.ScreenUpdating = False
End With
         F$ = Dir(FOLDER & "*.xlsx")
Do Until F = ""
    With Workbooks.Open(FOLDER & F).Worksheets(1).Cells(1).CurrentRegion.Rows
        If .Count > 1 Then
            BERG = Application.Index(.Item("1:2").Value, 1)
   
            With .Item("2:" & .Count)
                If Join(BERG) = HDR Then
                    .Copy Cells(Me.Rows.Count, 1).End(xlUp)(2)
                Else
                     R& = Cells(Me.Rows.Count, 1).End(xlUp).Row + 1

                    For S& = 1 To UBound(CARL)
                         V = Application.Match("*" & CARL(S) & "*", BERG, 0)
                         If IsNumeric(V) Then .Columns(V).Copy Cells(R, S) Else If S = 1 Then Exit For
                    Next
                End If
            End With
        End If
                .Parent.Parent.Close False
    End With
                F = Dir
Loop
End Sub

You like ? So thanks to …
​
 
You must log in or register to reply here.
Back
Top