Combing and Adding Data in Multiple Rows (Macro Needed)

  • Thread starter Thread starter Skip.Gibson
  • Start date Start date
S

Skip.Gibson

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
 
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
 
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
 
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
 
I am getting a Compile Error / Syntax error. Any other thoughts?
Thanks

The very top left entry A1 is Cat.

Cat 5 1
Dog 4 2
Dog 4 3
Cat 5 4
 
I can't get your error: make sure that none of the lines have been broken into two lines by your
news reader - look for lines in red (typically) once you paste them into your codemodule.

If that doesn't work, send me your workbook, and I will take a look and get it working. Reply to me,
then take out the spaces and change the dot to . in my email address.

HTH,
Bernie
MS Excel MVP
 
It worked for me with his posted data, but if Cat is in A1 as he stated, it
causes a slight problem in the results.

All the Data functions like a header in row 1.
 
Tom,

The lack of headers wasn't his problem. He copied the code through web
access, and the code in the workbook that he sent me had a few dashes
sprinkled throughout. That is something that I have seen recently when
copying code out of the Google archives, but haven't experienced with active
messages. I guess it is something that we will need to start warning about.

Bernie
 
Bernie,
I knew that wasn't the problem - which is why I emphasized that the code
worked fine for me - nonetheless, what I stated as a problem is a problem
once the OP gets it working with his layout as described - easily rectified.
So when I say it worked fine for me, I needed to tell the whole story.
 
Back
Top