Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: Combine Rows Based On Account Number/product No

  1. #1
    Join Date
    14th August 2006
    Posts
    15

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    14th July 2004
    Posts
    10,542

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,984

    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 at 10:52. Reason: Changed Code

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th December 2004
    Location
    Nanaimo, Vancouver Island, British Columbia, Canada
    Posts
    2,464

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    Cheers,

    dr

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

    http://www.ExcelVBA.joellerabu.com

  5. #5
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,984

    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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    9,423

    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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    14th August 2006
    Posts
    15

    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Replies: 9
    Last Post: September 12th, 2012, 04:17
  2. Replies: 7
    Last Post: May 11th, 2009, 04:53
  3. SUM Account For Added Rows
    By Jeff P in forum EXCEL HELP
    Replies: 4
    Last Post: January 4th, 2007, 04:07
  4. Replies: 2
    Last Post: March 1st, 2006, 04:41
  5. pull info to create account number
    By chrisxs5 in forum EXCEL HELP
    Replies: 3
    Last Post: August 9th, 2005, 05:10

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
  •  
porno