CODEWITHSUNDEEP
excelvba
Kolja
Kolja

Reputation: 868

Conditional merge of rows?

I have two excel sheets that I would like to merge. Some but not all rows could be identical to some degree, and I want to make 100% sure I am not losing data.

Here's what I mean:

Sheet 1 contains:

FName, Lname, Street, HasDog, HasCat, HasHorse
Joe, Carlson, 123 Street, 1, 1, 0
Bill, Jones, 345 Street, 1, 0, 0


Sheet 2 contains:

FName, Lname, Street, HasFish, HasBird
Joe, Carlson, 123 Street, 1, 0
Bill, Jones, 345 Street, 0, 1

In the end, I would like to have this:

Final Sheet Contains:

FName, Lname, Street, HasDog, HasCat, HasHorse, HasFish, HasBird
Joe, Carlson, 123 Street, 1, 1, 0, 1, 0
Bill, Jones, 345 Street, 1, 0, 0, 0, 1

Note: All entries should be on the final sheet, no matter if they are in sheet 1, 2 or both.

The data that were not set (such as HasFish) would be empty or "0"

Upvotes: 0

Views: 171

Answers (1)

Tony Dallimore
Tony Dallimore

Reputation: 12413

This site is for programmers to help other programmers improve. It is not a free coding site although sometimes large amounts of code are provided particularly if the answerer finds the question interesting. I do not think there is anything challenging in achieving your requirement so I doubt anyone will give you an entire macro.

You must learn Excel VBA. Even if someone did code a macro for you, it is unlikely to be just what you want. Without knowledge of VBA you would not be able to make those final changes.

Search the internet for "Excel VBA Tutorial". There are many to chose from. I consider some poor but others are very good. Try a few and pick one that sees appropriate to your learning style. Perhaps you prefer books. Visit a good library or bookshop to see a selection of Excel VBA Primers. The advantage of a library is you can take a couple of primers home and try them out before deciding which to purchase. I like to have a reference book on my desk so that was my approach.

I believe the key areas of knowledge you need to tackle this problem are:

  • Copy data from a worksheet to an array
  • Copy data from an array to a worksheet
  • Array handling
  • For-loops
  • If-Else-EndIf

I assume the example data you have shown us is fake. In the real data, are the identifying columns on the left or are they scattered? In the real data are the two worksheets in the same sequence? I would move columns and sort worksheets as necessary to make the programming as easy as possible. You can rearrange the rows and columns again after you have finished.

The macro below:

  • Copies worksheet "Src1" to an array. You will have to replace "Src1" with your name for the worksheet.
  • Concatenates the values from the top and bottom rows.
  • Outputs the concatenated values to the Immediate Window.

This macro is intended to demonstrate that it is not difficult to access data from worksheets or to manipulate that data.

Divide your problem into small steps. Write a macro that performs step 1 and proves it has performed the step correctly. Extend the macro to perform steps 1 and 2 then steps 1, 2 and 3. And so on. I have written the step 1 macro for you. Try to extend that macro. Come back here if your code for a step does not work; I am sure someone will help.

Option Explicit
Sub LoadSrc1()

  Dim ColSrc1Crnt As Long
  Dim ColSrc1Last As Long
  Dim RowSrc1Last As Long
  Dim ValuesSrc1 As Variant
  Dim StgBot As String
  Dim StgTop As String

  With Worksheets("Src1")

    RowSrc1Last = .Cells(Rows.Count, "A").End(xlUp).Row
    ColSrc1Last = .Cells(1, Columns.Count).End(xlToLeft).Column

    ValuesSrc1 = .Range(.Cells(1, 1), .Cells(RowSrc1Last, ColSrc1Last)).Value

    StgBot = ""
    StgTop = ""
    For ColSrc1Crnt = 1 To ColSrc1Last
      StgTop = StgTop & ValuesSrc1(1, ColSrc1Crnt) & "  "
      StgBot = StgBot & ValuesSrc1(RowSrc1Last, ColSrc1Crnt) & "  "
    Next

  End With

  Debug.Print StgTop
  Debug.Print StgBot

End Sub

Upvotes: 1

Related Questions