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.
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.
HTHVB: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
Last edited by Reafidy; May 8th, 2007 at 10:52. Reason: Changed Code
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...
"Questions, help and advice for free, small projects by donation. large projects by quote"
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
There are currently 1 users browsing this thread. (0 members and 1 guests)