Consolidate non-blank cells from different worksheets into one column.

[Colonel_J]

Hi,

I have a problem I have been trying to solve for some time and wondered if anyone on here can help me.

I have a workbook with several worksheets with the same format. The content of each worksheet is different, with some cells blank and others with text.

I want to have a final 'summary' worksheet that will consolidate particular data from the previous worksheets. I want all the non-blank cells from one column (Column D) of all the worksheets to be copied into a single column of the 'summary' worksheet in the order they appear in the previous worksheets (i.e. non-blank data from worksheet 1 followed by non-blank data from worksheet 2, and so on).

Then, if possible, I also want a column in the 'summary' worksheet to display the data from column A of the previous worksheets that correspond to the non-blank cells from Column D.

I would like to have it configured so that I can change the data in the worksheets and it will still be consolidated correctly in the summary worksheet.

I may not have explained it well, but for example:


[TABLE="width: 575"]
<colgroup><col><col span="3"><col span="4"></colgroup><tbody>[TR]
[TD="colspan: 2"]Worksheet 1[/TD]
[TD="colspan: 2"]Worksheet 2[/TD]
[TD][/TD]
[TD="colspan: 2"]Summary Worksheet[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]D[/TD]
[TD]A[/TD]
[TD]D[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]2[/TD]
[TD]s[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]a[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]b[/TD]
[TD][/TD]
[TD]t[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]c[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]c[/TD]
[TD]4[/TD]
[TD]u[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]8[/TD]
[TD]d[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]d[/TD]
[TD]8[/TD]
[TD]v[/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]g[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]e[/TD]
[TD][/TD]
[TD]w[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]t[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]f[/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]u[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]g[/TD]
[TD]5[/TD]
[TD]y[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]w[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]y
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any help or advice would be much appreciated.

 

[hiker95]

Colonel_J,

Welcome to the MrExcel forum.

What version of Excel and Windows are you using?

If worksheet Summary does not exist the macro will create it. If, it does exist, then columns A and B will be cleared to received the new data from the other worksheets.

Sample worksheets:

Excel 2007
	A	B
1
2
3
4
5
6
7
8
9
Summary

Excel 2007
	A	B	C	D
1	a			2
2	b
3	c			4
4	d			8
5	e
6	f
7	g			5
8
Sheet1

Excel 2007
	A	B	C	D
1	s
2	t			5
3	u			5
4	v
5	w			3
6	x
7	y			1
8
Sheet2

After the macro (using arrays in memory) in worksheet Summary:

Excel 2007
	A	B
1	2	a
2	4	c
3	8	d
4	5	g
5	5	t
6	5	u
7	3	w
8	1	y
9
Summary

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub ConsolidateData()
' hiker95, 11/20/2013
' http://www.mrexcel.com/forum/excel-questions/740711-consolidate-non-blank-cells-different-worksheets-into-one-column.html
Dim ws As Worksheet, wSum As Worksheet
Dim a As Variant, o As Variant
Dim i As Long, ii As Long, n As Long
Application.ScreenUpdating = False
If Not Evaluate("ISREF(Summary!A1)") Then Worksheets.Add().Name = "Summary"
Set wSum = Worksheets("Summary")
wSum.Columns("A:B").Clear
For Each ws In ThisWorkbook.Worksheets
  If ws.Name <> "Summary" Then
    n = n + Application.CountA(ws.Columns(4))
  End If
Next ws
ReDim o(1 To n, 1 To 2)
For Each ws In ThisWorkbook.Worksheets
  If ws.Name <> "Summary" Then
    a = ws.Range("A1:D" & ws.Range("A" & Rows.Count).End(xlUp).Row)
    For i = LBound(a) To UBound(a)
      If a(i, 4) <> "" Then
        ii = ii + 1
        o(ii, 1) = a(i, 4): o(ii, 2) = a(i, 1)
      End If
    Next i
  End If
Next ws
With wSum
  .Cells(1, 1).Resize(UBound(o, 1), UBound(o, 2)) = o
  .Columns("A:B").AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ConsolidateData macro.

 

[Colonel_J]

Bloody excellent, hiker! Just what I needed. Thank you for the quick response!

 

[hiker95]

Colonel_J,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.