Return all cells matching criteria

[jerichomurito]

Im totally new to vlookup and excel function..
Supposedly I have a spreadsheet like the one below, is it possible to check a certain string and return the value of all strings in that cell?
Let's say for example I want to search for all units/laptops that has "8GB" RAM and would return the display like these:

HP ProBook 450 G6 6FE36UT#ABA Laptop/i5-8265U/8GB/4TB 2.5 SATA SSD/15.6" Flat HD (1366x768)/Intel UHD 620/Windows 10 Pro
HP ProBook 450 G6 6FE36UT#ABA Laptop/i5-8265U/8GB/1TB 2.5 SATA SSD/15.6" Flat HD (1366x768)/Intel UHD 620/Windows 10 Pro
HP ProBook 450 G6 6FE36UT#ABA Laptop/i5-8265U/8GB/2TB 2.5 SATA SSD/15.6" Flat HD (1366x768)/Intel UHD 620/Windows 10 Pro
etc..

Please teach me how to do this..

https://docs.google.com/spreadsheets/d/1BHdyp4bx176gC4VfMb_B_2-zK3xPFBp9BvUhAXup5B4/edit?usp=sharing

 

[sandy666]

what is your Excel version?

 

[jerichomurito]

what is your Excel version?

MS Office Professional Plus 2016

 

[sandy666]

so try Power Query aka Get&Transform

example is here

 

[jerichomurito]

so try Power Query aka Get&Transform

example is here

I will definitely try that.. Thanks for the head start.

 

[Michael M]

Also, look at the Toolbar
Editing>>Sort and Filter>>Text Filter>>Contains....type in 8GB

 

[sandy666]

sure,

try with 8GB or Optane or any other "word"
after new word in Search cell use Refresh (or Ctrl+Alt+F5)

 

[jerichomurito]

is this possible using vlookup?

 

[sandy666]

Probably, but I prefer life easier

maybe someone else

Have a nice day

 

[mohadin]

Hi

Sub test()
    Dim a As Variant
    Dim i, k, m
    m = InputBox("Type memory you're looking for")
    a = Application.Transpose(Cells(1, 1).Resize(Cells(Rows.Count, 1).End(xlUp).Row))
    [b:b].ClearContents
    With CreateObject("VBScript.RegExp")
        .Global = True
        .Pattern = m & "[GB]+"
        For i = 1 To UBound(a)
            If Not .test(a(i)) Then
            Else
                Cells(k + 1, 2) = a(i)
                k = k + 1
            End If
        Next
    End With
End Sub