[Solved] Return all values associated with each unique value, including all duplicates in one cell separated by commas.

[tomcat]

I am needing help with sorting a data table I am working on. Below is an example set of data.

Column A is raw data that I'm starting with. Column B 'Section' has been extracted with a formula, which may or may not be needed.

I'm wanting to find all unique values in the 'Identifier' column (A) and return all corresponding values from the 'Room' column (C). Taking into account there will be duplicates in Column A, I want results from Column C returned in a single cell separated by commas for each unique value. I am familiar with CONCATENATE, for inserting ", " and joining values if needed. But I can't figure out whether a FILTER option is sufficient or creating a custom formula, or some array formula is needed. Being able to list the different departments at the top of each section of sorted/filtered data using a formula would be AWESOME, but if I have to do that manually, I'm ok with that.

I am currently working in Google Sheets, but can copy into Excel if there's not an equivalent in Sheets. I do have access to Excel 365.

This is my first time to post to any Excel forum, but this is an important project and need some swift help. I feel like there is probably a simple solution, but I'm stumped at the moment! Any help would be greatly appreciated.

Starting Data						Desired Result
A​		B​	C​	D​	E​	F​	G​
Identifier	Department	Section	Room			Sales
Sales 1	Sales	1	17			1	17, 105
Sales 1	Sales	1	105			1B	71, 423
Sales 1B	Sales	1B	71			1C	58
Sales 1B	Sales	1B	423
Sales 1C	Sales	1C	58			HR
HR 1B	HR	1B	435			1B	435
HR 3B	HR	3B	93			3B	93, 254
HR 3B	HR	3B	254

 

[shamimarita]

I am needing help with sorting a data table I am working on. Below is an example set of data.

Column A is raw data that I'm starting with. Column B 'Section' has been extracted with a formula, which may or may not be needed.

I'm wanting to find all unique values in the 'Identifier' column (A) and return all corresponding values from the 'Room' column (C). Taking into account there will be duplicates in Column A, I want results from Column C returned in a single cell separated by commas for each unique value. I am familiar with CONCATENATE, for inserting ", " and joining values if needed. But I can't figure out whether a FILTER option is sufficient or creating a custom formula, or some array formula is needed. Being able to list the different departments at the top of each section of sorted/filtered data using a formula would be AWESOME, but if I have to do that manually, I'm ok with that.

I am currently working in Google Sheets, but can copy into Excel if there's not an equivalent in Sheets. I do have access to Excel 365.

This is my first time to post to any Excel forum, but this is an important project and need some swift help. I feel like there is probably a simple solution, but I'm stumped at the moment! Any help would be greatly appreciated.

Starting Data						Desired Result
A​		B​	C​	D​	E​	F​	G​
Identifier	Department	Section	Room			Sales
Sales 1	Sales	1	17			1	17, 105
Sales 1	Sales	1	105			1B	71, 423
Sales 1B	Sales	1B	71			1C	58
Sales 1B	Sales	1B	423
Sales 1C	Sales	1C	58			HR
HR 1B	HR	1B	435			1B	435
HR 3B	HR	3B	93			3B	93, 254
HR 3B	HR	3B	254

Hello Tomcat,

To achieve the desired result of creating unique sections based on departments and room numbers in Google Sheets, I formatted your dataset. You can use your format by adjusting the cell references in the formulas according to your dataset.

First, use the UNIQUE and FILTER functions to create a unique list of sections for each department.
For the Sales department used =UNIQUE(FILTER(C2:C9, B2:B9="Sales"))
For the HR department =UNIQUE(FILTER(C2:C9, B2:B9="HR"))

Then, use the TEXTJOIN function along with FILTER to concatenate the room numbers for each unique section within the department,
For Sales =TEXTJOIN(", ", TRUE, FILTER($D$2:$D$9, $B$2:$B$9="Sales", $C$2:$C$9=G2))
For HR =TEXTJOIN(", ", TRUE, FILTER($D$2:$D$9, $B$2:$B$9="HR", $C$2:$C$9=I2))
These formulas will generate the unique sections and their corresponding room numbers grouped by the department as required.


Here is the Google Sheets
link: https://docs.google.com/spreadsheet...bwktw-wXhE/edit?gid=1850642820#gid=1850642820

 

[tomcat]

Thank you! That worked just as described. However, I'm noticing the joined, comma separated results are listed in the order they are found. Is there a way to sort the results in each cell in numerical order (ex. '423, 117, 17' vs '17, 117, 423')?

 

[shamimarita]

Thank you! That worked just as described. However, I'm noticing the joined, comma separated results are listed in the order they are found. Is there a way to sort the results in each cell in numerical order (ex. '423, 117, 17' vs '17, 117, 423')?

You are most welcome. Thanks for your appreciation.
There is a way to sort the results in numerical order: By using the SORT function within the previous formula.
To get in Descending order the formula is :
=TEXTJOIN(", ", TRUE, SORT(FILTER($D$2:$D$9, $B$2:$B$9="Sales", $C$2:$C$9=G2), 1, FALSE))
To get in Ascending order the formula is:
=TEXTJOIN(", ", TRUE, SORT(FILTER($D$2:$D$9, $B$2:$B$9="HR", $C$2:$C$9=I2)))


You can use any order of your choice.

Updated Google Sheets:

Unique Dept Duplicate Values Comma Separted

Sheet1 Dataset,Desired Result Identifier,Department,Section,Room,Sales Sales 1,Sales,1,17,1,17, 105 Sales 1,Sales,1,105,1B,71,423 Sales 1В,Sales,1B,71,1C,58 Sales 1В,Sales,1B,423 SaIes 1С,Sales,1C,58,HR HR 1В,HR,1B,435,1B,435 HR ЗВ,НR,3B,93,3B,93, 254 HR ЗВ,HR,3B,254

docs.google.com