Excel- Merge two worksheets with different text data

Question

I have two excel worksheets that have the same column and row information.

However, one of the sheets contains blank cells where the other document has text information (but both spreadsheets are too large to know exactly where one contains info and the other doesn't)

I want to merge the two worksheets such that the final "Master Worksheet" contains all the information contained in both worksheets.

I want to get all the information from: $T$5381:$AP$5400 of worksheet "B" and paste it into the same range in worksheet "A"

I have tried using the 'Consolidate' function in Excel, but that appears to only work if the data is numeric. I have tried to select all the data from one workbook, highlight the blanks of the other workbook and paste, but Excel won't let me because the "copy area and paste area are not the same size or shape."

Does anybody have any ideas?

So like this

Sheet 1

   A  B  C
1  A  C
2  Z
3

Sheet 2

Master Sheet

   A  B  C
1  A  C  X
2  Z
3  L  O  R

Unknown Unknown · Accepted Answer · 2014-02-25T10:50:12

Try something like this;

note: you may need to adjust the sheet names

Sub MergeData()
Application.ScreenUpdating = False

    Dim A, B, C, i&, j&
    Set A = Sheets("Sheet1")
    Set B = Sheets("Sheet2")
    Set C = Sheets("Sheet3")

    For i = 1 To A.UsedRange.Rows.Count + 10
        For j = 1 To A.UsedRange.Columns.Count + 10
            C.Cells(i, j) = IIf(IsEmpty(A.Cells(i, j)), B.Cells(i, j), A.Cells(i, j))
        Next j
    Next i
Application.ScreenUpdating = True
End Sub

Excel- Merge two worksheets with different text data

1 Answers