Announcement

Collapse
No announcement yet.

Merge Duplicate Entries For Count Or Sum

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Merge Duplicate Entries For Count Or Sum



    Good day and thanks for looking at my help request

    I often need to merge multiple occurences of data (such as account numbers or names) and to sum or count the values associated with each invividual instance (eg cost or number of entries). Data can often be thousands of rows and varies every time.

    For example:

    Col A Col B
    Ken 5.9
    Ken 12.6
    Brian 5.5
    John 6.4
    Fred 9.9
    Fred 11.6
    Fred 2.0

    I need to be left with either a sum

    Ken 18.5
    Brian 5.5
    John 6.4
    Fred 22.5

    or a count

    Ken 2
    Brian 1
    John 1
    Fred 3

    or ideally a combination of both

    I am sure there is a macro that can do this but am not very familiar with macros or virtual basic in general.

    At the moment I would generally assign each entry a value of 1 in col C then run a sub-total to sum or count, insert cells to align text entries with the subtotals, copy and paste special the data, remove the subtotals, sort and delete the excess data which is as i'm sure you can tell very time consuming.

    Any advice about this would be most welcome. I have attached an example in case my description is not clear

    Please phrase all answers in simple terms as i'm not overly bright!!!

    Thanks in advance
    Attached Files

  • #2
    Re: Merge Duplicate Entries And Count Or Sum

    I added a Results sheet and a macro to your file, assuming you want this to be automatic. Otherwise you could just manually do what the macro is doing: use an advance filter to get the unique names/account numbers, then use CountIf or SumIf functions.

    HTH
    Attached Files

    Comment


    • #3
      Re: Merge Duplicate Entries And Count Or Sum

      Superb stuff,

      many thanks Brian,

      will make my life a lot easier!!!

      Comment


      • #4
        Re: Merge Duplicate Entries And Count Or Sum

        PivotTable.

        Comment


        • #5


          Re: Merge Duplicate Entries For Count Or Sum

          As Dave suggest it Pivot Table is a good deal.
          See attached, it cares about empty cell to.
          Attached Files
          Triumph without peril brings no glory: Just try

          Comment

          Working...
          X