Hide Rows based on search-text in cell

[MiniatureCollector]

Hello! I am a collector of Dungeons and Dragons miniatures and I would like to have an effective way of searching through my indexed collection.

I have a spreadsheet of entries with generated tags (via CONCAT on a separate sheet), but I would like to know if there is a way I could have a Master Search that would hide rows within the sheet based on the text in a specific search cell. My solution right now is a separate sheet which imports rows based on a filter/isnumber/search, but it only imports the values and I'm losing the Comments which I would like to keep. So ideally I would like to have the search/results all in a single sheet.

Can this be done? I've read into Conditional Formatting for hiding Rows based on text using VBA, but that's outside of my coding skill. Any guidance would be appreciated.

 

[MiniatureCollector]

I forgot to add. Ideally if there is no value in the search cell, it should show all contents.

 

[Peter_SSs]

Welcome to the MrExcel board!

would hide rows within the sheet based on the text in a specific search cell.

Hide rows based on that search cell or Show rows based on that search cell?

When something is typed into cell B1, should we be looking in column B for that text? or column H? or something else?

You mention Comments but I can't see any evidence of comments in your picture. Can you expand on that issue?

BTW, I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

 

[MiniatureCollector]

Sorry for any confusion. I've made a simpler version here for ease of making the formula:
- Have all rows visible when the search box (B1) is empty
- When a search term from Column C is (such as "Badger") is entered into B1 it will hide all rows that do not contain that search string, leaving only rows with that value in the tags column.

Please ignore the remark about comments as it was extraneous to this request, and I've removed them from the screenshot and test sheet. As well, the tags generated in the column will be generated from another sheet, but for now they are just text in the cell for cleanliness of using XL2BB.

Miniatures-Search.xlsm
	A	B	C
1	SEARCH:	enter search term here
2	#	Creature Name:	Tags:
3	1	Celestial Dire Badger	Celestial Dire Badger, D&D, Beast , Celestial , Forest
4	2	Large Duergar	Large Duergar, Magic: The Gathering, Buy, Promo, Celestial
5	3	Flame Harrower	Flame Harrower, Sell, Dreamscape, Magic: The Gathering
6	4	Celestial Dire Badger	Celestial Dire Badger, Dreamscape Fiend Giant Shadowfell
7	5	Large Duergar	Large Duergar Brand: Magic: The GatheringGiant
8	6	Flame Harrower	Flame Harrower Brand
9	7	Celestial Dire Badger	Celestial Dire Badger Undead
10	8	Large Duergar	Large Duergar Brand:
11	9	Flame Harrower	Flame Harrower
List

 

[Peter_SSs]

Thanks for the sample data and further information. Try this worksheet change event code with a copy of your workbook.

To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim SchBox As Range
  
  Set SchBox = Range("B1")
  If Not Intersect(Target, SchBox) Is Nothing Then
    If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
    If Len(SchBox.Value) > 0 Then
      ActiveSheet.UsedRange.Offset(1).AutoFilter Field:=3, Criteria1:="*" & SchBox.Value & "*"
    Else
      Application.EnableEvents = False
      SchBox.Value = "enter search term here"
      Application.EnableEvents = True
    End If
  End If
End Sub

 

[MikeVol]

Hi, You can output the filtered data separately if it is not critical for you and without hiding the rows. For example, in cell "E2" enter the following formula:

=IF(B1="", A3:C11, FILTER(A3:C11, ISNUMBER(SEARCH(B1, C3:C11))))

You can change the range yourself, now up to the 11th row, like example.

 

[MiniatureCollector]

@MikeVol
I did this originally (to a second sheet), but the Creature Name column has comments that do not carry over when output into a new list, which is where the desire to simply truncate the list is. But thank you! It was a good suggestion.

@Peter_SSs
This is working PERFECTLY. The only thing I would like to know is how to adjust the Range that the code is indexing? I thought it would be intuitive, but I cannot see how the VBA code you posted references Column C. In playing around I can successfully move the search cell by changing the "B1", and that works well. And I can change the column by changing the Field. "Field:=3" refers to Column C, Field:=3 applies to Column B, but for some reason I cannot change it to any value above 4. Field:=5 for example I would think would reference Column E, spits a runtime error when I search in B1. It's very perplexing. Clearly there is something I'm missing, but I'm eager to learn.

I would love a revised Code for Column H, but a quick explanation about why this is occurring would help me diagnose on my own.

Miniatures-Sorting Test.xlsm
	A	B	C	D	E	F	G	H
1		enter search term here
2	#	Creature Name:	Size	Rarity	Qty	Sales:	Series:	Tags:
3	13	Celestial Dire Badger	Medium	Common	1		DDM Core Set: Deathknell (Mar 25, 2005)	Celestial Dire Badger, D&D, Beast , Celestial , Forest
4
5	21	Large Duergar	Large	Uncommon	2		DDM Core Set: War Drums (Mar 3, 2006)	Flame Harrower, Sell, Dreamscape, Magic: The Gathering
6
7	82	Flame Harrower	Medium	Uncommon	1		Dreamblade - Base Set (Aug 9, 2006)	Large Duergar, Magic: The Gathering, Buy, Promo, Celestial
8
9	5	Golden Protector	Large	Rare	2		DDM Core Set: War of the Dragon Queen (July 7, 2006)	Celestial Dire Badger, Dreamscape Fiend Giant Shadowfell
10
11	21	Phoera	Medium	Uncommon	2		DDM Core Set: Blood War (Nov 6, 2006)	Large Duergar Brand: Magic: The GatheringGiant
12	26	Maug	Medium	Uncommon	1		DDM Core Set: Blood War (Nov 6, 2006)	Flame Harrower Brand
13	48	Earth Element Gargoyle	Medium	Uncommon	3		DDM Core Set: Blood War (Nov 6, 2006)	Celestial Dire Badger, D&D, Beast , Celestial , Forest
14
15	5	Gnaw Demon	Small	Uncommon	2		DDM Core Set: Against the Giants (Jul 12, 2008)	Celestial Dire Badger Undead
16	9	Thunderblast Cyclone	Huge	Uncommon	1		DDM Core Set: Against the Giants (Jul 12, 2008)	Large Duergar Brand:
17	#	Gnaw Demon (promo)	Small	Uncommon	1		DDM Core Set: Against the Giants (Jul 12, 2008)	Flame Harrower
List

 

[MiniatureCollector]

Apologies for the double post, but I have sourced the issue. I had a column filters on, but not across EVERY column, so the Range didn't know what to do. It's working splendidly now. Thank you all!

 

[Peter_SSs]

It's working splendidly now.

Good news! Thanks for letting us know.

Thank you all!

You're welcome. Glad we could help.