VBA vind een na laatste waarde van een bepaalde dubbele

[Orcaa]

Heb een sheet met aantal kolommen. Nu komt in 1ste kolom "Comp Id" sommige waarden vaker voor. In 2de kolom staat een waarde die bij CompId uit 1ste kolom hoort en In kolom "aantal" staat het aantal keer dat waarde in kolom "CompId" voor komt. Hier heb ik een Countif voor gebruikt.
Nu wil ik in VBA de waarde van de een na laatste keer dat een CompId voorkomt ophalen.
Dus als ik naar RO74 kijk dan wil ik de een na laatste waarde van RO74 ophalen en dat is 15.

Hoe kan ik dit in VBA doen?

Comp Id Waarde aantal
RO73 10 1
RO74 14 3
RO75 25 1
RO76 32 1
RO74 15 3
RO77 10 1
RO74 13 3
RO78 11 1

 

[snb]

Door een voorbeeldbestand te plaatsen.....

 

[JEC.]

Zonder vba

 

[snb]

Met VBA:

Sub M_snb()
  sn = Filter(Application.Transpose(UsedRange.Columns(1)), "RO74")
  MsgBox sn(UBound(sn) - 1)
End Sub

 

[Orcaa]

Hi snb,

bedankt voor de vba
maar als ik dit probeer, krijg ik error , "object required "

heb de voorbeeld file toegevoegd

 

[Orcaa]

Hallo JVeer,

bedankt voor je oplossing. Het resultaat is ok, maar begrijp niet hoe de formule werkt.
En zou dus deze formule in elke regel moeten zetten om van iedere comp waar er meerdere van zijn de 2de waarde weer te geven

 

[JEC.]

Een vba suggestie

Sub j()
jv = Sheets(1).UsedRange
 For i = 2 To UBound(jv)
   If Cells(i, 1) = "RO74" Then c00 = c00 & "|" & Join(Application.Index(Cells(i, 1).Resize(, 2).Value, 0), "|")
 Next
  a = Split(c00, "|")
  MsgBox a(IIf(UBound(a) < 6, 2, UBound(a) - 2))
End Sub

 

[HSV]

Jv, suggestie.

If jv(i, 1) = "RO74" Then c00 = c00 & "|" & Join(Application.Index(jv, i, array(1, 2)), "|")

Als er toch maar twee zijn, gebruik dan liever niet de trage application.index maar:

c00 = c00 & "|" & jv(i, 1) & "|" & jv(i, 2)

 

[JEC.]

Thanks,
Al denk ik niet dat je het snelheidsverschil zal merken met 2 cellen

 

[HSV]

Uiteraard wel.

Wat dacht je als Ubound(jv) vele malen groter is. :d

 

[cow18]

Sub ZoekG2()
   With Sheets("sheet1")
      .UsedRange.Columns(1).Name = "x"           'gedefinieerde naam van je 1e kolom
      Names.Add "naam", "RO74"                   'je zoekwaarde eveneens in een gedefinieerde naam
      arr = [transpose(if(x=naam,row(x),0))]     'array met rijnummers corresponderend aan zoekwaarde
      G2 = Application.Large(arr, 2)             'de 2e grootste rij
      If G2 > 0 Then MsgBox .Cells(G2, 2) Else MsgBox "foutje"   'gewenste waarde
   End With
End Sub

 

[Orcaa]

Hallo allemaal,

bedankt voor jullie oplossingen. Maar zelf snap ik nog niet de oplossing in VBA. Het resultaat voor de een na laatste waarde klopt, maar moet ook nog de 2 na laatste waarde hebben.

De file met de werkelijke data heeft een paar duizend regels. Er word dagelijks een aantal nieuwe regels aan toegevoegd. Nu staat er in de kolom "waarde" een waarde voor een CompId van die datum. Heb er ook een kolom "Aantal" aan toegevoegd , waarin met een countif wordt geteld hoevaak een CompId voorkomt in de lijst. Wat nu de bedoeling is dat de 1 na laatste en 2 na laatste waarde voor dezelfde CompId er bij wordt gezocht en ernaast wordt gezet. Zodat we kunnen vergelijken of de data overeenkomt of echt afwijkt van eerdere resultaten. Dus als een Compid 8x voorkomt dan wil de 7de en 6de waarde ernaast krijgen. En als een CompId 3x voorkomt wil ik de 2de en 1ste waarde ernaast hebben. En dat geld dan voor alle CompId's van de laatst ingevoerde datum

Heb er een nieuwe voorbeeld file bijgedaan die hopelijk duidelijk is.

Alvast bedankt

 

[Orcaa]

Hallo Cow18,

In jouw code staat ook de regel: Names.Add "naam", "RO74"
Nu is "RO74" maar 1 van de waarden waarvan de 1 na laatste en 2 na laatste waarden bijgezocht moeten worden.
Hoe kan ik ipv "RO74" hier een variabele neerzetten zodat ik met een loop voor alle "Comp Id " de bijbehorende waardes opzoek

 

[cow18]

zo ?

 

[Orcaa]

Hallo Cow18,

dit doet precies wat ik bedoelde. Dank je wel voor deze oplossing