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

<Skip.Gibson@fairfieldresorts.com> wrote in message
news:1123266471.220208.145990@z14g2000cwz.googlegroups.com...
> 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


<Skip.Gibson@fairfieldresorts.com> wrote in message
news:1123266471.220208.145990@z14g2000cwz.googlegroups.com...
> 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


<Skip.Gibson@fairfieldresorts.com> wrote in message
news:1123268075.941475.325820@g49g2000cwa.googlegroups.com...
> 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

<Skip.Gibson@fairfieldresorts.com> wrote in message
news:1123271443.618996.176700@g47g2000cwa.googlegroups.com...
> Hi Bernie - I am getting a ...
>
> run time error '1004'
> application-defined or object-defined error
>

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


<Skip.Gibson@fairfieldresorts.com> wrote in message
news:1123275025.145014.267900@g49g2000cwa.googlegroups.com...
>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
>

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.

--
Regards,
Tom Ogilvy


"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:evv9jCgmFHA.2580@TK2MSFTNGP09.phx.gbl...
> 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
>
>
> <Skip.Gibson@fairfieldresorts.com> wrote in message
> news:1123275025.145014.267900@g49g2000cwa.googlegroups.com...
> >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
> >
>
>

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


"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:%23In78HgmFHA.708@TK2MSFTNGP09.phx.gbl...
> 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.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> news:evv9jCgmFHA.2580@TK2MSFTNGP09.phx.gbl...
>> 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
>>
>>
>> <Skip.Gibson@fairfieldresorts.com> wrote in message
>> news:1123275025.145014.267900@g49g2000cwa.googlegroups.com...
>> >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
>> >
>>
>>
>
>

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.

--
Regards,
Tom Ogilvy

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:%23wNgMlimFHA.3120@TK2MSFTNGP09.phx.gbl...
> 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
>
>
> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> news:%23In78HgmFHA.708@TK2MSFTNGP09.phx.gbl...
> > 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.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
> > news:evv9jCgmFHA.2580@TK2MSFTNGP09.phx.gbl...
> >> 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
> >>
> >>
> >> <Skip.Gibson@fairfieldresorts.com> wrote in message
> >> news:1123275025.145014.267900@g49g2000cwa.googlegroups.com...
> >> >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
> >> >
> >>
> >>
> >
> >
>
>