Required auto insert of Mismatch Data

ravi2628

New Member
Joined
Dec 20, 2017
Messages
23
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
8,879
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
23
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
8,879
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,082,246
Messages
5,363,984
Members
400,772
Latest member
solbebe

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top