Required auto insert of Mismatch Data

ravi2628

New Member
Joined
Dec 20, 2017
Messages
26
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

NameIDJanFebMarAprMayJunJulAug
Ravi42
1096
14
ravi141011
fbgsdf522464555525536

<tbody>
</tbody>

Table 2/Spread Sheet 2/Work book 2

NameIDFebMarJanJulAugApr
Ravi1450
teja855163655854
dfsgbd967678547

<tbody>
</tbody>

Output

NameIDJanFebMarAprMayJunJulAug
Ravi42109614
ravi14105011
fbgsdf522464555525536
teja853655165854
dfsgbd967678547

<tbody>
</tbody>

Thanks in Advance for every one

Thanks & Regards,
Raviteja
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,694
Office Version
2007
Platform
Windows
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

New Member
Joined
Dec 20, 2017
Messages
26
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

Well-known Member
Joined
Dec 3, 2018
Messages
9,694
Office Version
2007
Platform
Windows
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.

Code:
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
 

Forum statistics

Threads
1,085,694
Messages
5,385,225
Members
401,936
Latest member
stephenpoff

Some videos you may like

This Week's Hot Topics

Top