Combine 2 excel sheets having common values in one column

Question

I've 2 excel sheets . I want to combine them based on common values in a particular column.

Example:

if excel-A has values like this

orderNo  Product

C1-231  Spares
C1-232  Back-Ups
C1-242  SmatTerminal

and Excel-B like

Type            Activity       orderNo      Date

Standard        Maintenance     C1-230     2012-12-01
Standard        Maintenance     C1-231     2012-12-01
Standard        Maintenance     C1-232     2012-12-01
Standard        Quality         C1-240     2012-12-01

Then i want my output file like

orderNo   Product           Type             Activity    orderNo       Date

C1-231    Spares            Standard        Maintenance  C1-231     2012-12-01
C1-232    Back-Ups          Standard        Maintenance  C1-232     2012-12-01
C1-242    SmatTerminal       
                            Standard        Maintenance  C1-230     2012-12-01
                            Standard        Quality      C1-240     2012-12-01

I don't know how to do combine them.Anyone please guide me to achieve this..?

Answer 1

In VBA ... I'll do it in 2 loops

Assumed that excel A is Sheet1, excel B is Sheet2 and your result Sheet is Sheet3 .. and Start in A2

Sub MergeIt()
Dim LastA,LastA2 as Range
Dim y,y2 as Integer

Set LastA = Range("Sheet1!A65536").End(xlUp)
Set LastA2 = Range("Sheet2!A65536").End(xlUp)

For y = 2 to LastA.Row
  Sheet3!Cells(y,1) = Sheet1!Cells(y,1)
  Sheet3!Cells(y,2) = Sheet1!Cells(y,2)
  For y2=2 To LastA2
    If Sheet2!Cells(y2,3) = Sheet1!Cells(y,1) Then
      Sheet2!Cells(y2,5) = "v"

      Sheet3!Cells(y,3) = Sheet2!Cells(y2,1)
      Sheet3!Cells(y,4) = Sheet2!Cells(y2,2)
      Sheet3!Cells(y,5) = Sheet2!Cells(y2,3)
      Sheet3!Cells(y,6) = Sheet2!Cells(y2,4)
    End If
  Next
Next

y= y + 1
For y2 = 2 to LastA2
  If Sheet2!Cells(y,5) <> "v" Then
    Sheet3!Cells(y,3) = Sheet2!Cells(y2,1)
    Sheet3!Cells(y,4) = Sheet2!Cells(y2,2)
    Sheet3!Cells(y,5) = Sheet2!Cells(y2,3)
    Sheet3!Cells(y,6) = Sheet2!Cells(y2,4)
  End If
Next

End Sub

Hope .. hope .. this help !