Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / 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,539

    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,921

    Re: Combine Rows Based On Account Number/product No

    Hi

    Try This,

    VB:
    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 09: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,921

    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
    8,411

    Re: Combine Rows Based On Account Number/product No

    try
    VB:
    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, 03:17
  2. Replies: 7
    Last Post: May 11th, 2009, 03:53
  3. SUM Account For Added Rows
    By Jeff P in forum EXCEL HELP
    Replies: 4
    Last Post: January 4th, 2007, 03:07
  4. Replies: 2
    Last Post: March 1st, 2006, 03:41
  5. pull info to create account number
    By chrisxs5 in forum EXCEL HELP
    Replies: 3
    Last Post: August 9th, 2005, 04: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