Summarising row data into a separate column

[kamruk]

Hi,

I am trying to take string data from multiple sheets that is listed in the same row each time, and summarise it in a column (or row), while removing any gaps. There may be a quite obvious fix, but I have found no solution.

So I have data that looks like this:

Sheet 1

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Cow[/TD]
[TD][/TD]
[TD]Chicken[/TD]
[TD][/TD]
[TD]Barn[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Pig[/TD]
[TD][/TD]
[TD][/TD]
[TD]Sheep[/TD]
[/TR]
</tbody>[/TABLE]

...

etc.

And would like to then on my final sheet (let's say I only have 2 sheets of data and one final 3rd sheet) a summary of everything from the specific shown row, excluding any gaps e.g.:

Sheet 3

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Summary[/TD]
[/TR]
[TR]
[TD]Cow[/TD]
[/TR]
[TR]
[TD]Chicken[/TD]
[/TR]
[TR]
[TD]Barn[/TD]
[/TR]
[TR]
[TD]Pig[/TD]
[/TR]
[TR]
[TD]Sheep[/TD]
[/TR]
</tbody>[/TABLE]

Would this be possible? Any help is appreciated!

 

[kamruk]

kamruk,

In stead of having to track the changing number of worksheets, how about puting the results in a new worksheet, maybe of one the following names?

1. Results

2. Summary

3. Or, some other name

That's what I'm doing now! At the time I was stuck on the idea that the Sheets increase by number, but sticking to a result sheet makes much more sense, while adding more pages to be read. Thank you again!

 

[hiker95]

sticking to a result sheet makes much more sense

kamruk,

Here is another macro solution for you to consider, based on your above quote.

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

Sub UpdateSummary_V3()
' hiker95, 10/29/2015, ME897592
Dim wr As Worksheet, ws As Worksheet
Dim sr As Long, lc As Long, c As Range, a As Range, nr As Long
Application.ScreenUpdating = False
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(Before:=Sheets(1)).Name = "Results"
Set wr = Worksheets("Results")
With wr.Cells(1, 1)
  .Value = "Summary"
  .Font.Bold = True
End With
sr = 18
For Each ws In ThisWorkbook.Worksheets
  If ws.Name <> "Results" Then
    With ws
      lc = .Cells(sr, .Columns.Count).End(xlToLeft).Column
      For Each c In .Range(.Cells(sr, 1), .Cells(sr, lc))
        If Not c = vbEmpty Then
          Set a = wr.Columns(1).Find(c.Value, LookAt:=xlWhole)
          If a Is Nothing Then
            nr = wr.Cells(wr.Rows.Count, "A").End(xlUp).Row + 1
            wr.Cells(nr, 1).Value = c.Value
          End If
        End If
      Next c
    End With
  End If
Next ws
With wr
  .Columns(1).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, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then run the UpdateSummary_V3 macro.