Creating an excel macro to sum lines with duplicate values

Question

I need a macro to look at the list of data below, provide a number of instances it appears and sum the value of each of them. I know a pivot table or series of forumlas could work but i'm doing this for a coworker and it has to be a 'one click here' kinda deal. The data is as follows.

A        B
Smith   200.00
Dean    100.00
Smith   100.00
Smith    50.00
Wilson   25.00
Dean     25.00
Barry   100.00

The end result would look like this

Smith    3     350.00
Dean     2     125.00
Wilson   1      25.00
Barry    1     100.00

Thanks in advance for any help you can offer!

Answer 1

Are you allowed to mess with the data? You could sort it by column A and then use the subtotal capability, at changes in column A sum column B and show total in column B (which I think are the default settings anyway).

If you may not sort it then I'd think literally just copying it to a new sheet and sorting the copy of it would be the easiest way forward.

[Sorry, I forgot the "count" bit. You can add a new column filled with 1s and subtotal that in the same way.]

Answer 2

Excel provides these things

sumif()
countif()
Data, Filter, Advanced Filter, Unique records only

Gives you all you need, you can record as a macro.

Answer 3

Just create the pivot table in a new sheet, that references that data. To update it tell your co-worker to right click the pivot table and select refresh. No need for a macro or code for this.

Answer 4

Recorded macro to create a pivot table that does what you want.

Sub Macro1()

'Who Cost
'Smith   200
'Dean    100
'Smith   100
'Smith   50
'Wilson  25
'Dean    25
'Barry   100

    Range("A1:B8").Select
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R8C2").CreatePivotTable TableDestination:="[Book1]Sheet1!R1C4", _
        TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Who")
        .Orientation = xlRowField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Cost"), "Sum of Cost", xlSum
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Cost"), "Sum of Cost2", xlSum
    Range("E1").Select
    With ActiveSheet.PivotTables("PivotTable1").DataPivotField
        .Orientation = xlColumnField
        .Position = 1
    End With
    Range("E2").Select
    ActiveSheet.PivotTables("PivotTable1").PivotFields("Sum of Cost").Function = _
        xlCount
End Sub

Answer 5

i have a problem. i cannot speak english very good. i help me please. my project have 1 xmltransformprovider , 1 clientdataset,2 datasource,2 dbgrid,1 button.,1 progressbar,2 listview i read xml file in delphi but it must check followed.

XML FILE:

<?xml version="1.0" encoding="UTF-8" ?> 
<Hr>
<DC>205004</DC>
<RC>205</RC>
<FD>880901</FD>
<TD>880931</TD>
</Hr>
-<X>
-<PH>
<SQ>1</SQ> <PT>100</PT><ND>880902</ND><SN>10357593-2 </SN><RN>17</RN><VD>881217</VD><PC>34776</PC><PP>25000</PP><PS>7500</PS><IS>17500</IS> <RD>880902</RD>
</PH>
<BY>
<MH><MG>01814</MG><MD>1</MD><MR>1</MR><MP>3000</MP><MI>2100</MI><MS>900</MS></MH>
<MH><MG>00595</MG><MD>100</MD><MR>100</MR><MP>10000</MP><MI>7000</MI><MS>3000</MS></MH>
<MH><MG>01395</MG><MD>100</MD><MR>100</MR><MP>12000</MP><MI>8400</MI><MS>3600</MS></MH>
</BY>
</X>

DATASOURCE1 CONTAIN FIELDS: SQ,PT,ND,SN,RN,VD,PC,PP,PS,IS,RD,MH(DATASET) DATASOURCE2 CONTAIN FIELDS: MG,MD,MR,MP,MI,MS

IT IS MUST CHECK . fields value PS + IS = PP in datasource1 and fields value MP = PP in datasource2 if it is true then progressbar complete else fields have error in calculate show in listview.

my problem is how show just error fields.

my code is:

unit dataset;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, xmldom, StdCtrls, ExtCtrls, DBCtrls, Grids, DBGrids, Provider,
  Xmlxform, DB, DBClient, Buttons, ComCtrls;

type
  TForm6 = class(TForm)
    DataSource1: TDataSource;
    ClientDataSet1: TClientDataSet;
    XMLTransformProvider1: TXMLTransformProvider;
    DBGrid1: TDBGrid;
    DBNavigator1: TDBNavigator;
    check: TBitBtn;
    ClientDataSet1SQ: TStringField;
    ClientDataSet1PT: TStringField;
    ClientDataSet1ND: TStringField;
    ClientDataSet1SN: TStringField;
    ClientDataSet1RN: TStringField;
    ClientDataSet1VD: TStringField;
    ClientDataSet1PC: TStringField;
    ClientDataSet1PP: TStringField;
    ClientDataSet1PS: TStringField;
    ClientDataSet1IS: TStringField;
    ClientDataSet1RD: TStringField;
    ClientDataSet1MH: TDataSetField;
    ClientDataSet1JmKol: TAggregateField;
    DBGrid2: TDBGrid;
    DataSource2: TDataSource;
    ProgressBar1: TProgressBar;
    exit: TBitBtn;
    search: TBitBtn;
    searchedit: TEdit;
    ListView1: TListView;
    ListView2: TListView;
    procedure searchClick(Sender: TObject);
    procedure exitClick(Sender: TObject);
    procedure ClientDataSet1AfterScroll(DataSet: TDataSet);
    procedure checkClick(Sender: TObject);
    Function  FieldValue(FieldName  : String):real;
    Function  FieldValuem(FieldNamem  : String):real;
    private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form6: TForm6;

implementation

{$R *.dfm}


procedure TForm6.checkClick(Sender: TObject);
    var
    I:Integer;


    Begin
      ProgressBar1.Position := 0;
      ProgressBar1.Max:=Datasource1.DataSet.RecordCount;
      for i:=0 to  DataSource1.DataSet.RecordCount  do
           Begin
                  if (FieldValue('PS')+ FieldValue('IS') = FieldValue('PP')) then
                  begin
                if   (FieldValuem('MP') = FieldValue('PP'))  then



                       ProgressBar1.Position := i


                 else
                      if  FieldValue('PS')+ FieldValue('IS') <> FieldValue('PP') then
                             begin
                                  ListView1.show;
                                 if FieldValuem('MP') <> FieldValue('PP') then
                   Begin
                         ListView2.show;
                         ListView2.Columns.Items[0]:=DataSource2.DataSet.FieldByName('MG').AsString;
                        ShowMessage()


              End;
     End;

procedure TForm6.exitClick(Sender: TObject);
Begin
Form6.Close;
End;

procedure TForm6.ClientDataSet1AfterScroll(DataSet: TDataSet);
Begin
   DataSource2.DataSet:= ClientDataSet1MH.NestedDataSet;
End;
Function TForm6.FieldValue(FieldName  : String):Real;
Begin
   FieldValue := DataSource1.DataSet.FieldByName(FieldName).AsFloat;
End;
procedure TForm6.searchClick(Sender: TObject);

Begin
DataSource1.DataSet.Locate('SQ',searchedit.Text,[]);
End;

 Function TForm6.FieldValuem(FieldNamem  : String):Real;
Begin
FieldValuem := DataSource2.DataSet.FieldByName(FieldNamem).AsFloat;
End;
End.

delphi file is send.

Answer 6

I CAN'T YOUR ANSWER? WHAT'S THIS? PLEASE I GUIDE EASY.