Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    May 2014
    Posts
    6
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Consolidating 5 sets of results in Pivot table into one grand total

    I have attached an example of my problem where I have tried two approaches and failed in both. I have also made the result I need manually in a separate sheet.

    The problem is that when I export our QC's reports, I get the results of each report on one line with defect type 1, major 1, minor 1, defect 2 etc etc up to 5 (as in the data sheet).

    But the order in which the defects are found is not interesting to me. What I need to know is the total per defect type and total of minors and majors respective - as the manual result I have put in.

    I hope someone can help me with this!!

    Cheers

    Pivot Problem example.xlsx

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,224
    Thanks
    14
    Thanked 341 Times in 334 Posts
    I think the simplest approach would be to have a data table ready to be used by a pivot:
    Capture.PNG

    Then it is simple:
    Capture2.PNG

    Steve

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    elcosmopolitano (2014-05-30)

  4. #3
    New Lounger
    Join Date
    May 2014
    Posts
    6
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Thanks for your input, but I don't have the option to change the format in which I export the data. So that, to my regret, is not a solution.

  5. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,224
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Then it really can not be done with a pivot table. It could be done with a macro. Is a macro acceptable?

    If so, would you like a macro of the output directly, or do you want a macro to rearrange the data and then do a pivot of the rearranged data?

    Steve

  6. The Following User Says Thank You to sdckapr For This Useful Post:

    elcosmopolitano (2014-05-30)

  7. #5
    New Lounger
    Join Date
    May 2014
    Posts
    6
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Thanks again, Steve!

    Macro sounds like it could be a workable solution. If a macro could rearange the informaiton to the format you displayed in your first screenshot then I could easily make the pivot table. I don't have any experience with macros though.

  8. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,224
    Thanks
    14
    Thanked 341 Times in 334 Posts
    This should do the rearranging as desired..

    Steve
    Code:
    Option Explicit
    Sub RearrangeData2()
      Dim wSource As Worksheet
      Dim wDest As Worksheet
      Dim lRowSource As Long
      Dim iCol As Integer
      Dim iCols As Integer
      Dim lRows As Long
      Dim lRowDest As Long
      
      'Change as desired
      Set wSource = Worksheets("Data")
      Application.ScreenUpdating = True
      On Error GoTo ErrHandler
      'Create Dest sheet for rearranged data
      Set wDest = Worksheets.Add
      With wDest
        'Add Headers
        .Cells(1, 1) = wSource.Cells(1, 1)
        .Cells(1, 2) = wSource.Cells(1, 2)
        .Cells(1, 3) = "Defect"
        .Cells(1, 4) = "Type"
        .Cells(1, 5) = "Number"
      End With
      lRowDest = 2
      With wSource
        'Count rows
        lRows = .Cells(.Rows.Count, 1).End(xlUp).Row
        'Get number of columns
        iCols = .Cells(1, .Columns.Count).End(xlToLeft).Column
        For lRowSource = 2 To lRows
          For iCol = 3 To iCols Step 3
            If .Cells(lRowSource, iCol) <> "" Then
              'list if have a defect
              wDest.Cells(lRowDest, 1) = .Cells(lRowSource, 1)
              wDest.Cells(lRowDest, 2) = .Cells(lRowSource, 2)
              wDest.Cells(lRowDest, 3) = .Cells(lRowSource, iCol)
              'determine if major or minor
              If .Cells(lRowSource, iCol + 1) = "" Then
                'Not a major, must be minor
                wDest.Cells(lRowDest, 4) = "Minor"
                wDest.Cells(lRowDest, 5) = .Cells(lRowSource, iCol + 2)
              Else 'it is a major
                wDest.Cells(lRowDest, 4) = "Major"
                wDest.Cells(lRowDest, 5) = .Cells(lRowSource, iCol + 1)
              End If
              'go to next row
              lRowDest = lRowDest + 1
            End If
          Next iCol
        Next lRowSource
      End With
      With wDest
        'format the date column
        .Range("B2:B" & lRowDest).NumberFormat = "m/d/yyyy"
        'autofit columns
        .Cells.EntireColumn.AutoFit
      End With
      'let the user know it is done
      MsgBox "Done"
    ExitHandler:
      Application.ScreenUpdating = True
      Exit Sub
    ErrHandler:
      MsgBox Err.Number & Err.Description
      Resume ExitHandler
    End Sub

  9. The Following User Says Thank You to sdckapr For This Useful Post:

    elcosmopolitano (2014-06-03)

  10. #7
    New Lounger
    Join Date
    May 2014
    Posts
    6
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Hi steve!

    Once again many many thanks for your help!

    You solved the problem! My only problem now, is that the macro code is much more complicated than I had dared imagine. I have no programming experience and therefore I am not able to adjust the code to the real data.

    The real data output comes in the following columns:

    data-jpg.jpg

    So that means that I would need the rearranged data to be in something like this:
    With wDest
    'Add Headers
    .Cells(1, 1) = "PO Number"
    .Cells(1, 2) = "Date"
    .Cells(1, 3) = "Supplier"
    .Cells(1, 4) = "Item Number"
    .Cells(1, 5) = "Defect"
    .Cells(1, 6) = "Type"
    .Cells(1, 7) = "Number"

    The rest I haven't been able to readjust

    I hope you have another few minutes to give the last helping hand.

  11. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,224
    Thanks
    14
    Thanked 341 Times in 334 Posts
    Could you attach a sample set more representative and an example output of what you want so I can help with the modifications and (I hope) teach you more about how the coding works.

    Steve

  12. The Following User Says Thank You to sdckapr For This Useful Post:

    elcosmopolitano (2014-06-04)

  13. #9
    New Lounger
    Join Date
    May 2014
    Posts
    6
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Hi Steve,

    That would be great. I would love to learn more!!

    In the current data material there is 1823 rows and as the reports come in, 30-50 rows are added per week.

    I need to identify the most common defects per supplier so we can target them in our QA program. Therefore it is necessary for me to be able to drill down in the pivot results and see the underlying data e.g. customer and PO number.

    The way I have solved it manually is more simple and primitive way than you solved it in the above macro. I have simply copied the data so it is there 5 times and then placed defects under the same column. In the first example I made you got the same result as I did, so the way you programmed the macro is also fine, as long as I can drill down and see the data with customer, PO number and item no.

    I hope the attached will allow you to show me code that will work on the full data material.

    Thanks


    example of real data.xlsx

  14. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,224
    Thanks
    14
    Thanked 341 Times in 334 Posts
    This replicates your 'Wanted rearranged data' results...

    Steve
    Code:
    Option Explicit
    Sub RearrangeData3()
      Dim wSource As Worksheet
      Dim wDest As Worksheet
      Dim lRowSource As Long
      Dim iCol As Integer
      Dim vColSource As Variant
      Dim iUB As Integer
      Dim iColDefect As Integer
      Dim iGroupStart As Integer
      Dim iGroups As Integer
      Dim iGroup As Integer
      Dim i As Integer
      Dim lRows As Long
      Dim lRowDest As Long
      
      'Change as desired
      Set wSource = Worksheets("Original Data Example")
      iGroupStart = 15  ' Col O the start of the Defects sets of 3
      iGroups = 5  'The number of "Defect Sets"
      vColSource = Array(1, 2, 3, 4, 6) 'Cols from Source to Destination: AtoA, BtoB, CtoC, DtoD, FtoE
      
      Application.ScreenUpdating = True
      On Error GoTo ErrHandler
      'Create Dest sheet for rearranged data
      Set wDest = Worksheets.Add
      iUB = UBound(vColSource)
      iColDefect = iUB + 2
      With wDest
        'Add Headers
        For i = 0 To iUB
          .Cells(1, i + 1) = wSource.Cells(1, vColSource(i))
        Next
        .Cells(1, iColDefect) = "Defect"
        .Cells(1, iColDefect + 1) = "Major"
        .Cells(1, iColDefect + 2) = "Minor"
        'Bold and resize headers
        With .Range("A1", .Cells(1, iColDefect + 2)).Font
          .Bold = True
          .Size = 12
        End With
      End With
      lRowDest = 2
      With wSource
        'Count rows
        lRows = .Cells(.Rows.Count, 1).End(xlUp).Row
        For iGroup = 1 To iGroups
          For lRowSource = 2 To lRows
            iCol = 3 * (iGroup - 1) + iGroupStart
              'list for each defect
              For i = 0 To iUB
                wDest.Cells(lRowDest, i + 1) = .Cells(lRowSource, vColSource(i))
              Next
              For i = 0 To 2
                wDest.Cells(lRowDest, iColDefect + i) = .Cells(lRowSource, iCol + i)
              Next
              'go to next row
              lRowDest = lRowDest + 1
          Next lRowSource
        Next iGroup
      End With
      With wDest
        'format the date column
        .Range("B2:B" & lRowDest).NumberFormat = "m/d/yyyy"
        'autofit columns
        .Cells.EntireColumn.AutoFit
      End With
      'let the user know it is done
      MsgBox "Done"
    ExitHandler:
      Application.ScreenUpdating = True
      Exit Sub
    ErrHandler:
      MsgBox Err.Number & Err.Description
      Resume ExitHandler
    End Sub

  15. The Following User Says Thank You to sdckapr For This Useful Post:

    elcosmopolitano (2014-06-10)

  16. #11
    New Lounger
    Join Date
    May 2014
    Posts
    6
    Thanks
    5
    Thanked 0 Times in 0 Posts
    The macro is now fully operational in the actual data. Thanks a million! You are a life saver Steve!!!

  17. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,224
    Thanks
    14
    Thanked 341 Times in 334 Posts
    You are very welcome. I am glad I could help. I recommend that you step through the macro to get an idea of what it is doing so that you understand it better...

    Steve

Tags for this Thread

Posting Permissions

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