How to merge/consolidate 2 worksheets with text data, not numbers

Hello gurus!

I'm trying to figure out how to consolidate/merge multiple worksheets (data as text) with different column names with text. I understand that the "consolidate" function only works for worksheet with data as numbers. Here's my sample data:

Worksheet 1


Col1 Col2 Col3
1   Col2  
2     Col3
3   Col2  
4 Col1   Col3
5 Col1 Col2 Col3

Worksheet 2

ID Col1 Col2 Col3 Col4  Col5 Col6
a   Col2   Col4    
b     Col3     Col6
c   Col2     Col5  
d Col1   Col3      
e Col1 Col2 Col3      
f       Col4   Col6

May 20th, 2015 6:29pm

Hi MusicMan,

Based on your description, you wonder to merge text data in excl. As far as I know the Consolidation feature of Excel will not consolidate text. The only text that Consolidation will bring across is Category Titles. Titles are brought into the destination sheet when you select Top Row or Left Column from the Consolidate dialog box. Any other text in the source areas is not brought across. If the source areas contain text other than Category Titles, blanks are brought into the destination area.

Now I test your issue in my own environment. And I got two results as shown in the following figures.

For this result, I merge the text data without the title of worksheet2. I use VBA code to get this result.

Sub Combine()

Dim J As Integer

On Error Resume Next



Sheets(1).Name = "Combined"



Selection.Copy Destination:=Sheets(1).Range("A1")

For J = 2 To Sheets.Count




Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)


End Sub

My VBA code might be not perfect, you could refer to this article about consolidating data from multiple worksheets into a summary worksheet in excel.

If you need more help about code, please post your issue in MSDN forum for Excel.

Then I test another method with an add-in, and you can get the result with whole contents in 2 worksheets. As shown in the following figure.

For this result you can refer to this article and get more information about this result.

Please Note: Since the web site is not hosted by Microsoft, the link may change without notice. Microsoft does not guarantee the accuracy of this information.

Hope its helpful.


George Zhao
TechNet Community Support

Free Windows Admin Tool Kit Click here and download it now
May 21st, 2015 3:22am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics