Any order searches in Excel

[khanram]

Hi,


I just want to know if it is possible to search data in Excel in any order.


For instance, in cell A1, I have the product "10 mm Clear Saudia". If I hit Control + F, I have to search for "Saudia" or "Clear" or "Clear Saudia" to find the product.

I was wondering if there is a way if I type "Saudia Clear" and it would still find the product for me. This is because most of the time I do not remember the exact product names but do remember certain key words related to the product. Most of the time I can't find the product details because I can't type in the details in the correct order.

Is there anyway to get around this?


Any help would be greatly appreciated.


Thanks,
Ram

 

[marious]

You can use the advanced filter Filter by using advanced criteria - Excel

 

[khanram]

Hi there,

But I mainly use this to check for prices of products and I think this is a little time consuming to do it every now and then. Is there any other way that anyone knows about?

 

[Aladin Akyurek]

Does this:

=LOOKUP(9.99999999999999E+307,SEARCH(A1,Range),Range)

help?

 

[L. Howard]

Maybe try this.

Copy into a standard module (Insert > Module in the VB Editor)

Assign to a button.

Run code and put what you remember in the input box then OK. Gives you the cell address in a message box when found.

This is looking on a sheet named "Sheet 2" Adjust in the code to match your sheet name.

If you want to look at a couple of sheets or more, that can be done also.

Should find "Yellow" if you enter "llow" or "Calf Rope" if you enter "alf Ro".

Finds the first occurrence.

Howard

Option Explicit

Sub Find_Stuff()
  Dim myStuff As String '
  Dim Rng As Range '
  Dim i As String
  
myStuff = Application.InputBox("Enter some stuff.", _
    "Stuff I Forgot Finder", , , , , , 2)

   If Trim(myStuff) <> "" Then
          Dim lngLstRow As Long

          lngLstRow = ActiveSheet.UsedRange.Rows.Count

     With Sheets("Sheet2").Range("A1:z" & lngLstRow) '// edit sheet name if needed
     
       Set Rng = .Find(What:=myStuff, _
          After:=.Cells(.Cells.Count), _
          LookIn:=xlValues, _
          LookAt:=xlPart, _
          SearchOrder:=xlByRows, _
          SearchDirection:=xlNext, _
          MatchCase:=False)
   
       If Not Rng Is Nothing Then
              MsgBox "Some stuff is in cell " & Rng.Address
         Else
            MsgBox "Stuff not found"
       End If
       
     End With
   End If

End Sub

 

[khanram]

Does this:

=LOOKUP(9.99999999999999E+307,SEARCH(A1,Range),Range)

Where do I put this formula? I only have two columns in excel. One is with the product description and other one is with their cost price. I have 14,074 rows in total including the header.

Thanks for your help

 

[Aladin Akyurek]

Does this:

=LOOKUP(9.99999999999999E+307,SEARCH(A1,Range),Range)

Where do I put this formula? I only have two columns in excel. One is with the product description and other one is with their cost price. I have 14,074 rows in total including the header.

Thanks for your help

What are the range housing the descritions and the range housing the cost prices?

 

[khanram]

Maybe try this.

Copy into a standard module (Insert > Module in the VB Editor)

Assign to a button.

Run code and put what you remember in the input box then OK. Gives you the cell address in a message box when found.

This is looking on a sheet named "Sheet 2" Adjust in the code to match your sheet name.

If you want to look at a couple of sheets or more, that can be done also.

Should find "Yellow" if you enter "llow" or "Calf Rope" if you enter "alf Ro".

Finds the first occurrence.

Hi Howard,

The code is great but it only tells me that some stuff is there in specific cell and that too, only the first occurrence. What I am looking at is filtering the data which contain the keywords that I type and then find the price of the product which is in the next column.

Would be grateful if you can help me with this code.

Thanks for you time.

 

[khanram]

What are the range housing the descritions and the range housing the cost prices?

The description is in the range A1:A14074 and price is in the range B1:B14074 (both headers included).

Thanks

 

[Aladin Akyurek]

The description is in the range A1:A14074 and price is in the range B1:B14074 (both headers included).

Thanks

D2 houses a partial description.

E2:

Either...

=LOOKUP(9.99999999999999E+307,SEARCH(D2,$A$2:$A$14074),$B$2:$B$14074)

Or...

=VLOOKUP(D2,$A$2:$B$14074,2,0)

Both would return the cost price associated with the partial product description given in D2.

Is this what you are after?