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
9,522
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
9,522
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,084,733
Messages
5,379,498
Members
401,607
Latest member
Zemexi

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top