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

ID	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

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).Select

Worksheets.Add

Sheets(1).Name = "Combined"

Sheets(2).Activate

Range("A1").EntireRow.Select

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

For J = 2 To Sheets.Count

Sheets(J).Activate

Range("A1").Select

Selection.CurrentRegion.Select

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

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

Next

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.

https://msdn.microsoft.com/en-us/library/cc793964(office.12).aspx

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

http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

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.

http://www.extendoffice.com/documents/excel/1184-excel-merge-multiple-worksheets-into-one.html#a1

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.

Regards,

George Zhao
TechNet Community Support

You are awesome, George! Your code works as described.

I just come across a more challenging scenario. What if the columns heading are not identical? For example: (BTW, I have to combine 2 worksheets. One with < 200 columns and the other < 350 columns!)

Worksheet 1

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

Worksheet 2

ID	Col1	Col3	Col5	Col6	Col7	Col8
a					Col7
b		Col3		Col6		Col8
c			Col5
d	Col1	Col3			Col7
e	Col1	Col3				Col8
f				Col6	Col7

• Edited by MusicMan66 16 hours 2 minutes ago

You are awesome, George! Your code works as described.

I just come across a more challenging scenario. What if the columns heading are not identical? For example: (BTW, I have to combine 2 worksheets. One with < 200 columns and the other < 350 columns!)

Worksheet 1

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

Worksheet 2

ID	Col1	Col3	Col5	Col6	Col7	Col8
a					Col7
b		Col3		Col6		Col8
c			Col5
d	Col1	Col3			Col7
e	Col1	Col3				Col8
f				Col6	Col7

• Edited by MusicMan66 Thursday, May 21, 2015 3:29 PM

Use Power Query with "Merge Columns".
Share file.

Hi MusicMan,

Based on your new question, I get the result as shown in the following figure by using Power Query:

You can watch this video and learn how to Merge Multiple Worksheets in Workbook by Power Querty.

https://www.youtube.com/watch?v=LSDQGWdgNJs

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.

Regards,

George Zhao
TechNet Community Support