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

%3CLINGO-SUB%20id%3D%22lingo-sub-2536268%22%20slang%3D%22en-US%22%3ETo%20display%20the%20unique%20text%20based%20on%20some%20criteria%20in%20other%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2536268%22%20slang%3D%22en-US%22%3E%3CP%3EWhat%20should%20be%20the%20excel%20formula%20to%20display%20the%20name%20of%20cities%20in%20a%20single%20cell%20where%20both%20age%20group%20of%20people%20lives.%3C%2FP%3E%3CP%3E1st%20Age%20group%3D%26gt%3B30%3C%2FP%3E%3CP%3E2nd%20Age%20group%3D%26lt%3B30%3C%2FP%3E%3CTABLE%20width%3D%22332%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2294%22%3EEmployee%20No%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EAge%3C%2FTD%3E%3CTD%20width%3D%2294%22%3EExperience%3C%2FTD%3E%3CTD%20width%3D%2280%22%3ELocation%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEmployee%201%3C%2FTD%3E%3CTD%3E28%3C%2FTD%3E%3CTD%3E12%3C%2FTD%3E%3CTD%3EMumbai%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEmployee%202%3C%2FTD%3E%3CTD%3E43%3C%2FTD%3E%3CTD%3E20%3C%2FTD%3E%3CTD%3ESydney%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEmployee%203%3C%2FTD%3E%3CTD%3E28%3C%2FTD%3E%3CTD%3E17%3C%2FTD%3E%3CTD%3EMumbai%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEmployee%204%3C%2FTD%3E%3CTD%3E34%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3ESydney%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEmployee%205%3C%2FTD%3E%3CTD%3E28%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3ESydney%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEmployee%206%3C%2FTD%3E%3CTD%3E34%3C%2FTD%3E%3CTD%3E12%3C%2FTD%3E%3CTD%3EBangalore%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEmployee%207%3C%2FTD%3E%3CTD%3E32%3C%2FTD%3E%3CTD%3E16%3C%2FTD%3E%3CTD%3EBangalore%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEmployee%208%3C%2FTD%3E%3CTD%3E43%3C%2FTD%3E%3CTD%3E12%3C%2FTD%3E%3CTD%3EMumbai%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEmployee%209%3C%2FTD%3E%3CTD%3E44%3C%2FTD%3E%3CTD%3E22%3C%2FTD%3E%3CTD%3EMumbai%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEmployee%2010%3C%2FTD%3E%3CTD%3E43%3C%2FTD%3E%3CTD%3E21%3C%2FTD%3E%3CTD%3EKolkata%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEmployee%2011%3C%2FTD%3E%3CTD%3E28%3C%2FTD%3E%3CTD%3E12%3C%2FTD%3E%3CTD%3EMumbai%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEmployee%2012%3C%2FTD%3E%3CTD%3E34%3C%2FTD%3E%3CTD%3E13%3C%2FTD%3E%3CTD%3ESingapore%2C%20Singapore%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEmployee%2013%3C%2FTD%3E%3CTD%3E44%3C%2FTD%3E%3CTD%3E16%3C%2FTD%3E%3CTD%3EMelbourne%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEmployee%2014%3C%2FTD%3E%3CTD%3E38%3C%2FTD%3E%3CTD%3E14%3C%2FTD%3E%3CTD%3EDubai%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEmployee%2015%3C%2FTD%3E%3CTD%3E45%3C%2FTD%3E%3CTD%3E21%3C%2FTD%3E%3CTD%3ENew%20Delhi%2C%20India%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEmployee%2016%3C%2FTD%3E%3CTD%3E29%3C%2FTD%3E%3CTD%3E16%3C%2FTD%3E%3CTD%3EChennai%2C%20India%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEmployee%2017%3C%2FTD%3E%3CTD%3E37%3C%2FTD%3E%3CTD%3E16%3C%2FTD%3E%3CTD%3ESan%20Fransico%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEmployee%2018%3C%2FTD%3E%3CTD%3E29%3C%2FTD%3E%3CTD%3E13%3C%2FTD%3E%3CTD%3EJakarta%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEmployee%2019%3C%2FTD%3E%3CTD%3E41%3C%2FTD%3E%3CTD%3E13%3C%2FTD%3E%3CTD%3ESan%20Fransico%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EEmployee%2020%3C%2FTD%3E%3CTD%3E37%3C%2FTD%3E%3CTD%3E15%3C%2FTD%3E%3CTD%3EJakarta%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2536268%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2536338%22%20slang%3D%22en-US%22%3ERe%3A%20To%20display%20the%20unique%20text%20based%20on%20some%20criteria%20in%20other%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2536338%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1100766%22%20target%3D%22_blank%22%3E%40deepak1310%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EAlso%20included%20is%20two%20sample%20file%20with%20some%20options.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fsort-data-in-a-range-or-table-62d0b95d-2a90-4610-a6ae-2e545c4a4654%3Fui%3Den-US%26amp%3Brs%3Den-US%26amp%3Bad%3DUS%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3ESort%20data%20in%20a%20range%20or%20table%3C%2FA%3E%3C%2FP%3E%3CP%20class%3D%22x-hidden-focus%22%3ESorting%20data%20is%20an%20integral%20part%20of%20data%20analysis.%20You%20might%20want%20to%20arrange%20a%20list%20of%20names%20in%20alphabetical%20order%2C%20compile%20a%20list%20of%20product%20inventory%20levels%20from%20highest%20to%20lowest%2C%20or%20order%20rows%20by%20colors%20or%20icons.%20Sorting%20data%20helps%20you%20quickly%20visualize%20and%20understand%20your%20data%20better%2C%20organize%20and%20find%20the%20data%20that%20you%20want%2C%20and%20ultimately%20make%20more%20effective%20decisions.%3C%2FP%3E%3CP%3EYou%20can%20sort%20data%20by%20text%20(A%20to%20Z%20or%20Z%20to%20A)%2C%20numbers%20(smallest%20to%20largest%20or%20largest%20to%20smallest)%2C%20and%20dates%20and%20times%20(oldest%20to%20newest%20and%20newest%20to%20oldest)%20in%20one%20or%20more%20columns.%20You%20can%20also%20sort%20by%20a%20custom%20list%20you%20create%20(such%20as%20Large%2C%20Medium%2C%20and%20Small)%20or%20by%20format%2C%20including%20cell%20color%2C%20font%20color%2C%20or%20icon%20set.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E*%20Kindly%20Mark%20and%20Vote%20this%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20Community%20members%20reading%20here.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2536355%22%20slang%3D%22en-US%22%3ERe%3A%20To%20display%20the%20unique%20text%20based%20on%20some%20criteria%20in%20other%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2536355%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1100766%22%20target%3D%22_blank%22%3E%40deepak1310%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDo%20you%20have%20Microsoft%20365%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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 NoAgeExperienceLocation 
Employee 12812Mumbai
Employee 24320Sydney
Employee 32817Mumbai
Employee 43410Sydney
Employee 52810Sydney
Employee 63412Bangalore
Employee 73216Bangalore
Employee 84312Mumbai
Employee 94422Mumbai
Employee 104321Kolkata
Employee 112812Mumbai
Employee 123413Singapore, Singapore
Employee 134416Melbourne
Employee 143814Dubai
Employee 154521New Delhi, India
Employee 162916Chennai, India
Employee 173716San Fransico
Employee 182913Jakarta
Employee 194113San Fransico
Employee 203715Jakarta
5 Replies

@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.

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

@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"), "")))