Question about matching multiple rows

Copper Contributor

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.