How can this be done??

[Johny1900]

Hi,

I have a table with articles which have been bought during this year. The structure is:

Number_art, description,discount

Now in this table the article can be there in several lines. What I want to achieve is to get as a result

Number_art, description,discount1,discount2

Where discount1 is the latest discount and discount2 is th second latest discount.
Example in data

Table org.

1234 VHS 10 
1234 VHS  5  
1234 VHS 11 
2222 VCR   5 
2223 BETA 10
2223 BETA  5

the result should be

1234 VHS 10 5 
2222 VCR   5  
2223 BETA 10 5

getting the data is no problem, but just getting the Discount of the second row and adding it there is getting me a headache..... any help is very welcome!!!

thx

Johny

 

[jindon]

try

Sub test()
Dim a, b(), w(), i As Long, z As String, n As Long
With Range("a1").CurrentRegion.Resize(,3)
    a = .Value
    ReDim b(1 To UBound(a,1), 1 To Columns.Count)
    With CreateObject("Scripting.Dictionary")
        .CompareMode = vbTextcompare
        z = a(i,1) & ";" & a(i,2)
        For i = 2 To UBound(a,1)
            If Not .exists(z) Then
                n = n + 1 : b(n,1) = a(i,1) : b(n,2) = a(i,2)
                .add z, Array(n, 2)
            Else
            w = .item(z) : w(1) = w(1) + 1
            b(w(0), w(1)) = a(i,3)
            maxCol = Application.Max(maxCol, w(1))
            .item(z) = w
        Next
    End With
    .Offset(, .Columns.Count + 1).Resize(n, maxCol).Value = b
End With
End Sub

 

[Stephen_IV]

Jindon,

I tried you code this morning because I needed something simular and I get the following errors.

Compile Error: Next without For

probably because an end if is missing,

Then I get

subscript out of range z = a(i, 1) & ";" & a(i, 2)

 

[jindon]

Jindon,

I tried you code this morning because I needed something simular and I get the following errors.

Compile Error: Next without For

probably because an end if is missing,

Then I get

subscript out of range z = a(i, 1) & ";" & a(i, 2)

Stephen,
1) Yes, Else should be End If
2) change

        z = a(i,1) & ";" & a(i,2)
        For i = 2 To UBound(a,1)

to

        For i = 2 To UBound(a,1)
            z = a(i,1) & ";" & a(i,2)

 

[Stephen_IV]

That was it Jindon,

Thanks again, your code was very usefull!

 

[Johny1900]

Thx Jindon,

The code you gave was great. i have just used it. Many thx again