Showing in a separate cell a partial match

[laurigardner]

Hi,

I have group of tickets that are in different statuses. For example:
2018-06-21T13:02:19.639 -> Accepted

For me I am interested only in the Accepted and Invoiced data and have it linked to SKU.

I want to have those cells with the accepted or invoiced information, matched by SKU.

I have linked an example excel sheet of the data and a simple example how it's to be displayed.
https://www.dropbox.com/s/4ecj7t0ixoa808s/Example data to search through.xlsx?dl=0

Thanks!

 

[MickG]

Try this for results on sheet 2.

[COLOR="Navy"]Sub[/COLOR] MG11Aug04
    [COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
    [COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
    [COLOR="Navy"]Dim[/COLOR] c           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
    [COLOR="Navy"]Dim[/COLOR] Ac          [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
    [COLOR="Navy"]Dim[/COLOR] Lst         [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range("A2", Range("A1").End(xlDown))
    ReDim ray(1 To Rng.Count + 1, 1 To 3)
        ray(1, 1) = "SKU": ray(1, 2) = "Accepted": ray(1, 3) = "Invoiced"
c = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Lst = Cells(Dn.Row, Columns.Count).End(xlToLeft).Column
    c = c + 1
    [COLOR="Navy"]For[/COLOR] Ac = 1 To Lst - 1
       ray(c, 1) = Dn.Value
       [COLOR="Navy"]If[/COLOR] Right(Dn.Offset(, Ac), 8) = "Accepted" [COLOR="Navy"]Then[/COLOR] ray(c, 2) = Dn.Offset(, Ac).Value
       [COLOR="Navy"]If[/COLOR] Right(Dn.Offset(, Ac), 8) = "Invoiced" [COLOR="Navy"]Then[/COLOR] ray(c, 3) = Dn.Offset(, Ac).Value
    [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(c, 3)
    .Value = ray
    .Borders.Weight = 2
    .Columns.AutoFit
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick