Comparing two excels with Dynamic range

[Vingesh]

I am having a workbook with two sheets. Data source 1 and Datasource 2. Both can have any columns and rows. (Columns and rows may not be equal)

Datasource 1:

Company	A	B	C
12 Month Sales	9607​	10672​	6270​
24 Month Sales	8581​	5313​	1997​
36 Month Sales	7397​	19859​	9085​
12 Month Profit	17407​	11497​	5105​
24 Month Profit	7721​	19516​	16613​
36 Month Profit	1846​	14835​	10351​

Datasource 2:

Company	B	D	C
12 Month Sales	9607​	5775​	6270​
24 Month Sales	8581​	2887​	5105​
36 Month Sales	7397​	19859​	16613​
12 Month Profit	17407​	11497​	5105​
24 Month Profit	4370​	19516​	16613​
36 Month Profit	2355​	14835​	10351​

If both data source has same value in same cell then return DataSource 1 value. if value is different then "Datasource 1: datasouce1 value | Datasource2: datasource2 value"

see below output:

Company	Datasource1: A | Datasource2:B	Datasource1: B | Datasource2:D	C
12 Month Sales	9607​	Datasource1: 10672 | Datasource2:5775	6270​
24 Month Sales	8581​	Datasource1: 5313 | Datasource2:2887	Datasource1: 1997 | Datasource2:5105
36 Month Sales	7397​	19859​	Datasource1: 9085 | Datasource2:16613
12 Month Profit	17407​	11497​	5105​
24 Month Profit	Datasource1: 7721 | Datasource2:4370	19516​	16613​
36 Month Profit	Datasource1: 1846 | Datasource2:2355	14835​	10351​

I am able to achieve this with below formula:

=IF(Datasource1!A1=Datasource2!A1, Datasource1!A1, "Datasource1: " & Datasource1!A1 & " | Datasource2:" & Datasource2!A1)

I have created the VBA also. My question is i am currently selecting static range for the formula to be applied. but the number of columns and rows may change in both tables. Need to make a vba to pick the max column and max row from both tables and apply the formula to all cells.

VBA will be run once to compare both sheets


Hope this is clear. Could anyone provide macro for this. I have tried for almost a week.
Thanks in advance.

 

[Legacy 143009]

What happens if Datasource2 has the 5th column and Datasource1 doesn't?
Is it going to be empty like Datasource1 is?

 

[Vingesh]

Thanks for the reply.

Then in the 5th column of the compare sheet it will have Datasource1: <blank> | datasource2: xxx(value)

Same way for the rows too.

 

[Legacy 143009]

Assuming you have a "Output" sheet:

Sub compareData()
  Dim lRowDatasource1 As Integer, lRowDatasource2 As Integer, lColDatasource1 As Integer, lColDatasource2 As Integer, maxRow As Integer, maxCol As Integer

  lRowDatasource1 = Worksheets("Datasource 1").Cells(Rows.Count, 1).End(xlUp).Row
  lRowDatasource2 = Worksheets("Datasource 2").Cells(Rows.Count, 1).End(xlUp).Row
  lColDatasource1 = Worksheets("Datasource 1").Cells(1, Columns.Count).End(xlToLeft).Column
  lColDatasource2 = Worksheets("Datasource 2").Cells(1, Columns.Count).End(xlToLeft).Column

  maxRow = IIf(lRowDatasource1 >= lRowDatasource2, lRowDatasource1, lRowDatasource2)
  maxCol = IIf(lColDatasource1 >= lColDatasource2, lColDatasource1, lColDatasource2)

  For c = 1 To maxCol
    For r = 1 To maxRow
      If Worksheets("Datasource 1").Cells(r, c).Value = Worksheets("Datasource 2").Cells(r, c).Value Then
        Worksheets("Output").Cells(r, c).Value = Worksheets("Datasource 1").Cells(r, c).Value
      Else
        Worksheets("Output").Cells(r, c).Value = "Datasource 1: " & Worksheets("Datasource 1").Cells(r, c).Value & " | Datasource2: " & Worksheets("Datasource 2").Cells(r, c).Value
      End If
    Next
  Next

End Sub

 

[Vingesh]

@Flashbond : thanks a ton. you saved my day!!!

 

[Legacy 143009]

Nice to hear Have a nice day!