Search Function?

[Acrid]

Hello everyone, sorry to bother everyone, but I have spent hours searching for this answer and cant seem to find it.

I use an excel spreadsheet to keep track of my DVD collection. It's a basic spreadsheet, and only has 4 columns.

I would like to create a separate sheet that only has a search function on it.

If I enter a keyword, such as "Aladdin", it should display all results with that word, and the information in the other 3 columns.

It should also be able to find and display partial matches as well. For example, If i enter "add" it should locate and display Aladdin, the addams family, etc.

Any help would be greatly appreciated.

Thanks

 

[hiker95]

Acrid,

Welcome to the MrExcel forum.

What version of Excel and Windows are you using?


Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
See reply #2 the BLUE text in the following link:
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045


If you are not able to give us screenshots:

You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.

 

[hiker95]

Acrid,

It would really help if we could see your worksheets.

In any event, here goes.

I am using the Worksheet_Change event in the search worksheet Sheet2.

Sample raw data worksheet Sheet1:

Excel 2007
	A	B	C	D
1	Title A	Title B	Title C	Title D
2	Aladdin's lamp	Aladdin's lamp	Aladdin's lamp	Aladdin's lamp
3	Avatar	Avatar	Avatar	Avatar
4	Outlander	Outlander	Outlander	Outlander
5	The Addam's Family	The Addam's Family	The Addam's Family	The Addam's Family
6
Sheet1

Sample search worksheet, worksheet Sheet2 (the YELLOW cell is where you would type in your search text):

Excel 2007
	A	B	C	D
1
2
3	Title A	Title B	Title C	Title D
4
5
6
7
8
9
Sheet2

If you type into the YELLOW cell, say Aladdin, you would get this automatically:

Excel 2007
	A	B	C	D
1	Aladdin
2
3	Title A	Title B	Title C	Title D
4	Aladdin's lamp	Aladdin's lamp	Aladdin's lamp	Aladdin's lamp
5
6
7
8
9
Sheet2

If you type into the YELLOW cell, say Out, and, press the ENTER key, you would get this automatically:

Excel 2007
	A	B	C	D
1	Out
2
3	Title A	Title B	Title C	Title D
4	Outlander	Outlander	Outlander	Outlander
5
6
7
8
9
Sheet2

If you delete what is in the YELLOW cell you would get this:

Excel 2007
	A	B	C	D
1
2
3	Title A	Title B	Title C	Title D
4
5
6
7
8
9
Sheet2

If you enter into the YELLOW cell, the * character, and press the ENTE key, you would get all the records from Sheet1:

Excel 2007
	A	B	C	D
1	*
2
3	Title A	Title B	Title C	Title D
4	Aladdin's lamp	Aladdin's lamp	Aladdin's lamp	Aladdin's lamp
5	Avatar	Avatar	Avatar	Avatar
6	Outlander	Outlander	Outlander	Outlander
7	The Addam's Family	The Addam's Family	The Addam's Family	The Addam's Family
8	Title A	Title B	Title C	Title D
9
Sheet2

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Select the worksheet in which your code is to run
3. Right click on the sheet tab and choose View Code, to open the Visual Basic Editor
4. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
5. Press the keys ALT + Q to exit the Editor, and return to Excel

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
' hiker95, 01/18/2014, ME751411
Dim lr As Long, nr As Long
Dim c As Range, firstaddress As String
If Range("A1") = "" Then
  lr = Cells(Rows.Count, 1).End(xlUp).Row
  If lr > 3 Then Range("A4:D" & lr).ClearContents
  Exit Sub
End If
If Not Intersect(Target, Range("A1")) Is Nothing Then
  With Application
    .EnableEvents = False
    .ScreenUpdating = False
    lr = Cells(Rows.Count, 1).End(xlUp).Row
    If lr > 3 Then Range("A4:D" & lr).ClearContents
    With Sheets("Sheet1").Columns(1)
      Set c = .Find("*" & Target.Value & "*", LookIn:=xlValues, LookAt:=xlWhole)
      If Not c Is Nothing Then
        firstaddress = c.Address
        Do
          nr = Range("A" & Rows.Count).End(xlUp).Offset(1).Row
          Cells(nr, 1).Resize(, 4).Value = Sheets("Sheet1").Cells(c.Row, 1).Resize(, 4).Value
          Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> firstaddress
      End If
    End With
    .EnableEvents = True
    .ScreenUpdating = True
  End With
End If
End Sub

Before you use the Worksheet_Change event with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then add or delete entries in worksheet Sheet2 in the YELLOW cell, cell A1.

 

[Acrid]

Sorry, I have attached the workbook now, completely forgot. I have 2 tabs at the bottom. I prefer dropbox, because there is no ads. Here is my spreatsheet:

https://www.dropbox.com/s/96rhv96a8wyrnxc/DVD List.xlsx

 

[hiker95]

Acrid,

I am getting an error when trying to down load your workbook.

Excel found unreadable content in 'DVD Kist.xlsx'. Do you want to recover the content of this workbook? If you trust the source of this workbook, click Yes.

 

[hiker95]

Acrid,

Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
See reply #2 the BLUE text in the following link:
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

 

[Acrid]

Not sure why it wont let you download, it works for me even from my phone...... strange. I have attached the 2 screenshots, 1 of the raw data, the other of the search tab. My worksheet after completion will be adding a fourth column, possible a 5th, but I should be able to modify code enough to include those.

 

[azumi]

Follow the link Im fix them for you, hope it works

https://www.dropbox.com/s/mfqy1jm4qtlx08r/DVD List.xlsx

The main formula to search with some keywords :
=IFERROR(IF(OR($B$4="",ROWS(B$13:B13)>$B$10),"",INDEX('DVD''s'!$A$2:$A$10000,AGGREGATE(15,6,(ROW('DVD''s'!$A$2:$A$10000)-ROW('DVD''s'!$A$2)+1)/ISNUMBER(SEARCH($B$4,'DVD''s'!$A$2:$A$10000)),ROWS(B$13:B13)))),"")

More clearer see the file

Regards
Azumi

 

[Rick Rothstein]

I have attached the 2 screenshots, 1 of the raw data, the other of the search tab. My worksheet after completion will be adding a fourth column, possible a 5th, but I should be able to modify code enough to include those.

Why do you need a search function on the "Search" sheet when that functionality is already built into the table you have on the "DVD's" sheet? Since you have AutoFilter turned on, the button displayed in the lower right corner of the "Movie Name" cell (A1) will let you filter the list the way you want. Click that button and then do the following depending on version...

XL2007: Click the "Text Filters" item and select "Contains" from the fly-out submenu, type "add" (without the quotes) and click the OK button.

XL2010: Type "add" (without the quotes) into the field with the word "Search" in it and then click the OK button.

XL2013: I don't have this version, but it probably is identical to XL2010.

To return to the full list, click the button again and put a check mark in the "Select All" and then click the OK button.

 

[hiker95]

Acrid,

You are posting pictures/graphics. This means that if this was a problem where one needed to use your data, anyone trying to help you would have to enter the data manually. That makes no sense.

One last try:

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.
See reply #2 the BLUE text in the following link:
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045


If you are not able to give us screenshots per the above, then:

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.