2 inputsheets

[Fodiographer]

I want to filter and merge data from 2 input sheets into two result sheets. (see attachment!)

In sheet 'result1" i want to have this:

If any cell "column A sheet Input1" equals (throug Vertical Lookup) a cell of "column A sheet input 2" = not true then skip but if true then show in "sheet result"
for instance "cell A4 sheet Input 1" equals "cell A3 sheet Input2" then show "cells A3 and B3 sheet Input 2" at the place of "A2 and B2 of sheet result" and show
"cells B4,C4,D4,E4 sheet Input2" at the place of "cells C2,D2,E2,F2 of sheet Result"

If cells in "column A sheet Input1" doesn't equal any cells in "column A sheet Input2" then skip in "sheet result"

Sheet Result is totally filled with formula results of sheets "Input1" and "Input2"

In sheet "result2" i want to have this:

if any cell "column A sheet Input2" does not equal any cell " column A sheet Input 1" then show
in sheet Results2.
"A3,B3 sheet Input2" should be placed at "A2,B2 sheet Result2" etc.

Please can someone help me with this? the two input sheets are coming from 2 different parties so i have to merge and filter the sheets myself. This is just a small portion of the data and also i have to merge&filter this every month so it is crucial i have some sort of formula to deal with this!

Thanks

[rylo]

Hi

Here goes

Sub aaa()
  Dim OutSH As Worksheet
  Set OutSH = Sheets("Result1")
  'create headings
  Sheets("Result1").Range("A1:B1").Value = Sheets("Input2").Range("A1:B1").Value
  Sheets("Result1").Range("C1:F1").Value = Sheets("Input1").Range("B1:E1").Value
  Sheets("Result2").Range("A1:B1").Value = Sheets("Input2").Range("A1:B1").Value
  'process matches
  With Sheets("Input1")
    For Each ce In .Range("A2", .Range("A2").End(xlDown))
      Set findit = Sheets("Input2").Range("A:A").Find(what:=ce)
      If Not findit Is Nothing Then
        outrow = OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        OutSH.Cells(outrow, 1).Resize(1, 2).Value = findit.Resize(1, 2).Value
        OutSH.Cells(outrow, 3).Resize(1, 4).Value = ce.Offset(0, 1).Resize(1, 4).Value
      End If
    Next ce
  End With
  
  'process non matches
  Set OutSH = Sheets("Result2")
  With Sheets("Input2")
    For Each ce In .Range("A2", .Range("A2").End(xlDown))
      Set findit = Sheets("Input1").Range("A:A").Find(what:=ce)
      If findit Is Nothing Then
        outrow = OutSH.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        OutSH.Cells(outrow, 1).Resize(1, 2).Value = ce.Resize(1, 2).Value
      End If
    Next ce
  
  End With
End Sub

rylo

[Fodiographer]

Thank you rylo for solving it for me! I really appreciate it!

[royUK]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

I've also edited the thread title to remove unnecessary wording.

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.