+ Reply to Thread
Results 1 to 10 of 10

merge data and summing values & create report

  1. #1
    Forum Contributor
    Join Date
    06-02-2019
    Location
    libya
    MS-Off Ver
    2010
    Posts
    665

    merge data and summing values & create report

    hello
    I have this sample simple just to understand what I want , my original data begins from COL A to COL H it should create whole data with the headers from COL K: S , so each column contains duplicated strings as in COLS B,C,G should merge as one , as for the values should summing for duplicated data and insert column(TOTAL) to summing the values for any item I put the result from COL K:S and my real data at least 5000 rows and every time is increasing
    thanks in advance
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,589

    Cool Hi, try this


    According to your attachment a VBA demonstration as a beginner starter to paste to the 'collection' worksheet module :

    PHP Code: 
    Sub Demo1()
         
    Dim R&, S$
             
    UsedRange.Columns("K:S").Offset(1).Clear
             Application
    .ScreenUpdating False
        With 
    [A1].CurrentRegion.Rows
            
    .Columns(2).AdvancedFilter 2, , [L1], True
             R 
    = [L1].CurrentRegion.Rows.Count
             Range
    ("M2:M" R).Formula "=VLOOKUP(L2,B$2:H$" & .Count ",2,FALSE)"
             
    Range("Q2:Q" R).Formula Replace([M2].Formula",2"",6")
             
    Range("N2:P" ",R2:R" R).Formula Replace("=SUMIF($B$2:$B$#,$L2,D$2:D$#)""#", .Count)
             
    Range("S2:S" R).Formula "=SUM(N2:R2)"
        
    End With
             Application
    .ScreenUpdating True
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon Add Reputation !

  3. #3
    Forum Contributor
    Join Date
    06-02-2019
    Location
    libya
    MS-Off Ver
    2010
    Posts
    665

    Re: merge data and summing values & create report

    thanks
    it gives error object required
    [HTML][UsedRange.Columns("K:S").Offset(1).Clear/HTML]

  4. #4
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,589

    Arrow Re: merge data and summing values & create report


    As it works as expected on my side

  5. #5
    Forum Contributor
    Join Date
    06-02-2019
    Location
    libya
    MS-Off Ver
    2010
    Posts
    665

    Re: merge data and summing values & create report

    any solution ?

  6. #6
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,589

    Arrow As solution belongs to good enough readers


    so well read post #2 and paste the code where it must be rather than any standard module

  7. #7
    Forum Contributor
    Join Date
    06-02-2019
    Location
    libya
    MS-Off Ver
    2010
    Posts
    665

    Re: merge data and summing values & create report

    thanks
    but I have two questions , why it doesn't work in standard module and this is my mistake and can you mod the code to make create the headers with borders and formatting as in range from COL A to COL H when merge data ?

  8. #8
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    6,589

    Arrow Re: merge data and summing values & create report


    According to your attachment headers are already created but as a beginner starter you can amend the code to add anything

    It can not work in a standard module as it is designed for a worksheet module but for a standard module
    you can add a valid worksheet reference to all statements relative to the worksheet

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,140

    Re: merge data and summing values & create report

    Quote Originally Posted by ABDELFATTA View Post
    each column contains duplicated strings as in COLS B,C,G should merge as one
    Please Login or Register  to view this content.
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    06-02-2019
    Location
    libya
    MS-Off Ver
    2010
    Posts
    665

    Re: merge data and summing values & create report

    @Jindon that's great ! thanks so much for your supporting
    Last edited by ABDELFATTA; 06-14-2021 at 04:23 AM.

+ 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. [SOLVED] merge data and summing values for across multiple sheets
    By Alaa-A in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-31-2021, 07:44 AM
  2. Create a report from a Data Model that shows values only
    By Nerdio in forum Excel Charting & Pivots
    Replies: 11
    Last Post: 04-13-2021, 05:52 AM
  3. [SOLVED] VBA Auto Split and Merge Report Data
    By PandahNZ in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 12-02-2020, 05:23 PM
  4. Code to create a report without zero values
    By Deenah in forum Access Programming / VBA / Macros
    Replies: 9
    Last Post: 11-10-2011, 12:58 PM
  5. Replies: 2
    Last Post: 06-29-2011, 04:35 PM
  6. Create a summing up Report based on Three columns
    By acsishere in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 11-21-2009, 02:29 PM
  7. How to create a report for selected values in the column
    By Shreeshail Ganiger in forum Excel General
    Replies: 0
    Last Post: 07-29-2006, 12:35 PM

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