Return all cells matching criteria

jerichomurito

New Member
Joined
Nov 6, 2019
Messages
11
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Also, look at the Toolbar
Editing>>Sort and Filter>>Text Filter>>Contains....type in 8GB
 
Upvote 0
sure,

try with 8GB or Optane or any other "word"
after new word in Search cell use Refresh (or Ctrl+Alt+F5)
 
Last edited:
Upvote 0
Probably, but I prefer life easier :)

maybe someone else

Have a nice day
 
Upvote 0
Hi
Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top