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

tomcat

New member
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 DataDesired Result
A​
B​
C​
D​
E​
F​
G​
IdentifierDepartmentSectionRoomSales
Sales 1Sales117117, 105
Sales 1Sales11051B71, 423
Sales 1BSales1B711C58
Sales 1BSales1B423
Sales 1CSales1C58HR
HR 1BHR1B4351B435
HR 3BHR3B933B93, 254
HR 3BHR3B254
 
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 DataDesired Result
A​
B​
C​
D​
E​
F​
G​
IdentifierDepartmentSectionRoomSales
Sales 1Sales117117, 105
Sales 1Sales11051B71, 423
Sales 1BSales1B711C58
Sales 1BSales1B423
Sales 1CSales1C58HR
HR 1BHR1B4351B435
HR 3BHR3B933B93, 254
HR 3BHR3B254
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"))
Unique Values.png
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.
Duplicate Values Comma seperated.png

Here is the Google Sheets
link: https://docs.google.com/spreadsheet...bwktw-wXhE/edit?gid=1850642820#gid=1850642820
 
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')?
 
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)))
Using SORT function.png

You can use any order of your choice.

Updated Google Sheets:

 

Online statistics

Members online
0
Guests online
44
Total visitors
44

Forum statistics

Threads
337
Messages
1,470
Members
624
Latest member
duytoi
Top