+ Reply to Thread
Results 1 to 6 of 6

consolidate function vba

  1. #1
    Registered User
    Join Date
    12-20-2016
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    16

    consolidate function vba

    Hi all,

    i have an excel report that spits out duplicate values due to the source system.

    Need help with a consolidate function that will first scan the data, and then consolidate.

    I initially had a loop function that went through the data line by line looking at duplicate policy numbers and consolidating the values if so, but realised it has flaws ( it will consolidate the first 2 lines, and then sum the rest)

    Is there some sort of scan function i can use?

    Data
    Policy
    No value

    1 10.00
    1 10.00
    1 10.00
    1 20.00
    1 20.00
    1 20.00
    2 30.00
    3 40.00
    3 40.00

    goal result
    1 30.00
    2 30.00
    3 40.00

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: consolidate function vba

    Have you considered applying a Pivot Table to the data and putting the No. in the Row Labels area of the field list and the Value in the Values area?
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    12-20-2016
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    16

    Re: consolidate function vba

    hi Richard,

    that's a good idea actually, thanks heaps!

    Still curious of there is a scan function in general though.

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: consolidate function vba

    I don't understand the example. Shouldn't the first value in the result be 90 and the last be 80?
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Registered User
    Join Date
    12-20-2016
    Location
    Australia
    MS-Off Ver
    2010
    Posts
    16

    Re: consolidate function vba

    They are duplicate lines

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: consolidate function vba

    The line 1 30 doesn't appear at all in the upper list.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Function to consolidate results
    By managingcrap in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-16-2015, 02:24 PM
  2. Function to consolidate totals
    By managingcrap in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-11-2014, 08:50 AM
  3. [SOLVED] Function to classify/consolidate list
    By uhtt in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-04-2013, 04:22 AM
  4. Consolidate function
    By NewtoExcel12 in forum Excel General
    Replies: 3
    Last Post: 11-27-2012, 09:45 AM
  5. Replies: 1
    Last Post: 12-08-2010, 10:46 PM
  6. Consolidate Function
    By ExcelNewby in forum Excel General
    Replies: 3
    Last Post: 10-13-2010, 11:13 AM
  7. Is there a function to consolidate multiple records into one recor
    By Richmond in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-10-2005, 03:05 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1