To display the unique text based on some criteria in other column

[deepak1310]

What should be the excel formula to display the name of cities in a single cell where both age group of people lives.

1st Age group=>30

2nd Age group=<30

Employee No	Age	Experience	Location
Employee 1	28	12	Mumbai
Employee 2	43	20	Sydney
Employee 3	28	17	Mumbai
Employee 4	34	10	Sydney
Employee 5	28	10	Sydney
Employee 6	34	12	Bangalore
Employee 7	32	16	Bangalore
Employee 8	43	12	Mumbai
Employee 9	44	22	Mumbai
Employee 10	43	21	Kolkata
Employee 11	28	12	Mumbai
Employee 12	34	13	Singapore, Singapore
Employee 13	44	16	Melbourne
Employee 14	38	14	Dubai
Employee 15	45	21	New Delhi, India
Employee 16	29	16	Chennai, India
Employee 17	37	16	San Fransico
Employee 18	29	13	Jakarta
Employee 19	41	13	San Fransico
Employee 20	37	15	Jakarta

[#2]

@deepak1310 

Also included is two sample file with some options.

Sort data in a range or table

Sorting data is an integral part of data analysis. You might want to arrange a list of names in alphabetical order, compile a list of product inventory levels from highest to lowest, or order rows by colors or icons. Sorting data helps you quickly visualize and understand your data better, organize and find the data that you want, and ultimately make more effective decisions.

You can sort data by text (A to Z or Z to A), numbers (smallest to largest or largest to smallest), and dates and times (oldest to newest and newest to oldest) in one or more columns. You can also sort by a custom list you create (such as Large, Medium, and Small) or by format, including cell color, font color, or icon set.

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

[#3]

@deepak1310 

Do you have Microsoft 365?

[#4]

Yes

[#5]

I want to display the names of cities in one cell/many cell where both age group people lives(greater than 30 and less than 30).In the table 3 cities fulfill this criteria I.e Jakarta,Mumbai and Sydney.I want to display the names of these cities in one/many cell using excel formula and functions

[#6]

@deepak1310 

Try this:

=TEXTJOIN("; ", TRUE, UNIQUE(FILTER(D2:D21, COUNTIFS(D2:D21, D2:D21, B2:B21, ">=30")*COUNTIFS(D2:D21, D2:D21, B2:B21, "<30"), "")))