Announcement

Collapse
No announcement yet.

Combine Rows Based On Account Number/product No

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

  • Combine Rows Based On Account Number/product No

    I am curious if there is a way to combine the content of rows based on a change in account number(column A). The tricky part of it is that an account number could be in the file once and another could be in the data twelve times. The list is sorted by account number. I want to take the product code and description and keep placing in the combined row for each account number.

    Attached is a sample of the data.

    Thank in advance for the help.

    TKO
    Attached Files

  • #2
    Re: Combine Rows Based On Account Number/product No

    I'm sorry but that attachment makes no sense, to me anyway.

    You have JOHN DOE in the before data, but no JOHN DOE in the after data.
    Boo!

    Comment


    • #3
      Re: Combine Rows Based On Account Number/product No

      Hi

      Try This,

      Code:
      Sub test()
          Dim i As Integer
          Dim b() As Variant
          Dim val As Variant
      
          With CreateObject("Scripting.Dictionary")
              .CompareMode = vbTextCompare
              For i = Range("B" & Rows.Count).End(xlUp).Row To 2 Step -1
                  val = Cells(i, 2).Value
                  If Not IsEmpty(val) And Not .exists(val) Then
                      .Add val, i
                  Else
                      Range("IV" & .Item(val)).End(xlToLeft).Offset(, 1).Resize(1, 2).Value = _
                      Cells(i, 7).Resize(1, 2).Value
                      .Item(val) = .Item(val) - 1
                      Rows(i).EntireRow.Delete
                  End If
              Next i
          End With
      End Sub
      HTH
      Last edited by Reafidy; May 8th, 2007, 09:52. Reason: Changed Code
      Reafidy

      Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

      Comment


      • #4
        Re: Combine Rows Based On Account Number/product No

        Hi TKO,

        The attached will do exactly as your example. Inserts a summary row, copies data to the Sumary row and formats it light grey (on my machine). It also deletes any previously built summary rows before proceeding to build new ones.

        If I read your explanation and the example correctly this is it...
        Attached Files
        Cheers,

        dr

        "Questions, help and advice for free, small projects by donation. large projects by quote"

        http://www.ExcelVBA.joellerabu.com

        Comment


        • #5
          Re: Combine Rows Based On Account Number/product No

          Will the names of people always be together if they are the same?

          Mary
          Mary
          Joe
          Joe

          Or could it be?

          Mary
          Joe
          Mary
          Joe
          Reafidy

          Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

          Comment


          • #6
            Re: Combine Rows Based On Account Number/product No

            try
            Code:
            Sub test()
            Dim a, i As Long, w(), y
            With Sheets("ifsubs")
                 a = Range("a2",.Range("a" & Rows.Count).End(xlUp)).Resize(,8).Value
                 With CreateObject("Scripting.Dictionary")
                      For i = 1 To UBound(a,1)
                           If Not IsEmpty(a(i,1)) Then
                                If Not .exists(a(i,1)) Then
                                     .add a(i,1), Array(a(i,7), a(i,8), i + 1)
                                Else
                                     w = .item(a(i,1)) : ReDim Preserve w(UBound(w) + 2)
                                     w(UBound(w) - 2) = a(i,7) : w(UBound(w)-1) = a(i,8)
                                     w(UBound(w)) = i + 1
                                     .item(a(i,1)) = w
                                End If
                           End If
                      Next
                      y = .items : Erase a
                 End With
                 For i = 0 To UBound(y)
                      .Cells(y(i)(UBound(y(i)), "g").Resize(,UBound(y(i))).Value = y(i)
                 Next
            End With
            End Sub

            Comment


            • #7
              Re: Combine Rows Based On Account Number/product No

              Thanks for the help. I have yet to try any of your sugestions. As soon as I get a chance I will let you know the results.

              Thanks again

              TKO

              Comment

              Working...
              X