Required auto insert of Mismatch Data

[ravi2628]

Hi Every one,


I required a macro or Formula to Auto insert the Row after matching of two Spreadsheets

Table 1/Spread Sheet 1/Work book 1

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Name[/TD]
[TD]ID[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[/TR]
[TR]
[TD]Ravi[/TD]
[TD]42
[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD]96
[/TD]
[TD][/TD]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ravi[/TD]
[TD]14[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]fbgsdf[/TD]
[TD]52[/TD]
[TD]246[/TD]
[TD][/TD]
[TD]455[/TD]
[TD][/TD]
[TD]5525[/TD]
[TD][/TD]
[TD][/TD]
[TD]536[/TD]
[/TR]
</tbody>[/TABLE]

Table 2/Spread Sheet 2/Work book 2

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Name[/TD]
[TD]ID[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Jan[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[TD]Apr[/TD]
[/TR]
[TR]
[TD]Ravi[/TD]
[TD]14[/TD]
[TD]50
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]teja[/TD]
[TD]85[/TD]
[TD][/TD]
[TD]516[/TD]
[TD]365[/TD]
[TD]585[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]dfsgbd[/TD]
[TD]96[/TD]
[TD]76[/TD]
[TD]78[/TD]
[TD][/TD]
[TD][/TD]
[TD]547[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Output

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Name[/TD]
[TD]ID[/TD]
[TD]Jan[/TD]
[TD]Feb[/TD]
[TD]Mar[/TD]
[TD]Apr[/TD]
[TD]May[/TD]
[TD]Jun[/TD]
[TD]Jul[/TD]
[TD]Aug[/TD]
[/TR]
[TR]
[TD]Ravi[/TD]
[TD]42[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD]96[/TD]
[TD][/TD]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ravi[/TD]
[TD]14[/TD]
[TD]10[/TD]
[TD]50[/TD]
[TD][/TD]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]fbgsdf[/TD]
[TD]52[/TD]
[TD]246[/TD]
[TD][/TD]
[TD]455[/TD]
[TD][/TD]
[TD]5525[/TD]
[TD][/TD]
[TD][/TD]
[TD]536[/TD]
[/TR]
[TR]
[TD]teja[/TD]
[TD]85[/TD]
[TD]365[/TD]
[TD][/TD]
[TD]516[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]585[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]dfsgbd[/TD]
[TD]96[/TD]
[TD][/TD]
[TD]76[/TD]
[TD]78[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]547[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in Advance for every one

Thanks & Regards,
Raviteja

 

[DanteAmor]

You could give a little more information.

1. "Jan". Is it a date or is the text "Jan"
2. "ID". In which cell is it?
3. "Table 2/Spread Sheet 2/Work book 2". How many sheets does the book2 have? Or do you mean that the information will be on the second sheet, or is it a typing error and is sheet1 of book2?
4. "Output". Where will the output be, in book1 on sheet2?
5. Example:
Spread Sheet 1/Work book 1 / ID 52 / Mar 455
Spread Sheet 2/Work book 2 / ID 52 / Mar 30

Output:
ID 52 / Mar 485
or
ID 52 / Mar 30?

 

[ravi2628]

Hi Danteamor,

1. "Jan". Is it a date or is the text "Jan": it a Text
2. "ID". In which cell is it?:Cell ID is Like B1
3. "Table 2/Spread Sheet 2/Work book 2":its 2 different Spread Sheets

First Input in Workbook 1
Second Input in WorkBook2 2
Output Required in WoorkBook 1

 

[DanteAmor]

You did not answer question 5. I guess you want to add the value of input1 plus the value of input2.


And it is not clear to me which sheet is the Second input: is it on the second sheet of book 2?


But let's start with the following:


Change the data in red for your information.

Sub Auto_Insert_Mismatch_Data()
  Dim wb1 As Workbook, wb2 As Workbook, sh1 As Worksheet, sh2 As Worksheet
  Dim i As Long, j As Long, c As Long, r As Long, f As Range
  '
  Set wb1 = ThisWorkbook
  Set sh1 = wb1.Sheets(1)
  Set wb2 = Workbooks("[COLOR=#ff0000][B]Book2.xlsx[/B][/COLOR]") 'name of book2
  Set sh2 = wb2.Sheets([B][COLOR=#ff0000]2[/COLOR][/B])    '2 if it is the second sheet, 1 if it is the first sheet
  '
  For i = 2 To sh2.Range("B" & Rows.Count).End(xlUp).Row
    Set f = sh1.Range("B:B").Find(sh2.Range("B" & i).Value, , xlValues, xlWhole)
    If f Is Nothing Then
      r = sh1.Range("B" & Rows.Count).End(xlUp).Row + 1
      sh1.Range("A" & r).Value = sh2.Range("A" & i).Value
      sh1.Range("B" & r).Value = sh2.Range("B" & i).Value
    Else
      r = f.Row
    End If
    For j = 3 To sh2.Cells(1, Columns.Count).End(xlToLeft).Column
      If sh2.Cells(i, j).Value <> "" Then
        Set f = sh1.Rows(1).Find(sh2.Cells(1, j), , xlValues, xlWhole)
        If Not f Is Nothing Then
          c = f.Column
          sh1.Cells(r, c).Value = sh1.Cells(r, c).Value + sh2.Cells(i, j).Value
        End If
      End If
    Next
  Next
End Sub