Data Processing

  • Thread starter Thread starter Hande & Tolga
  • Start date Start date
H

Hande & Tolga

We want achieve some data processing in the code instead
of data processing such as filtering and subtotals. We
achieved this using subtotals and filtering in the code
with a long and tidious process. We think this can be
achieved with a shorter method in the code. The data table
is as follows:

ID Amount Name
-- ------ ----
001 100 fffff
001 50 fffff
002 10 ppppp
002 5 ppppp
002 3 ppppp
003 1 yyyyy


Result To be achived

ID Amount Name
-- ------ ----
001 150 fffff
002 18 ppppp
003 1 yyyyy
 
One possible solution is to use an Excel Pivot Table.

Steps:
1. Remove the row that contains "----" between the
column/field headers and the data.
2. Click anywhere in your data table, go to Excel's Data
menu, and select the "PivotTable..." menu command (may be
different based on your Excel version)
3. In the PivotTable wizard, Step 1 of 3, select
the "Microsoft Excel list or database" and "PivotTable"
options.
4. In Step 2 of 3, make sure your data range is properly
selecting, column headers and data together.
5. In Step 3 of 3, click the "Layout..." button, then
drag the ID field button and Name field button to the ROW
area of the layout area, and drag the Amount field button
to the DATA area.
6. BEFORE clicking OK in the Layout dialog, double-click
the ID button in the ROW area and turn OFF subtotals by
selecting NONE. Do the same for the Name button in the
ROW area. Now click OK on the Layout dialog.
7. Back in Step 3 of 3, select whether you want the
PivotTable to be in a new worksheet or on the existing
worksheet. Click Finish.

You may have to make a few tweaks, but this should give
you a refreshable view of your data, with subtotalling
your data's AMOUNT field by unique ID / NAME
combinations. Whenever you edit your data range, simply
right-click on the PivotTable and select the "Refresh
Data" command to get updated subtotals.
 
Back
Top