StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Really what you want to do is run a macro or whatever that copies all your data from the three sheets onto a new sheet, then runs a pivot table on the result. Pivot tables handle the unique-ification of your data set and the aggregation of data for multiplicities.</p> <hr> <p>You can use the following VB code (type Alt-F11 in Excel to get to the VBA editor, insert a new module, and paste this code into it). This code assumes your spreadsheet has three sheets named Sheet1, Sheet2, and Sheet3 that contain your data, and that the data is contiguous and starts in cell A1 on each sheet. It also presumes your spreadsheet has a sheet named "Pivot Sheet" which is where the data will all get copied into.</p> <pre><code>Sub CopyDataToPivotSheet() Sheets("Pivot Sheet").Select Range("A1:IV65536").Select Selection.Clear Sheets("Sheet1").Select Range("A1").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("Pivot Sheet").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Sheet2").Select Range("A1").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Application.CutCopyMode = False Selection.Copy Sheets("Pivot Sheet").Select Range("A1").Select Selection.End(xlDown).Offset(1, 0).Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Sheets("Sheet3").Select Range("A1").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Application.CutCopyMode = False Selection.Copy Sheets("Pivot Sheet").Select Selection.End(xlDown).Select Range("A1").Select Selection.End(xlDown).Offset(1, 0).Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Rows("1:1").Select Application.CutCopyMode = False Selection.Insert Shift:=xlDown Range("A1").Select ActiveCell.FormulaR1C1 = "AccountNum" Range("B1").Select ActiveCell.FormulaR1C1 = "Description" Range("C1").Select ActiveCell.FormulaR1C1 = "Total" End Sub </code></pre> <p>This is 95% excel generated code (via Record Macro), but I changed up some stuff to make it more generic. So anyway, you can then assign that Macro to a button in the usual way, or you can assign it to a keyboard shortcut via the Tools => Macro => Macros... Options... dialog.</p> <p>Anyway, that will get your data aggregated onto the Pivot Sheet sheet with appropriate headings.</p> <p>Then you can go to Data => PivotTable and PivotChart Report. Hit Next, select the data on the Pivot Sheet (including the headings!), hit Next, choose Layout.</p> <p>Drag the AccountNumber field (on the right of the wizard) into the area labelled "Row". Drag the Description field to under the Account Number field in the "Row" area. Drag the Total field into the "Data" area, then double click on it in the "Data" area and choose "Sum" so that it aggregates this field. Hit OK and you should get a Pivot Table. You're probably going to want to Hide the sub-totals by right clicking on the sub-total title (i.e. "blah blah Total") and clicking Hide. That result looks basically exactly like what your desired output is. </p> <p>If you wanted to get fancy, you could conceivably automate that last paragraph, but it's probably not worth it.</p> <p>Hope this helps!</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload