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

 

[jerichomurito]

Probably, but I prefer life easier

maybe someone else

Have a nice day

Thanks! Still puzzled but I now have a little idea..

 

[jerichomurito]

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

This is definitely out of my league! Sorry if im so noob, but how do I use this?

 

[Michael M]

Did you try the filter method

 

[mohadin]

This is definitely out of my league! Sorry if im so noob, but how do I use this?

1. Open your workbook in Excel.
2. Press Alt + F11 to open Visual Basic Editor (VBE).
3. Right-click on your workbook name in the "Project-VBAProject" pane (at the top left corner of the editor window) and select Insert -> Module from the context menu.
4. Copy the VBA code (from a web-page etc.) and paste it to the right pane of the VBA editor ("Module1" window).

When you want to run the VBA code that you added as described inthe section above: press Alt+F8 toopen the "Macro" dialog.<o></o>
Thenselect the wanted macro from the "Macro Name" list and click the"Run" button.<o></o>

 

[Peter_SSs]

I think that you need to tell us more about exactly how we would determine the rows to return.
I say that because as it stands, I think all 3 suggestions so far return incorrect rows if searching for, say, 8GB

To help clarify, what does this row tell us about the memory available?

HP ProBook 450 G6 6FE36UT#ABALaptop/i5-8265U/16GB/512GB M.2 PCIe SSD + 1TB 2.5 HDD/15.6" Flat HD (1366x768)/Intel UHD 620/Windows 10 Pro

 

[jerichomurito]

1. Open your workbook in Excel.
2. Press Alt + F11 to open Visual Basic Editor (VBE).
3. Right-click on your workbook name in the "Project-VBAProject" pane (at the top left corner of the editor window) and select Insert -> Module from the context menu.
4. Copy the VBA code (from a web-page etc.) and paste it to the right pane of the VBA editor ("Module1" window).

When you want to run the VBA code that you added as described inthe section above: press Alt+F8 toopen the "Macro" dialog.<o></o>
Thenselect the wanted macro from the "Macro Name" list and click the"Run" button.<o></o>

Thanks! I will be trying this today..

 

[jerichomurito]

What exactly I would want is to return a value of all product or laptop description that has the same RAM memory for this instance i used 8GB as an example..
So all laptops with 8GB RAM memory in the spreadsheet would be displayed..

 

[jerichomurito]

it says run error.. type mismatch

 

[jerichomurito]

Did you try the filter method

I haven't yet..

 

[mohadin]

it says run error.. type mismatch

Well, I see
In the code provided the data supposed to be in column A
If Not, You will get the error you mentioned