Group with Name and use Keywords

%3CLINGO-SUB%20id%3D%22lingo-sub-2668685%22%20slang%3D%22en-US%22%3EGroup%20with%20Name%20and%20use%20Keywords%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2668685%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20have%20grouped%20a%20list%20of%20entities%20with%20name%20%22Metals%22%20and%20using%20the%20name%20for%20comparing%20my%20material%20with%20this%20so%20called%20%22Metals%22%20database%20with%20the%20following%20formula%3C%2FP%3E%3CP%3E%3CSPAN%3E%3DSUMPRODUCT(%24F%249%3A%24F%2421*(%24Y%249%3A%24%3C%2FSPAN%3E%3CSPAN%3EY%2421%3DFALSE)*(ISNUMBER(MATCH(%3C%2FSPAN%3E%3CSPAN%3ED9%3AF21%3BMetals%3B0))%3DTRUE))%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20value(material%20name)%20in%20range%20D9%3AF21%20will%20be%20checked%20in%20the%20%22Metals%22%20name%20list%20and%20the%20sum%20will%20be%20written%20if%20they%20match.%20The%20list%20as%20shown%20below%20for%20instance%20has%20Stahl%2C%20Stahl%208.8%20A2S%2C%20Stahl%20DC01%2B%20C590....%3CBR%20%2F%3EHere%20I%20would%20like%20to%20reduce%20the%20input%20in%20the%20%22Metals%22%20group%20by%20adding%20one%20entity%20Stahl*%20keyword%20and%20include%20all%20the%20names%20like%26nbsp%3BStahl%208.8%20A2S%2C%20Stahl%20DC01%2B%20C590%20etc.%20Is%20it%20possible%20in%20my%20case%3F%20I%20know%20the%20usage%20of%20SEARCH%20but%20in%20other%20context.%20Any%20suggestions%20are%20highly%20appreciated.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image003.jpg%22%20style%3D%22width%3A%20185px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F304617iD5CB377B1B0BE9CF%2Fimage-dimensions%2F185x315%3Fv%3Dv2%22%20width%3D%22185%22%20height%3D%22315%22%20role%3D%22button%22%20title%3D%22image003.jpg%22%20alt%3D%22image003.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2668685%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20on%20mobile%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2668879%22%20slang%3D%22en-US%22%3ERe%3A%20Group%20with%20Name%20and%20use%20Keywords%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2668879%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1133148%22%20target%3D%22_blank%22%3E%40Yash635%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20Excel%20365%20you%20would%20use%20ISNUMBER%2FSEARCH%20to%20determine%20whether%20the%20word%20'Stahl'%20is%20present%20and%20FILTER%20to%20limit%20the%20list%20to%20the%20matched%20items.%26nbsp%3B%20From%20you%20choice%20of%20functions%2C%20I%20assume%20you%20are%20using%20an%20older%20version%20of%20Excel.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2669205%22%20slang%3D%22en-US%22%3ERe%3A%20Group%20with%20Name%20and%20use%20Keywords%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2669205%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20sheet%20with%20list%20of%20parts%2C%20it's%20material%20name%20(some%20grade%2Fkind%20of%20metal%20or%20plastic)%20along%20with%20its%20weight%20(sheet%201).%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%221.png%22%20style%3D%22width%3A%20275px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F304635i34D0DF5D27A3C609%2Fimage-dimensions%2F275x298%3Fv%3Dv2%22%20width%3D%22275%22%20height%3D%22298%22%20role%3D%22button%22%20title%3D%221.png%22%20alt%3D%221.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EA%20List%20is%20defined%20with%20different%20material%20names%20classified%20into%20%22Metals%22%20and%20%22Plastics%22%20Names%20in%20Excel%20(sheet%202).%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222.png%22%20style%3D%22width%3A%20324px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F304636iAD2728C3FC88DA29%2Fimage-dimensions%2F324x331%3Fv%3Dv2%22%20width%3D%22324%22%20height%3D%22331%22%20role%3D%22button%22%20title%3D%222.png%22%20alt%3D%222.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20summing%20the%20weights%20of%20Material%20from%20sheet%201%20based%20on%20the%20comparison%20made%20with%20%22Metals%22%20and%20%22Plastics%22%20list%20in%20sheet%202%20and%20the%20sum%20is%20displayed%20like%20this%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%223.png%22%20style%3D%22width%3A%20292px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F304638iC5DE0FD02EA109DD%2Fimage-dimensions%2F292x59%3Fv%3Dv2%22%20width%3D%22292%22%20height%3D%2259%22%20role%3D%22button%22%20title%3D%223.png%22%20alt%3D%223.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EHere%20sheet%201%20is%20the%20input%20I%20get%20and%20the%20material%20names%20are%20not%20standardized%20here%20and%20aslo%20I%20cannot%20do%20the%20changes%20though%20there%20are%20several%20naming%20conventions%20like%20Stahl%2C%20Stahl%20DC01%2C....%20I%20can%20only%20control%20the%20Names%20list%20%22Metals%22%20and%20%22Plastics%22%20where%20I%20have%20added%20manually%20the%20list%20of%20materials.%20If%20there%20is%20a%20new%20additional%20material%20for%20example%20Stahl_XX_YY%20I%20need%20to%20add%20this%20into%20the%20%22Metals%22%20list%20and%20which%20would%20be%20a%20difficult%20task%20to%20check%20among%20the%20several%20input%20lines.%26nbsp%3B%3CBR%20%2F%3ESo%20I%20want%20to%20say%20in%20my%20Names%20list%2C%20if%20*Stahl*%20keyword%20is%20in%20sheet%201%20Material%20column%20then%20add%20into%20metals.%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3CP%3E%E2%80%83%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,

I have grouped a list of entities with name "Metals" and using the name for comparing my material with this so called "Metals" database with the following formula

=SUMPRODUCT($F$9:$F$21*($Y$9:$Y$21=FALSE)*(ISNUMBER(MATCH(D9:F21;Metals;0))=TRUE))

 

The value(material name) in range D9:F21 will be checked in the "Metals" name list and the sum will be written if they match. The list as shown below for instance has Stahl, Stahl 8.8 A2S, Stahl DC01+ C590....
Here I would like to reduce the input in the "Metals" group by adding one entity Stahl* keyword and include all the names like Stahl 8.8 A2S, Stahl DC01+ C590 etc. Is it possible in my case? I know the usage of SEARCH but in other context. Any suggestions are highly appreciated.

image003.jpg

 

Thanks

2 Replies

@Yash635 

In Excel 365 you would use ISNUMBER/SEARCH to determine whether the word 'Stahl' is present and FILTER to limit the list to the matched items.  From you choice of functions, I assume you are using an older version of Excel.

Hi @Peter Bartholomew ,

 

I have a sheet with list of parts, it's material name (some grade/kind of metal or plastic) along with its weight (sheet 1). 

1.png

A List is defined with different material names classified into "Metals" and "Plastics" Names in Excel (sheet 2).

2.png

 

I am summing the weights of Material from sheet 1 based on the comparison made with "Metals" and "Plastics" list in sheet 2 and the sum is displayed like this

3.png

Here sheet 1 is the input I get and the material names are not standardized here and aslo I cannot do the changes though there are several naming conventions like Stahl, Stahl DC01,.... I can only control the Names list "Metals" and "Plastics" where I have added manually the list of materials. If there is a new additional material for example Stahl_XX_YY I need to add this into the "Metals" list and which would be a difficult task to check among the several input lines. 
So I want to say in my Names list, if *Stahl* keyword is in sheet 1 Material column then add into metals.