Need Help With Filtering Data In Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-1516523%22%20slang%3D%22en-US%22%3ENeed%20Help%20With%20Filtering%20Data%20In%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1516523%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3C%2FP%3E%3CP%3EI%20will%20appreciate%20if%20somebody%20can%20help%20me%20to%20find%20the%20solution%20to%20this%20problem%20or%20even%20provide%20me%20a%20clue%20so%20I%20can%20go%20head%20do%20the%20rest%20myself.%3C%2FP%3E%3CP%3EI%20attached%20the%20file%20excel%20that%20I%20am%20working%20on.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20guess%20the%20best%20way%20to%20ask%20my%20question%20is%20with%20an%20example.%20Let's%20say%20I%20choose%20%22Blue%22%20%22red%22%20%22yellow%22%20%22white%22%20from%20column%20H%20(I%20already%20created%20a%20checkbox%20for%20this%20column%20and%20link%20each%20cell)%3C%2FP%3E%3CP%3EI%20would%20like%20to%20come%20up%20with%20a%20solution%20that%20each%20time%20I%20select%20different%20Colors%20from%20Column%20%22I%22%2C%20it%20searches%20all%20the%20cells%20in%20column%20%22inventory%201%22%20and%20those%20cells%20that%20contain%20the%20selected%20colors%20(In%20this%20case%20any%20cells%20that%20contain%26nbsp%3B%22blue%22%20%22red%22%20%22yellow%22%20%22white%22)%20be%20extracted%20to%20column%20%22New%201%22.%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20I%20can%20do%20the%20same%20for%20%22Inventory%202%22%2C%20%22Inventory%203%22...%22Inventory%205%22%20and%20results%20for%20each%20will%20be%20shown%20in%20Column%20%22NEW%202%22%20%22NEW3%22%20...%22NEW%205%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1516523%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1516592%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20With%20Filtering%20Data%20In%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1516592%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F714984%22%20target%3D%22_blank%22%3E%40msm66%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20(could%20be%20wrong)%20that%20you're%20getting%20ahead%20of%20yourself%20by%20wanting%20to%20filter.%20Is%20it%20possible%20to%20do%20what%20you're%20asking%3F%20Yes.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20if%20you're%20really%20wanting%20to%20build%20a%20stock%20inventory%2C%20you'd%20be%20better%20off%20creating%20a%20better%20database%20at%20the%20heart%20of%20it%20all.%20There%20are%20templates%20for%20this.%20YouTube%20has%20a%20number%20of%20videos.%20Here's%20a%20link%20to%20one%3A%20%3CA%20href%3D%22https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3Dyap9EMwSK2I%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3Dyap9EMwSK2I%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20worked%20not%20too%20long%20ago%20with%20a%20family%20member%20who%20was%20building%20a%20business%20selling%20tableware.%20They'd%20gotten%20as%20far%20as%20selling%20on-line%20(through%20Shopify%20and%20Wayfair%2C%20among%20others)%2C%20and%20as%20a%20result%20had%20SKUs%20and%20UPC%20codes.%20The%20valuable%20feature%20of%20those%20is%20that%20generally%20there%20will%20be%20a%20different%20SKU%20(same%20with%20UPCs)%20for%20Shirt%2C%20XL%2C%20Pink%2C%20from%20Shirt%2C%20XL%2C%20Blue....But%20there%20will%20also%20be%20a%20pattern%20such%20that%20Pants%2C%20XL%2C%20Pink%20may%20share%20a%20couple%20of%20digits%20with%20the%20SKU%20for%20Shirt%2C%20XL%2C%20Pink.....which%20leads%20me%20to%20suggesting%20that%20a%20good%20database%20design%20is%20more%20likely%20to%20have%20those%20kinds%20of%20characteristics%20as%20separate%20cells%20in%20the%20row%2C%20and%20one%20row%20for%20each%20distinctive%20product.%20Rather%20than%20%22hard-coding%22%20the%20full%20product%20description%20into%20a%20single%20cell%20(as%20you%20have%20them%20in%20your%20example)%2C%20you%20are%20better%20off%20with%20several%20columns%3A%3C%2FP%3E%3CP%3ESKU%26nbsp%3B%26nbsp%3B%26nbsp%3B%20UPC%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ItemType%26nbsp%3B%26nbsp%3B%26nbsp%3B%20ItemSize%26nbsp%3B%26nbsp%3B%20ItemColor%26nbsp%3B%20....etc.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFiltering%2C%20Sorting%2C%20data%20manipulation%20in%20general%2C%20is%20greatly%20facilitated%20by%20doing%20that....%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20that's%20the%20clue%20that%20I'd%20give%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1516608%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20With%20Filtering%20Data%20In%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1516608%22%20slang%3D%22en-US%22%3EThanks%20for%20taking%20your%20time%20and%20responding%20in%20detail.%3CBR%20%2F%3EI%20m%20not%20trying%20to%20track%20my%20inventory.%20I%20am%20working%20on%20a%20project%20so%20I%20just%20made%20an%20example%20to%20be%20able%20to%20apply%20it%20for%20my%20project%20since%20there%20are%20lots%20of%20data%20involved.%3CBR%20%2F%3Ebut%20thanks%20a%20lot%20for%20your%20help.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1516616%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20With%20Filtering%20Data%20In%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1516616%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F714984%22%20target%3D%22_blank%22%3E%40msm66%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWell%2C%20so%20long%20as%20it%20doesn't%20contain%20confidential%20info%2C%20maybe%20you%20could%20post%20the%20actual%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20have%20the%20newest%20edition%20of%20Excel%2C%20there%20are%20functions%20like%20UNIQUE%20and%20FILTER%20and%20SORT%20that%20might%20come%20in%20handy.%20They're%20very%20new....and%20incredibly%20powerful.%20So%20there's%20another%20clue...if%20you'd%20like%20to%20continue%20trying%20to%20resolve%20it%20on%20your%20own.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Hi 

I will appreciate if somebody can help me to find the solution to this problem or even provide me a clue so I can go head do the rest myself.

I attached the file excel that I am working on. 

I guess the best way to ask my question is with an example. Let's say I choose "Blue" "red" "yellow" "white" from column H (I already created a checkbox for this column and link each cell)

I would like to come up with a solution that each time I select different Colors from Column "I", it searches all the cells in column "inventory 1" and those cells that contain the selected colors (In this case any cells that contain "blue" "red" "yellow" "white") be extracted to column "New 1". 

And I can do the same for "Inventory 2", "Inventory 3"..."Inventory 5" and results for each will be shown in Column "NEW 2" "NEW3" ..."NEW 5"

 

3 Replies

@msm66 

 

I think (could be wrong) that you're getting ahead of yourself by wanting to filter. Is it possible to do what you're asking? Yes.

 

But if you're really wanting to build a stock inventory, you'd be better off creating a better database at the heart of it all. There are templates for this. YouTube has a number of videos. Here's a link to one: https://www.youtube.com/watch?v=yap9EMwSK2I

 

I worked not too long ago with a family member who was building a business selling tableware. They'd gotten as far as selling on-line (through Shopify and Wayfair, among others), and as a result had SKUs and UPC codes. The valuable feature of those is that generally there will be a different SKU (same with UPCs) for Shirt, XL, Pink, from Shirt, XL, Blue....But there will also be a pattern such that Pants, XL, Pink may share a couple of digits with the SKU for Shirt, XL, Pink.....which leads me to suggesting that a good database design is more likely to have those kinds of characteristics as separate cells in the row, and one row for each distinctive product. Rather than "hard-coding" the full product description into a single cell (as you have them in your example), you are better off with several columns:

SKU    UPC    ItemType    ItemSize   ItemColor  ....etc.

 

Filtering, Sorting, data manipulation in general, is greatly facilitated by doing that....

 

So that's the clue that I'd give you.

Use this free Excel Inventory Template to track all the stock items for your small business.Access and download the free template here:https://www.beginner-b...
Thanks for taking your time and responding in detail.
I m not trying to track my inventory. I am working on a project so I just made an example to be able to apply it for my project since there are lots of data involved.
but thanks a lot for your help.

@msm66 

 

Well, so long as it doesn't contain confidential info, maybe you could post the actual file.

 

If you have the newest edition of Excel, there are functions like UNIQUE and FILTER and SORT that might come in handy. They're very new....and incredibly powerful. So there's another clue...if you'd like to continue trying to resolve it on your own.