Find a distinct list by matching items that meet a criteria

[pkp]

Kindly help for getting formula. I am attaching a excel file here for finding a distinct list by matching items that meet a criteria. Result should be like as given below with comma.

less than & equat to 60= A,F,G,H,O,P,Q,S,T,W,X,Z

61 to 70 = B,C,I

71 to 80= D, R

Greater than and equal to 81 = E,J,K,L,M,N,Y

Thanks & Regards,
Paresh

 

[Chihiro]

What version of Excel do you have? If using Office 365 or latest update of Excel 2016...

You can do something like below for example...
=LEFT(CONCAT(TRANSPOSE(IF(B2:B25<=60,A2:A25&",",""))),LEN(CONCAT(TRANSPOSE(IF(B2:B25<=60,A2:A25&",",""))))-1)

Confirmed as array (CSE).


Edit: Oh, you won't need Transpose... so.
=LEFT(CONCAT(IF(B2:B25<=60,A2:A25&",","")),LEN(CONCAT(IF(B2:B25<=60,A2:A25&",","")))-1)
Confirmed as array (CSE).

 

[Chihiro]

Slightly shorter and bit more efficient.
=SUBSTITUTE(TRIM(CONCAT(IF(B2:B25<=60,A2:A25&" ",""))), " ",",")

Confirmed as array (CSE)

 

[pkp]

I am using Excel 2010, Above formula is not working

 

[Chihiro]

In that case, easiest way to do it is probably the following.

In a cell enter following formula.
=TRANSPOSE(IF(B2:B25<=60,A2:A25&" ",""))

Without leaving the cell, hit F9. You will get result like below.


Remove {} from resulting value.

Nest result in =SUBSTITUTE(TRIM(CONCATENATE(Result)," ",",")


Hit enter.

 

[AVK]

Dear pkp. plz refer attach file.
plz confirm, As per your req, : if select 60 then result should be as below.
A,F,G,H,O,P,Q,S,T,W,X,Z

 

[pkp]

I am getting result only A
But I want result (with comma) = A,F,G,H,O,P,Q,S,T,W,X,Z
I am attaching here excel file for reference.

 

[AVK]

Dear pkp, plz refer attach file. I have modify some few changes in structure.

 

[bosco_yip]

1] Select criteria from B29 dropdown list

2] Result in cell E29

3] Please refer to the attached file

Regards

 

[Chihiro]

Another method using PowerQuery.

1. Load data from table.
2. Create custom function and name it "BucketFunc"

For details on creating custom Bucket function see thread below.
http://chandoo.org/forum/threads/power-query-without-using-if-function.31541/#post-187511

Function:

(vals) =>
let ValBucket =
    {
        {(x)=>x<61, "60 or less"},
        {(x)=>x<71, "61 to 70"},
        {(x)=>x<81, "71 to 80"},
        {(x)=>true, "81 or more"}
    },
    Result = List.First(List.Select(ValBucket, each _{0}(vals))){1}
in
    Result

3. Add custom column. Formula =BucketFunc([Value]) and name it "Buckets"
4. Remove [Value] column
5. Group rows by Buckets. All Rows.
6. Modify formula for Grouping rows as following.

= Table.Group(#"Removed Columns", {"Buckets"}, {{"Concat", each Text.Combine([Name], ", "), type text}})

Result:


See attached for sample.

PowerQuery is free add-in for Excel 2010.

Edit: Woops, just realized I typed "80 or less" rather than "81 or more". Just edit the custom bucket function and rest will update itself upon refresh. Function updated.