0

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..?

migrated from stackoverflow.com Jul 5 '13 at 9:22

This question came from our site for professional and enthusiast programmers.

  • May be VBA or someother .....I just want the output... – CarlJohn Jul 5 '13 at 7:16
0

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 !

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.