Make a difference Amount from 2 sheet using Macro

[abadilala]

Hi, I have a problem to be solved. I have data from sheet 2016 and 2017. What I want to do is to make another sheet to calculate the difference money within 2016 and 2017. Please see the image link attached:

https://ibb.co/hniZo6 --- case 1
https://ibb.co/jWcuo6 ---- case 2

What I want to do is simply create the new sheet to compare each column except column A (column name).
and another problem is the number of columns is not fixed. some case may have 1 column to be compared, another might have more. So I guess I need the number of columns to become a variable?

How is the idea to make the total (comparison) sheet using macro that can solve case 1 and case 2? please share your thought!

 

[wideboydixon]

Here's my stab:

Book1
	A	B	C
1	Name	Money	Asset
2	Rei	100	130
3	Komar	120	150
4	Sento	300	160
5	Rashid	150	180
2016

Book1
	A	B	C
1	Name	Money	Asset
2	Rei	100	180
3	Komar	130	160
4	Sento	290	170
5	Rashid	300	160
2017

Public Sub MakeDifferenceSheet()

Dim sheet2016 As Worksheet
Dim sheet2017 As Worksheet
Dim sheetDiff As Worksheet
Dim lastCol As Long
Dim lastRow As Long
Dim thisRow As Long
Dim thisCol As Long
Dim foundCol As Variant
Dim foundRow As Variant
Dim nextRow As Long

Set sheet2016 = Sheets("2016")
Set sheet2017 = Sheets("2017")
Set sheetDiff = Sheets.Add(After:=sheet2017)

lastCol = sheet2016.Cells(1, sheet2016.Columns.Count).End(xlToLeft).Column
lastRow = sheet2016.Cells(sheet2016.Rows.Count, 1).End(xlUp).Row
sheetDiff.Cells(1, 1).Value = sheet2016.Cells(1, 1).Value
For thisCol = 2 To lastCol
    sheetDiff.Cells(1, thisCol * 3 - 4).Value = sheet2016.Cells(1, thisCol).Value & " 2016"
    sheetDiff.Cells(1, thisCol * 3 - 3).Value = sheet2016.Cells(1, thisCol).Value & " 2017"
    sheetDiff.Cells(1, thisCol * 3 - 2).Value = "Difference " & sheet2016.Cells(1, thisCol).Value
Next thisCol
sheetDiff.Cells(1, 1).EntireRow.Font.Bold = True

nextRow = 2
For thisRow = 2 To lastRow
    foundRow = Application.Match(sheet2016.Cells(thisRow, 1).Value, sheet2017.Range("A:A"), 0)
    If Not IsError(foundRow) Then
        sheetDiff.Cells(nextRow, 1).Value = sheet2016.Cells(thisRow, 1).Value
        For thisCol = 2 To lastCol
            foundCol = Application.Match(sheet2016.Cells(1, thisCol).Value, sheet2017.Range("1:1"), 0)
            If Not IsError(foundCol) Then
                sheetDiff.Cells(nextRow, thisCol * 3 - 4).Value = sheet2016.Cells(thisRow, thisCol).Value
                sheetDiff.Cells(nextRow, thisCol * 3 - 3).Value = sheet2017.Cells(foundRow, foundCol).Value
                sheetDiff.Cells(nextRow, thisCol * 3 - 2).FormulaR1C1 = "=RC[-1]-RC[-2]"
            End If
        Next thisCol
        nextRow = nextRow + 1
    End If
Next thisRow

End Sub

WBD

 

[abadilala]

Perfect!! you are genius!