Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine/Merge Rows, Multiple Worksheets
I have an issue similar to this one:
http://groups.google.com/group/micro...rogramming/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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine/Merge Rows, Multiple Worksheets
I also evidently need to learn how to count. I'll figure that one out.
Will still appreciate help with the macro though. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine/Merge Rows, Multiple Worksheets
Sigh. Sorry guys.
http://groups.google.com/group/micro...t&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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine/Merge Rows, Multiple Worksheets
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 A1:D999 Select your data (a1:D999) 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/assistan...lconPT101.aspx On 07/28/2010 16:18, Excel Hates Me wrote: Sigh. Sorry guys. http://groups.google.com/group/micro...t&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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine/Merge Rows, Multiple Worksheets
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine/Merge Rows, Multiple Worksheets
I would start by recording a macro when I did it manually.
On 08/04/2010 21:14, Excel Hates Me wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combine/Merge Rows, Multiple Worksheets
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I combine worksheets w/o enough rows to combine? | Excel Worksheet Functions | |||
How to merge / combine several worksheets into one new worksheet without VBA / Macro? FOR EXPERTS | Excel Worksheet Functions | |||
two worksheets, combine (merge data) | Excel Discussion (Misc queries) | |||
How do I merge or combine 2 excel worksheets by a common collumn? | Excel Worksheet Functions | |||
Combine multiple workbooks into 1 workbook w/ multiple worksheets | Excel Discussion (Misc queries) |