Combine/Merge Rows, Multiple Worksheets

[Excel Hates Me]

I have an issue similar to this one:

http://groups.google.com/group/microsoft.public.excel.programming/bro...

However, I have a few differences:

1. I have multiple worksheets (non-static)
2. Cannot use subtotals or pivottables (non-negotiable and not my
call)
4. Need to combine rows based on match in multiple columns
5. Where the cells are "" in the example above, I have zeroes (though
summing the rows might be a good idea just in case)

I have searched and nothing I have found seems to work. Any help
would
be greatly appreciated. Thanks in advance!

 

[Excel Hates Me]

I also evidently need to learn how to count. I'll figure that one out.
Will still appreciate help with the macro though.

 

[Excel Hates Me]

Sigh. Sorry guys.

http://groups.google.com/group/micr...4eb8cae/e376f663c98ae9fd?lnk=gst&q=merge+rows

Excerpt:
Before Sort/Merge
A1 123
A5 456
A3 789
A1 222
A5 333
A5 111
A3 655

After Sort/Merge
A1 123 222
A3 655 789
A5 333 456 111

 

[Dave Peterson]

I didn't look at the other thread, but if your data is numeric and exactly one
entry per column (per item), you could add column headers (new row 1) and use
Data|Pivottable (in xl2003 menus).

Use "Sum of" for each of the fields.

Saved from a previous post:

Make sure your data has a header row.
Say your data is in A1999
Select your data (a1999)
Data|Pivottable (in xl2003 menus)
Follow the wizard until you get to the step with the Layout button on it.

Click the Layout button
Drag the header for the item to the row field
drag the header for the first number to the row field
drag the header for the 2nd number to the data field
etc...

If the amount says "count of", double click on it and change it to "sum of".

Finish up the wizard.

You'll have a nice summary table.

If you've never used pivottables, here are a few links:

Debra Dalgleish's pictures at Jon Peltier's site:
http://peltiertech.com/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx

 

[Excel Hates Me]

Thanks Dave, much appreciated as always. Is there any way to automate
this with a macro? I don't have the ability to use pivot tables with
this report.

 

[Dave Peterson]

I would start by recording a macro when I did it manually.

 

[Excel Hates Me]

I found this from a solution Dave Peterson provided in 2006 to delete
duplicate rows based on a match in columns 1-4; how could I add code
to have it sum the values in columns Q:AA and AC:AV as well?

Option Explicit
Sub FixDuplicateRows()
Dim RowNdx As Long
Dim iCol As Long
Dim DeleteThisRow As Boolean
Dim rng As Range


For RowNdx = Selection(Selection.Cells.Count).Row To _
Selection(1).Row + 1 Step -1


DeleteThisRow = True
For iCol = 1 To 4 'column A to column D
If Cells(RowNdx, iCol).Value = Cells(RowNdx - 1,
iCol).Value Then
'do nothing, keep looking for a difference
Else
DeleteThisRow = False
Exit For
End If
Next iCol


If DeleteThisRow = True Then
If rng Is Nothing Then
Set rng = Cells(RowNdx, 1)
Else
Set rng = Union(rng, Cells(RowNdx, 1))
End If
End If
Next RowNdx
If Not rng Is Nothing Then
rng.EntireRow.Delete
End If
End Sub