Excelfilter: I need a filter

January 25, 2022 8:08 AM

I want to figure out how to make a list of attributes for a row and then search for any one of those attributes. I am not very expert with Excel and I don't even know what to call what I want, to browse through the list of functions.

I am using Excel as a database (yes, I know it's not fundamentally a database but hey) to keep track of my favorite striped yarn. I have a row for each colorway and I'd like to list the colors included in that colorway in one column. Then, I'd like to be able to filter and pull up, say, all the colorways that contain "red."

I suppose I could use several columns to list all the colors, but some colorways have up to eight colors. There has got to be an easier way, right?

posted by Liesl to Computers & Internet (5 answers total)

Best answer: Type all the colors in a single cell, separated by a space or comma or something similar. Create a header for that column in Row A, and call it "Colors."

Use your mouse to select Row A, and then click the Data tab, then the Filter button.

A small, gray box with a downward facing arrow will appear next to the word Colors. Click it, mouse down to "Text Filters," and then choose "Contains..."

Type in the color you're looking for in the top box, hit "OK," and only the rows with that color listed will remain.
posted by NotMyselfRightNow at 8:27 AM on January 25, 2022 [2 favorites]

I have found vlookup and hlookup to be useful functions when trying to do database things with excel. Create columns for each color, you can just label them r, o, y, etc. They can be very narrow. Put a 1 in for each row that has the color. Use vlookup to find all rows with the colors of interest. You can create a drop down list box to select the colors to look up. Good luck!
posted by Rivvo at 12:32 PM on January 25, 2022

The easiest way to do what you want is to structure the spreadsheet with the colorway name in column A and the color in column B. Additional attributes can be added in columns C and on. The structure is going to look a little sloppy due to the duplication of the name but when it's filtered it'll give you what you need. Just click on the 'Data' tab, then 'Filter' and a little drop-down arrow will appear next to each column name. So if you select "red" from the 'Color' column then you'll see only 2 rows returned: Colorway1 and Colorway3.

ColorwayName  Color  Material
Colorway1    Blue
Colorway1    Yellow
Colorway1    Green
Colorway1    Red
Colorway1         Wool
Colorway2    Yellow
Colorway2    Purple
Colorway2         Cotton
Colorway3    Green
Colorway3    Brown
Colorway3    Red
Colorway3         Acrylic
posted by mezzanayne at 10:01 PM on January 25, 2022 [2 favorites]

mezzanayne, I don't think that solution would work. Poster stated that colorways include multiple colors, some up to 8, and they want to be able to search just for individual colors. The dropdown list will include the full range of color mixes, but not the ability to select a specific color.
posted by NotMyselfRightNow at 2:39 PM on January 26, 2022

Response by poster: Okay, I am back. The filtering solution worked great. Thanks!

Now I would like to limit the colors that can be used, by selecting multiple entries from a predefined dropdown list. I followed the instructions on https://www.youtube.com/watch?v=-GJ0hI_nv8w and was able to create the list of colors I want to pick from, enabled data validation on the cells I want, and added the Visual Basic code. But I cannot select multiple entries like in the video; a new selection just overwrites the old one. What am I doing wrong? The file is saved as macro-enabled.

Here's my code:
Private Sub Worksheet_Change(ByVal Target As Range)
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Not Intersect(Target, Range("G:G")) Is Nothing Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & vbNewLine & Newvalue
Else:
Oldvalue = Replace(Oldvalue, ", " & Newvalue, "")
Oldvalue = Replace(Oldvalue, Newvalue & ", ", "")
Target.Value = Oldvalue
End If
End If
End If
End If
posted by Liesl at 10:37 AM on January 27, 2022