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

Name	ID	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug
Ravi	42		10		96		14
ravi	14	10			11
fbgsdf	52	246		455		5525			536

<tbody>
</tbody>

Table 2/Spread Sheet 2/Work book 2

Name	ID	Feb	Mar	Jan	Jul	Aug	Apr
Ravi	14	50
teja	85		516	365	585	4
dfsgbd	96	76	78			547

<tbody>
</tbody>

Output

Name	ID	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug
Ravi	42		10		96		14
ravi	14	10	50		11
fbgsdf	52	246		455		5525			536
teja	85	365		516				585	4
dfsgbd	96		76	78					547

<tbody>
</tbody>

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