combine data in three sheets based on the first three coulmn of each sheet.

[shebrahimi]

Dear Experts
I would appreciate if you could let me know how to merge the data in 3 sheets in a new sheet based on comparing the first 3 columns of each sheet. A simple form of each three sheet is as follows.

x1	date2	date1	name
			abc
			abc
			xyz
			frt

<tbody>
</tbody>

y3	y2	y1	date2	date1	name
					abc
					xyz
					frt
					frt
					uop

<tbody>
</tbody>

z2	z1	date2	date1	name
				xyz
				xyz
				frt
				uop
				frt

<tbody>
</tbody>

I want to merge them as follow

Thanks in advance
regards

z2	z1	y3	y2	y1	x1	date2	date1	name
								abc
								abc
								frt
								frt
								xyz
								xyz
								uop

<tbody>
</tbody>

 

[JLGWhiz]

Comparison of the first three columns is unlikely, since your example layout indicates the first three columns of each sheet contain different type data. It is also unclear if the criteria is to execute a merge for a match, or mis-match, or other. A lot of guessing on this one.

 

[ask2tsp]

I used 3 test sheets (labelled "x", "y" and "z")

Excel Workbook
	A	B	C	D
1	x1	date2	date1	name
2	x1.1	2-sep	1-sep	abc
3	x1.2	6-sep	5-sep	abc
4	x1.3	10-sep	9-sep	xyz
5	x1.4	14-sep	13-sep	frt
x

Excel Workbook
	A	B	C	D	E	F
1	y3	y2	y1	date2	date1	name
2	y3.1	y2.1	y1.1	4-nov	1-nov	abc
3	y3.1	y2.1	y1.2	5-nov	2-nov	xyz
4	y3.1	y2.2	y1.3	6-nov	3-nov	frt
5	y3.2	y2.2	y1.4	7-nov	4-nov	frt
6	y3.2	y2.3	y1.5	8-nov	5-nov	uop
y

Excel Workbook
	A	B	C	D	E
1	z2	z1	date2	date1	name
2	z2.1	z1.1	4-dec	1-dec	xyz
3	z2.2	z1.1	7-dec	5-dec	xyz
4	z2.1	z1.2	2-dec	1-dec	frt
5	z2.3	z1.3	8-dec	5-dec	uop
6	z2.3	z1.4	9-dec	7-dec	frt
z

And a 4th sheet labelled "Merged" that, after running the mergeXYZ macro, looks like

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	x1	y3	y2	y1	z2	z1	date2	date1	name
2	x1.1	*	*	*	*	*	2-9-2016	1-9-2016	abc
3	x1.2	*	*	*	*	*	6-9-2016	5-9-2016	abc
4	*	y3.1	y2.1	y1.1	*	*	4-11-2016	1-11-2016	abc
5	x1.4	*	*	*	*	*	14-9-2016	13-9-2016	frt
6	*	y3.1	y2.2	y1.3	*	*	6-11-2016	3-11-2016	frt
7	*	y3.2	y2.2	y1.4	*	*	7-11-2016	4-11-2016	frt
8	*	*	*	*	z2.1	z1.2	2-12-2016	1-12-2016	frt
9	*	*	*	*	z2.3	z1.4	9-12-2016	7-12-2016	frt
10	*	y3.2	y2.3	y1.5	*	*	8-11-2016	5-11-2016	uop
11	*	*	*	*	z2.3	z1.3	8-12-2016	5-12-2016	uop
12	x1.3	*	*	*	*	*	10-9-2016	9-9-2016	xyz
13	*	y3.1	y2.1	y1.2	*	*	5-11-2016	2-11-2016	xyz
14	*	*	*	*	z2.1	z1.1	4-12-2016	1-12-2016	xyz
15	*	*	*	*	z2.2	z1.1	7-12-2016	5-12-2016	xyz
Merged

Go to the Visual Basic Environment (press ALT+F11), insert a module and paste this

Option Explicit

Dim rowM    As Long
Dim colM    As Long
Dim colMd2  As Long
Dim colMap  As Object

Private Sub addLabels(group As String)
    Dim sht As Worksheet
    Dim col As Long
    
    Set sht = ThisWorkbook.Worksheets(group)
    col = 1
    While Left(sht.Cells(1, col), 1) = group
        colM = colM + 1: shtM.Cells(1, colM) = sht.Cells(1, col)
        colMap.Item(shtM.Cells(1, colM).Value) = colM
        col = col + 1
    Wend
End Sub

Sub copyData(group As String)
    Dim sht     As Worksheet
    Dim col     As Long
    Dim colM    As Long
    Dim shtRow  As Long
    
    Set sht = ThisWorkbook.Worksheets(group)
    For shtRow = 2 To sht.UsedRange.Rows.Count
        'copy <group> columns
        col = 1
        While Left(sht.Cells(1, col), 1) = group
            colM = colMap.Item(sht.Cells(1, col).Value)
            shtM.Cells(rowM, colM).Value = sht.Cells(shtRow, col)
            col = col + 1
        Wend
        'copy date2, date1, name columns
        shtM.Cells(rowM, colMd2).Value = sht.Cells(shtRow, col) 'date2
        shtM.Cells(rowM, colMd2 + 1).Value = sht.Cells(shtRow, col + 1) 'date1
        shtM.Cells(rowM, colMd2 + 2).Value = sht.Cells(shtRow, col + 2) 'name
        rowM = rowM + 1
    Next shtRow
End Sub

Sub mergeXYZ()
    Dim i           As Integer
    Dim mergedArea  As Range
    Dim lastCol     As Long
    Dim lastRow     As Long
    
    shtM.UsedRange.ClearContents
    
    '----- setup columns -----
    
    Set colMap = CreateObject("scripting.Dictionary")
    
    colM = 0
    addLabels "x"
    addLabels "y"
    addLabels "z"
    
    colM = colM + 1: shtM.Cells(1, colM) = "date2"
    colMd2 = colM
    colM = colM + 1: shtM.Cells(1, colM) = "date1"
    colM = colM + 1: shtM.Cells(1, colM) = "name"
    
    '----- copy data -----
    
    rowM = 2
    copyData "x"
    copyData "y"
    copyData "z"
    
    Set colMap = Nothing
    
    '----- sort on name -----
    
    Set mergedArea = shtM.Range("A1").CurrentRegion
    lastCol = mergedArea.Columns.Count
    lastRow = mergedArea.Rows.Count
    shtM.Sort.SortFields.Clear
    shtM.Sort.SortFields.Add _
    Key:=shtM.Range(mergedArea.Cells(2, lastCol), mergedArea.Cells(lastRow, lastCol)) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With shtM.Sort
        .SetRange mergedArea
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
End Sub

press Fn4, this opens the properties pane (PP).
In the project explorer pane (EP) select sheet labelled "x". In PP change name to shtX.
In EP select sheet labelled "y". In PP change name to shtY.
In EP select sheet labelled "z". In PP change name to shtZ.
In EP select sheet labelled "Merged". In PP change name to shtM.

 

[shebrahimi]

Hi
I thank you very much for sharing your time and expertise. Indeed, some dates are common in the 3 separate sheets so that I want if the same names and dates exist in them, they will be matched against each other. Sorry, does it do this?
Thanks in advance,
Best regards

 

[ask2tsp]

if the same names and dates exist in them, they will be matched against each other.

There are two dates. How should they be the same? What are these two dates. Begindate and Enddate? Must they both be the same or overlap? Please tell us something more about what we are dealing with here?

What JGLWhiz also already noted, you are unclear in describing what you want to do. Give us more info. What do all these columns mean; where does the data come from; why a different number of columns in x,y and z.

 

[shebrahimi]

Hi. Thanks a lot. For example, date 1 and date2 for abc would be the same in X,Y and Z. In fact, date 1 is financial year and date 2 is when company release these data. X, Y and Z are respectively balance sheet, income statement and cash flow statement. I want for each company in each year all 3 statements will be in one worksheet. columns are different parts of those statements for example cash, receivables, sales, cost of goods sold, financial expenses, cash flow from operations, cash flow from investments and so on.
Best regards, Thanks in advance.