Results 1 to 14 of 14
  1. #1
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,002
    Thanks
    63
    Thanked 2 Times in 2 Posts

    Consolidating Data where the descriptions are common

    Attached please find sample data where I would like to consolidate the values on sheet Br1 & BR2 (Cols F & H) where the text in Col B is the same by department.

    I also need a macro to clear the ---- , in all columns on all sheets

    Your assistance in this regard is most appreciated
    Attached Files Attached Files

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,205
    Thanks
    14
    Thanked 331 Times in 324 Posts
    Could you explain what you mean by "consolidate" and perhaps show us what you want the results to be and walk us through the logic.

    you don't need a macro to do that, find and replace should work.

    Steve

  4. #3
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,002
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Hi Steve

    What I mean by consolidate is to combine the values where the descriptions are the same in a given Range for Eg Dept 1 ranges from 1 to 125

    For eg I would like to add the values for sheets Br1 & Br2 where the decriptions are the same. I have 5 different departments where some of the descriptions can be the same. I only want to add the values in Col C "Consolidated" pertaining to Sheets Br1 & Br2 where the descriptions are the same for the department (can be a range). The Departments are in Col B for eg Dept1, Dept2 etc

    I have attached a new sample file showing the results, by linking the values.

    I need a more accurate formulas to add the values based on the descriptions in Col B on sheets Br1 & Br2
    Attached Files Attached Files
    Last edited by HowardC; 2014-07-26 at 08:56.

  5. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,205
    Thanks
    14
    Thanked 331 Times in 324 Posts
    I am confused. Here are a few of teh things that confuse me:
    1) How are the values in Consolidated col B determined?
    2) When the value is Blank in rows 15, 25, and 26 how would the code know to get the items from BR2 row 24,34, and 35 and which rows to look for in BR1?
    3) Items: UNITS SOLD, GROSS SALES, NET SALES, COST OF SALES, and GROSS (PROFIT) are all duplicates. How is the code to know which row to use in BR2 and BR1?
    4) How would the code know to ignore the values for the item: VEHICLE 2ND GROSS, FINANCE BANK REBATES/DIC, INSURANCE COMMS RECEIVED, NV WARR/EXT WARR/INS GP, NV MAINT/SVC PLAN SOLD GP, SHIELD PRODUCTS SOLD GP, TRACKER / OTHER VAPS GP, F&I GROSS PROFIT?

    For the most part you don't need code a formula in Consolidated C5:
    =IF(B5="","",IFERROR(VLOOKUP(B5,'Br1'!B:H,5,0),0)+ IFERROR(VLOOKUP(B5,'BR2'!B:H,5,0),0))

    and in Consolidated D5 of:
    =IF(B5="","",IFERROR(VLOOKUP(B5,'Br1'!B:H,7,0),0)+ IFERROR(VLOOKUP(B5,'BR2'!B:H,7,0),0))

    Copied down the column is correct over 60% of the time. I could have an "IGNORE list" and it would go up to 77%. If I could ignore the blank items it would go to 84%. If there was some way to differentiate the duplicate items it would go to 100%...

    [Whether formula or macro, those oddities still need to be resolved...]

    Steve

  6. #5
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,002
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Thanks for the reply, Steve

    Looking at your points 1 - 4, I now see that this is going to cause a huge problem

    There may be a way to resolve this. If there is a way to extract unique descriptions from all the sheets by department to Col B on Sheet "consolidated" , then one can use your formulas and ignore blank rows

    Howard

  7. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,205
    Thanks
    14
    Thanked 331 Times in 324 Posts
    But where is the dept information located? How can one tell from the info in Consolidated Col B what dept it is and where is the dept listed in BR1 and BR2?

    That information is needed for a formulas as well as a macro solution...

    I don't think we can help with the questions 1-4 I posed or even the questions i pose here...

    Steve

  8. #7
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,002
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Thanks for the reply Steve. I will import Trial balances where the account numbers are exactly the same and then set up a standard management report

  9. #8
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,107
    Thanks
    39
    Thanked 197 Times in 184 Posts
    Howard,

    Here is some code that will find matching description on sheets BR1 and BR2 then copy the added values in the columns as you describe to the matching description on the Consolidation sheet.

    I set it up by creating named ranges in the code for the Department 1. Repeat the indicated lines of code for additional departments added.

    Be careful because you have multiple same descriptions for different units on the same sheet (eg. GROSS SALES, GROSS (PROFIT). Place a button on the Consolidated sheet then run the Consolidate macro from there.

    HTH,
    aud

    Code:
    Public Sub Consolidate()
    '--------------------------------
    'DECLARE AND SET VARIABLES
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim Consol As Worksheet
    Dim SourceCell As Range
    Dim RangeCell As Range
    Dim DestinationCell As Range
    Set ws1 = Worksheets("BR1")
    Set ws2 = Worksheets("BR2")
    Set Consol = Worksheets("Consolidated")
    '-------------------------------
    'SET NAMED RANGES
        'CHANGE THE ROWS TO MATCH EACH DEPARTMENT
        'ADD NEW NAMED RANGES AS YOU ADD ADDITIONAL DEPARTMENTS
    ActiveWorkbook.Names.Add Name:="BR1Dept1", RefersToR1C1:="=BR1!R12C2:R118C2"
    ActiveWorkbook.Names.Add Name:="BR2Dept1", RefersToR1C1:="=BR2!R11C2:R60C2"
    ActiveWorkbook.Names.Add Name:="ConsolDept1", RefersToR1C1:="=BR2!R11C2:R48C2"
    '-------------------------------
    'FIND MATCHING DESCRIPTIONS IN DEPARTMENT 1 AND COPY TO CONSOLIDATION SHEET
        'REPEAT IF NEW DAPARTMENTS ARE ADDED USING NEW NAMED RANGES
    For Each SourceCell In Range("BR2Dept1")
        If SourceCell = "" Or SourceCell = "-" Then GoTo Skip
        For Each RangeCell In Range("BR1Dept1")
            If SourceCell = RangeCell Then
                Set DestinationCell = Consol.Cells.Find(SourceCell)
                If Not DestinationCell Is Nothing Then
                    DestinationCell.Offset(0, 1) = SourceCell.Offset(0, 4) + RangeCell.Offset(0, 4)
                    DestinationCell.Offset(0, 2) = SourceCell.Offset(0, 6) + RangeCell.Offset(0, 6)
                End If
            End If
        Next RangeCell
    Skip:
    Next SourceCell
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2014-07-27 at 20:29.

  10. #9
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,002
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Thanks for the help Maude

    Where the decriptions are unique to a particular sheet for eg BR2 the values must also be copied to the consolidated sheet


    For example Units Sold
    Gross Sales


    This is quite a complex problem. I have even looked at using Vlookups for a particular range, but this is time consuming, but can be done


    Howard

  11. #10
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,107
    Thanks
    39
    Thanked 197 Times in 184 Posts
    Howard,

    With multiple descriptions on the same sheet, there must be a way to distinguish them from each other. For example, in BR2, if there was a related index, part, or job number in column A adjacent to the descriptions that grouped them together and could be used to associate them to a grouping on the consolidation sheet, then it could be done. As it stands, how can you map two GROUP SALES descriptions on one sheet to the right GROUP SALES on another sheet with no reference? Vlookup offers no intuitive powers

    I would say the task is not difficult at all with some organization. It is the way you have your sheets set up that makes it complex. Additional sub named ranges can be created for related descriptions but there is no clue as to what they are.

  12. #11
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,002
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Thanks Maud.

    Where the following appears under Dept1 for the first time , would it be possible to write code to insert the description "RETAIL SALES/EXPORT-SUMMARY" next to each of these in Col A. As can be seen this description will always appear in B3 . The descriptions below are in B5:B16




    UNITS SOLD

    GROSS SALES
    DISCOUNTS
    FACTORY INCENTIVES PAID

    NET SALES

    COST OF SALES
    OVERALLOWANCE/GIVEAWAYS

    GROSS (PROFIT)

    The same will apply to the items below wholesale sales summary where the descriptions are the same "WHOLESALE SALES-SUMMARY" B19 to B27

    The same logic will apply to dept2

    Howard

  13. #12
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,107
    Thanks
    39
    Thanked 197 Times in 184 Posts
    The groupings on your sheets are now apparent. This is very doable. Does the consolidation sheet start out totally blank and both the descriptions and the values are written to it? If so, is there any specific order of the descriptions between BR1 and BR2 that must appear on the Consolidated sheet?

    OR

    Are the descriptions already on the Consolidation sheet and just the values need to be carried over?

  14. #13
    5 Star Lounger
    Join Date
    Feb 2008
    Posts
    1,002
    Thanks
    63
    Thanked 2 Times in 2 Posts
    Thanks for the reply.

    The descriptions are already on the Consolidation sheet and just the values need to be carried over

  15. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,205
    Thanks
    14
    Thanked 331 Times in 324 Posts
    But as both Maudibe and I have mentioned, the descriptions are not unique. You seem to want them carried over separately and NOT consolidated, but your example gives no indication of what makes them different. As far as I am concerned, The questions I raised earlier are all waiting for a response to understand what is going on....

    Steve

Posting Permissions

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