Home

Question about matching multiple rows

%3CLINGO-SUB%20id%3D%22lingo-sub-389774%22%20slang%3D%22en-US%22%3EQuestion%20about%20matching%20multiple%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-389774%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%20I'm%20working%20on%20an%20Excel%20dataset%20and%20looking%20for%20a%20proper%20function%20to%20generate%20a%20summarizing%20column.%20Here's%20the%20raw%20data%20format%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20458px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F100341i96963BD3B776ADEF%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture.PNG%22%20title%3D%22Capture.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20get%20a%20summary%20table%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20261px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F100345iBC79CDD4B50619B2%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Capture2.PNG%22%20title%3D%22Capture2.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBasically%2C%20I%20want%20Excel%20to%20find%20all%20rows%20whose%20Category%20is%20%22apple%22%2C%20find%20if%20any%20of%20these%20rows%20has%20'yes'%20for%20the%20columns%20'sold%20in%20supermarket'%20and%20'sold%20in%20grocery'%2C%20and%20label%20the%20category%20accordingly%20in%20the%20summary%20table.%20Moreover%2C%20I%20want%20Excel%20to%20automatically%20finish%20the%20searching%20and%20labeling%20for%20all%20categories(there%20may%20be%20thousands%20of%20categories%2C%20e.g.%20papaya%2C%20grape%2C%20etc.)%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20any%20magical%20combination%20of%20formulas%20do%20the%20task%3F%26nbsp%3B%20I%20appreciate%20any%20advice%20or%20potential%20resource!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-389774%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-389951%22%20slang%3D%22en-US%22%3ERe%3A%20Question%20about%20matching%20multiple%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-389951%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F309330%22%20target%3D%22_blank%22%3E%40Janice_Li%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20second%20solution%20is%20a%20variation%20that%20treats%20the%20category%20as%20a%20relative%20reference%20and%20so%20returns%20results%20category%20by%20category%20rather%20than%20as%20a%20single%20array.%26nbsp%3B%20The%20formulas%20for%20availability%20from%20supermarket%20or%20grocery%20store%20are%20as%20before%20but%20the%20final%20lookup%20differs.%26nbsp%3B%20This%20time%20'Suppliers'%20is%20a%202x2%20array%26nbsp%3B%3C%2FP%3E%3CP%3E%3D%20%7B%22Nowhere%22%2C%22Grocery%22%3B%22Supermarket%22%2C%22Supermarket%20and%20Grocery%22%7D%3C%2FP%3E%3CP%3Eand%20INDEX%20can%20be%20used%20to%20return%20the%20appropriate%20text%20string%3C%2FP%3E%3CP%3E%3D%20INDEX(%20Suppliers%2C%201%2BSupermarket%3F%2C%201%2BGrocery%3F%20)%3C%2FP%3E%3CP%3Esince%20the%20row%20and%20column%20numbers%20are%20now%20scalars.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENo%20doubt%20both%20solution%20look%20somewhat%20strange%20but%20I%20include%20them%20to%20show%20that%20there%20is%20another%20way%20to%20develop%20spreadsheet%20solutions%20other%20than%20the%20traditional%20direct%20referencing%20and%20cloned%20single-cell%20formulas.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-389946%22%20slang%3D%22en-US%22%3ERe%3A%20Question%20about%20matching%20multiple%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-389946%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F309330%22%20target%3D%22_blank%22%3E%40Janice_Li%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETwo%20solutions%2C%20similar%20to%20that%20of%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%20%2C%20but%20based%20on%20the%20use%20of%20tables%20and%20named%20arrays.%3C%2FP%3E%3CP%3EThe%20first%20treats%20the%20output%20list%20of%20products%20as%20an%20array%20and%20uses%20the%20named%20formula%20'Supermarket%3F'%3C%2FP%3E%3CP%3E%3D%20SIGN(%20COUNTIFS(%20Table1%5BCategory%5D%2C%20Category%2C%20Table1%5BSupermarket%5D%2C%20%22yes%22%20)%20)%3C%2FP%3E%3CP%3Eto%20determine%20the%20product%20availability%20from%20supermarkets.%26nbsp%3B%20Similarly%20'Grocery%3F'%3C%2FP%3E%3CP%3E%3D%20SIGN(%20COUNTIFS(%20Table1%5BCategory%5D%2C%20Category%2C%20Table1%5BGrocery%5D%2C%20%22yes%22%20)%20)%3C%2FP%3E%3CP%3EThe%20columns%20of%20%5B0%2C1%5D%20are%20then%20combined%20within%20a%20named%20formula%20'case'%20which%20refers%20to%3C%2FP%3E%3CP%3E%3D%201%20%2B%202*Supermarket%3F%20%2B%20Grocery%3F%3C%2FP%3E%3CP%3EThe%20final%20result%20is%20given%20by%20the%20worksheet%20formula%3C%2FP%3E%3CP%3E%3D%20LOOKUP(%20case%2C%20Suppliers%20)%3C%2FP%3E%3CP%3Ewhere%20'Suppliers'%20is%20the%20array%3C%2FP%3E%3CP%3E%3D%20%7B1%2C%22Nowhere%22%3B2%2C%22Grocery%22%3B3%2C%22Supermarket%22%3B4%2C%22Supermarket%20and%20Grocery%22%7D%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-389829%22%20slang%3D%22en-US%22%3ERe%3A%20Question%20about%20matching%20multiple%20rows%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-389829%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Janice%2C%3C%2FP%3E%3CP%3EApple%20is%20available%20in%20supermarket%20or%20grocery.%20In%20the%20attached%20file%2C%20the%20formula%20in%20G2%2C%20copied%20down%20to%20G5%2C%20is%3A%3C%2FP%3E%3CP%3E%3DCHOOSE(1%2B(COUNTIFS(B%242%3AB%2412%2CF2%2CC%242%3AC%2412%2C%22yes%22)%26gt%3B0)%2B(COUNTIFS(B%242%3AB%2412%2CF2%2CD%242%3AD%2412%2C%22yes%22)%26gt%3B0)%2C%3CBR%20%2F%3E%22nowhere%22%2C%3CBR%20%2F%3EIF(COUNTIFS(B%242%3AB%2412%2CF2%2CC%242%3AC%2412%2C%22yes%22)%2C%22supermarket%22%2C%22grocery%22)%2C%3CBR%20%2F%3E%22supermarket%20or%20grocery%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Janice_Li
Occasional Visitor

Hi everyone, I'm working on an Excel dataset and looking for a proper function to generate a summarizing column. Here's the raw data format:

 

Capture.PNG

 

I want to get a summary table like this:

 

Capture2.PNG

 

Basically, I want Excel to find all rows whose Category is "apple", find if any of these rows has 'yes' for the columns 'sold in supermarket' and 'sold in grocery', and label the category accordingly in the summary table. Moreover, I want Excel to automatically finish the searching and labeling for all categories(there may be thousands of categories, e.g. papaya, grape, etc.) 

 

Can any magical combination of formulas do the task?  I appreciate any advice or potential resource!

 

3 Replies

Hello Janice,

Apple is available in supermarket or grocery. In the attached file, the formula in G2, copied down to G5, is:

=CHOOSE(1+(COUNTIFS(B$2:B$12,F2,C$2:C$12,"yes")>0)+(COUNTIFS(B$2:B$12,F2,D$2:D$12,"yes")>0),
"nowhere",
IF(COUNTIFS(B$2:B$12,F2,C$2:C$12,"yes"),"supermarket","grocery"),
"supermarket or grocery")

@Janice_Li 

Two solutions, similar to that of @Twifoo , but based on the use of tables and named arrays.

The first treats the output list of products as an array and uses the named formula 'Supermarket?'

= SIGN( COUNTIFS( Table1[Category], Category, Table1[Supermarket], "yes" ) )

to determine the product availability from supermarkets.  Similarly 'Grocery?'

= SIGN( COUNTIFS( Table1[Category], Category, Table1[Grocery], "yes" ) )

The columns of [0,1] are then combined within a named formula 'case' which refers to

= 1 + 2*Supermarket? + Grocery?

The final result is given by the worksheet formula

= LOOKUP( case, Suppliers )

where 'Suppliers' is the array

= {1,"Nowhere";2,"Grocery";3,"Supermarket";4,"Supermarket and Grocery"}

@Janice_Li 

The second solution is a variation that treats the category as a relative reference and so returns results category by category rather than as a single array.  The formulas for availability from supermarket or grocery store are as before but the final lookup differs.  This time 'Suppliers' is a 2x2 array 

= {"Nowhere","Grocery";"Supermarket","Supermarket and Grocery"}

and INDEX can be used to return the appropriate text string

= INDEX( Suppliers, 1+Supermarket?, 1+Grocery? )

since the row and column numbers are now scalars.

 

No doubt both solution look somewhat strange but I include them to show that there is another way to develop spreadsheet solutions other than the traditional direct referencing and cloned single-cell formulas.

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies