Combing and Adding Data in Multiple Rows (Macro Needed)

Anyone can help on this, please -- looking for a macro. Thx


CURRENT:


Column A Column B Column C .... Column J


Cat 5 1
Dog 8 2
Dog 3 3
Cat 2 4


DESIRED: (all columns will have the same concept of combining)


Column A Column B Column C .... Column J


Cat 7 5
Dog 11 5


Thanks Much!

Put headers in row 1 if you don't already have them.
then do

Data=>PivotTable Report and Chart

Follow the wizard.

Put the first column in as a row field and the other columns in as Data
fields.

If it initially comes out as

Cat columnB 7
columnC 5

then select the Data button, drag slightly to the right while holding and
release
--
Regards,
Tom Ogilvy

<[email protected]> wrote in message
news:[email protected]...
> Anyone can help on this, please -- looking for a macro. Thx
>
>
> CURRENT:
>
>
> Column A Column B Column C .... Column J
>
>
> Cat 5 1
> Dog 8 2
> Dog 3 3
> Cat 2 4
>
>
> DESIRED: (all columns will have the same concept of combining)
>
>
> Column A Column B Column C .... Column J
>
>
> Cat 7 5
> Dog 11 5
>
>
> Thanks Much!
>

Skip,

Use a Pivot table. Slect your data table, then use DAta / Pivot table. Drag the Column A button to
the row field, all other column buttons into the data fields (set to sum), and then when they're all
there, drag the data field (grey button) to the header of the pivot table to place them as column
headers, and you're done. Not a single formula in sight....

HTH,
Bernie
MS Excel MVP


<[email protected]> wrote in message
news:[email protected]...
> Anyone can help on this, please -- looking for a macro. Thx
>
>
> CURRENT:
>
>
> Column A Column B Column C .... Column J
>
>
> Cat 5 1
> Dog 8 2
> Dog 3 3
> Cat 2 4
>
>
> DESIRED: (all columns will have the same concept of combining)
>
>
> Column A Column B Column C .... Column J
>
>
> Cat 7 5
> Dog 11 5
>
>
> Thanks Much!
>

If I wanted to use a Macro and combine it together, can I do it that
way? If so, can you assist on the code?

Thanks Guys,

Skip

Skip,

This will put the summary below the current table. Select a cell within your data table, and run
the macro.

HTH,
Bernie
MS Excel MVP

Sub CombineForSkip()
Dim myCell As Range
Set myCell = ActiveCell
myCell.CurrentRegion.Sort _
myCell.CurrentRegion.Range("A1"), _
xlAscending, Header:=xlYes
myCell.CurrentRegion.Subtotal GroupBy:=1, _
Function:=xlSum, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
myCell.CurrentRegion.SpecialCells(xlCellTypeVisible).Copy
Range("A65536").End(xlUp)(5).PasteSpecial Paste:=xlPasteValues
myCell.CurrentRegion.RemoveSubtotal
myCell.Select
End Sub


<[email protected]> wrote in message
news:[email protected]...
> If I wanted to use a Macro and combine it together, can I do it that
> way? If so, can you assist on the code?
>
> Thanks Guys,
>
> Skip
>

Hi Bernie - I am getting a ...

run time error '1004'
application-defined or object-defined error

Skip,

Try this version: Your table should start in column A

HTH,
Bernie
MS Excel MVP

Sub CombineForSkip2()
Dim myArray() As Integer
Dim i As Integer
Dim myCell As Range
Set myCell = ActiveCell
ReDim myArray(1 To myCell.CurrentRegion.Columns.Count - 1)
For i = 1 To UBound(myArray)
myArray(i) = i + 1
Next i
myCell.CurrentRegion.Sort _
myCell.CurrentRegion.Range("A1"), _
xlAscending, Header:=xlYes
myCell.CurrentRegion.Subtotal GroupBy:=1, _
Function:=xlSum, TotalList:=myArray, SummaryBelowData:=True
ActiveSheet.Outline.ShowLevels RowLevels:=2
myCell.CurrentRegion.SpecialCells(xlCellTypeVisible).Copy
Cells(65536, myCell.CurrentRegion.Columns(1).Column).End(xlUp)(5) _
.PasteSpecial Paste:=xlPasteValues
myCell.CurrentRegion.RemoveSubtotal
myCell.Select
End Sub

<[email protected]> wrote in message
news:[email protected]...
> Hi Bernie - I am getting a ...
>
> run time error '1004'
> application-defined or object-defined error
>