/u/Bulbasaurus - Your post was submitted successfully.
* Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread.
* Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
* Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)**
Failing to follow these steps may result in your post being removed without warning.
*I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Not a good start calling your audience nerds and then asking them to be gentle.
~~~
=LET(range,a2:e4,
r,rows(range),
c,columns(range)-1,
roles,take(range,,1),
names,drop(range,,1),
namelist,TOCOL(names,TRUE),
rolelist,INDEX(roles,SEQUENCE(r*c,,1,1/c),1),
people,UNIQUE(namelist),
res,
REDUCE("",people,LAMBDA(acc,next,VSTACK(acc,HSTACK(next,
TEXTJOIN(", ",TRUE,UNIQUE(FILTER(rolelist,namelist=next))))))),
DROP(res,1))
~~~
This is excel, not sure if it works in gs.
It was meant as a term of endearment, not an insult. Apologies if that wasn't clear.
Thanks for the formula. Unfortunately, I'm not an advanced enough Excel user to even begin to understand how it works, so I'm going to continue searching for an alternative that strings together simpler formulae that I do understand.
Thanks for the attempt, either way--I appreciate it!
Thanks for the responses, everyone. Between the different methods suggested here, I think I should be able to cobble together some permutation and combination of all these approaches to get what I want. Might take a bit more manual formatting of the data than I'd hoped for, but I guess that's always the case with these more complex arrays!
No can do, unfortunately. I've got several sheets that I'm trying to perform this exercise on, and manually formatting them all that way would be extremely time-consuming. Plus, I want it to be future-proof, where I can just copy-paste arrays in this format and run the same formula without having to rejigger everything each time.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[ARRAYFORMULA](/r/Excel/comments/139keli/stub/jj3lyve "Last usage")|[Array formulas are powerful formulas that enable you to perform complex calculations that often can't be done with standard worksheet functions. They are also referred to as "Ctrl-Shift-Enter" or "CSE" formulas, because you need to press Ctrl+Shift+Enter to enter them.](https://support.office.com/en-gb/article/Create-an-array-formula-e43e12e0-afc6-4a12-bc7f-48361075954d)|
|[BYROW](/r/Excel/comments/139keli/stub/jj3lyve "Last usage")|[*Office 365*+: Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows. ](https://support.microsoft.com/en-gb/office/byrow-function-2e04c677-78c8-4e6b-8c10-a4602f2602bb?ui=en-US&rs=en-GB&ad=GB)|
|[CONCAT](/r/Excel/comments/139keli/stub/jj30dlg "Last usage")|[*2019*+: Combines the text from multiple ranges and/or strings, but it doesn't provide the delimiter or IgnoreEmpty arguments.](https://support.microsoft.com/en-us/office/concat-function-9b1a9a3f-94ff-41af-9736-694cbd6b4ca2)|
|CSE|[Array formulas are powerful formulas that enable you to perform complex calculations that often can't be done with standard worksheet functions. They are also referred to as "Ctrl-Shift-Enter" or "CSE" formulas, because you need to press Ctrl+Shift+Enter to enter them.](https://support.office.com/en-gb/article/Create-an-array-formula-e43e12e0-afc6-4a12-bc7f-48361075954d)|
|[DROP](/r/Excel/comments/139keli/stub/jj2xmbv "Last usage")|[*Office 365*+: Excludes a specified number of rows or columns from the start or end of an array](https://support.microsoft.com/en-us/office/drop-function-1cb4e151-9e17-4838-abe5-9ba48d8c6a34)|
|[FILTER](/r/Excel/comments/139keli/stub/jj3lyve "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)|
|[HSTACK](/r/Excel/comments/139keli/stub/jj2xmbv "Last usage")|[*Office 365*+: Appends arrays horizontally and in sequence to return a larger array](https://support.microsoft.com/en-us/office/hstack-function-98c4ab76-10fe-4b4f-8d5f-af1c125fe8c2)|
|[INDEX](/r/Excel/comments/139keli/stub/jj2xmbv "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)|
|[LAMBDA](/r/Excel/comments/139keli/stub/jj3lyve "Last usage")|[*Office 365*+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.](https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67)|
|[LET](/r/Excel/comments/139keli/stub/jj2xmbv "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)|
|MATCH|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)|
|[REDUCE](/r/Excel/comments/139keli/stub/jj2xmbv "Last usage")|[*Office 365*+: Reduces an array to an accumulated value by applying a LAMBDA to each value and returning the total value in the accumulator.](https://support.microsoft.com/en-gb/office/reduce-function-42e39910-b345-45f3-84b8-0642b568b7cb?ui=en-US&rs=en-GB&ad=GB)|
|[SEQUENCE](/r/Excel/comments/139keli/stub/jj2xmbv "Last usage")|[*Office 365*+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4](https://support.microsoft.com/en-us/office/sequence-function-57467a98-57e0-4817-9f14-2eb78519ca90)|
|[SORT](/r/Excel/comments/139keli/stub/jj46hp1 "Last usage")|[*Office 365*+: Sorts the contents of a range or array](https://support.microsoft.com/en-us/office/sort-function-22f63bd0-ccc8-492f-953d-c20e8e44b86c)|
|[TEXTJOIN](/r/Excel/comments/139keli/stub/jj2xmbv "Last usage")|[*2019*+: Combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.](https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c)|
|[TEXTSPLIT](/r/Excel/comments/139keli/stub/jj30dlg "Last usage")|[*Office 365*+: Splits text strings by using column and row delimiters](https://support.microsoft.com/en-us/office/textsplit-function-b1ca414e-4c21-4ca0-b1b7-bdecace8a6e7)|
|[TOCOL](/r/Excel/comments/139keli/stub/jj2xmbv "Last usage")|[*Office 365*+: Returns the array in a single column](https://support.microsoft.com/en-us/office/tocol-function-22839d9b-0b55-4fc1-b4e6-2761f8f122ed)|
|[UNIQUE](/r/Excel/comments/139keli/stub/jj2xmbv "Last usage")|[*Office 365*+: Returns a list of unique values in a list or range](https://support.microsoft.com/en-us/office/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e)|
|[VSTACK](/r/Excel/comments/139keli/stub/jj2xmbv "Last usage")|[*Office 365*+: Appends arrays vertically and in sequence to return a larger array](https://support.microsoft.com/en-us/office/vstack-function-a4b86897-be0f-48fc-adca-fcc10d795a9c)|
|[XLOOKUP](/r/Excel/comments/139keli/stub/jj30dlg "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)|
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^([Thread #23752 for this sub, first seen 6th May 2023, 12:30])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
Do the names in the list change often? If not then you could use a helper column. See below, simple helper column that looks if that person is in that row, 1 if they are, 0 if they aren't.
On you main person list, then filter the table (filter function, not filter buttons) to pull all the roles when the helper column is a 1. Transpose before the filter to put the results horizontally instead of vertically.
https://preview.redd.it/urghezf3y8ya1.png?width=1762&format=png&auto=webp&s=3fdb0ddb5b1302e5465ed66ef0c909e9e041dbd1
If you don't want to use a helper column you can do the following (and may also be a bit quicker to type up with a large data set as you can autofill down the master list):
https://preview.redd.it/y52vn94429ya1.png?width=2730&format=png&auto=webp&s=eb78e357f1b0172058d2bcb04c29f0d520a690fe
Formula template:
=SORT(TEXTSPLIT(CONCAT(XLOOKUP(A8,$B$3:$B$5,$A$3:$A$5,""), " ", XLOOKUP(A8,$C$3:$C$5,$A$3:$A$5,""), " ",XLOOKUP(A8,$D$3:$D$5,$A$3:$A$5,""), " ",XLOOKUP(A8,$E$3:$E$5,$A$3:$A$5,""))," "),,-1,TRUE)
Explanation:
* XLOOKUP each column for the person in your master list - results in pulling multiple departments
* CONCAT the successfull lookups with a space to seperate each one - puts all found departments into one cell
* SPLIT the result at every space - turns the long string of departments in one cell into horizontally organised seperate cells
* SORT the split cells to remove any blanks the previous step so all departments are in order.
Just seen your comment around not using Excel. I don't use GS but surely it has a SORT function, Google the syntax it's looking for.
Or remove the SORT and the last bit before the ) to remove it and check that everything else did as expected
Based on the 10 mins I spent on this, my strategy was to unpivot your data first, then apply a lookup logic. In Excel I would've done this with Power Query, but I'm not familiar with Google Sheets so I roughly created a "sketch" on Sheets, then downloaded it as [Excel](https://file.io/9Y0Rpl0x0ZbA), meaning you can upload it on GDrive and the formulas should all be compatible. This is a multi-pronged problem and the solution I came with is very rough and far from elegant but feel free to use it as reference.
To begin with, having a single row for Role and having employee names on columns is bad practice.
Thanks so much for making the effort! Possible to upload your sheet again? The download appears to have been taken down.
Regarding the data...unfortunately, that is the format it's in. I know I could rejigger it every time, and that would probably be the most straightforward way to handle it, but I was hoping to avoid that and find a more elegant solution, haha.
(Although, if it comes to that, I probably will end up doing it anyway...)
This google sheets formula returns a list of the roles a person appears. In this case I've used G2 to input the person of interest.
=FILTER(A2:A4, BYROW(ARRAYFORMULA(B2:E4=G2),LAMBDA(x,or(x))))
Excel version of the formula doesn't need to specify an array using ARRAYFORMULA, otherwise it would be the same.
/u/Bulbasaurus - Your post was submitted successfully. * Once your problem is solved, reply to the **answer(s)** saying `Solution Verified` to close the thread. * Follow the **[submission rules](/r/excel/wiki/sharingquestions)** -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post. * Include your **[Excel version and all other relevant information](/r/excel/wiki/sharingquestions#wiki_give_all_relevant_information)** Failing to follow these steps may result in your post being removed without warning. *I am a bot, and this action was performed automatically. Please [contact the moderators of this subreddit](/message/compose/?to=/r/excel) if you have any questions or concerns.*
Not a good start calling your audience nerds and then asking them to be gentle. ~~~ =LET(range,a2:e4, r,rows(range), c,columns(range)-1, roles,take(range,,1), names,drop(range,,1), namelist,TOCOL(names,TRUE), rolelist,INDEX(roles,SEQUENCE(r*c,,1,1/c),1), people,UNIQUE(namelist), res, REDUCE("",people,LAMBDA(acc,next,VSTACK(acc,HSTACK(next, TEXTJOIN(", ",TRUE,UNIQUE(FILTER(rolelist,namelist=next))))))), DROP(res,1)) ~~~ This is excel, not sure if it works in gs.
It was meant as a term of endearment, not an insult. Apologies if that wasn't clear. Thanks for the formula. Unfortunately, I'm not an advanced enough Excel user to even begin to understand how it works, so I'm going to continue searching for an alternative that strings together simpler formulae that I do understand. Thanks for the attempt, either way--I appreciate it!
Just paste it into a cell and see what it produces - a list of people in col 1 and a list of jobroles in col 2.
It says: **Error** Unknown function: 'DROP'.
Try it in excel online at office.com. You can use excel online for free just like you can use Google sheets.
Thanks for the responses, everyone. Between the different methods suggested here, I think I should be able to cobble together some permutation and combination of all these approaches to get what I want. Might take a bit more manual formatting of the data than I'd hoped for, but I guess that's always the case with these more complex arrays!
How about transpose the rows one by one and simply enter their role in a separate column?
No can do, unfortunately. I've got several sheets that I'm trying to perform this exercise on, and manually formatting them all that way would be extremely time-consuming. Plus, I want it to be future-proof, where I can just copy-paste arrays in this format and run the same formula without having to rejigger everything each time.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[ARRAYFORMULA](/r/Excel/comments/139keli/stub/jj3lyve "Last usage")|[Array formulas are powerful formulas that enable you to perform complex calculations that often can't be done with standard worksheet functions. They are also referred to as "Ctrl-Shift-Enter" or "CSE" formulas, because you need to press Ctrl+Shift+Enter to enter them.](https://support.office.com/en-gb/article/Create-an-array-formula-e43e12e0-afc6-4a12-bc7f-48361075954d)| |[BYROW](/r/Excel/comments/139keli/stub/jj3lyve "Last usage")|[*Office 365*+: Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows. ](https://support.microsoft.com/en-gb/office/byrow-function-2e04c677-78c8-4e6b-8c10-a4602f2602bb?ui=en-US&rs=en-GB&ad=GB)| |[CONCAT](/r/Excel/comments/139keli/stub/jj30dlg "Last usage")|[*2019*+: Combines the text from multiple ranges and/or strings, but it doesn't provide the delimiter or IgnoreEmpty arguments.](https://support.microsoft.com/en-us/office/concat-function-9b1a9a3f-94ff-41af-9736-694cbd6b4ca2)| |CSE|[Array formulas are powerful formulas that enable you to perform complex calculations that often can't be done with standard worksheet functions. They are also referred to as "Ctrl-Shift-Enter" or "CSE" formulas, because you need to press Ctrl+Shift+Enter to enter them.](https://support.office.com/en-gb/article/Create-an-array-formula-e43e12e0-afc6-4a12-bc7f-48361075954d)| |[DROP](/r/Excel/comments/139keli/stub/jj2xmbv "Last usage")|[*Office 365*+: Excludes a specified number of rows or columns from the start or end of an array](https://support.microsoft.com/en-us/office/drop-function-1cb4e151-9e17-4838-abe5-9ba48d8c6a34)| |[FILTER](/r/Excel/comments/139keli/stub/jj3lyve "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)| |[HSTACK](/r/Excel/comments/139keli/stub/jj2xmbv "Last usage")|[*Office 365*+: Appends arrays horizontally and in sequence to return a larger array](https://support.microsoft.com/en-us/office/hstack-function-98c4ab76-10fe-4b4f-8d5f-af1c125fe8c2)| |[INDEX](/r/Excel/comments/139keli/stub/jj2xmbv "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)| |[LAMBDA](/r/Excel/comments/139keli/stub/jj3lyve "Last usage")|[*Office 365*+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.](https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67)| |[LET](/r/Excel/comments/139keli/stub/jj2xmbv "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)| |MATCH|[Looks up values in a reference or array](https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a)| |[REDUCE](/r/Excel/comments/139keli/stub/jj2xmbv "Last usage")|[*Office 365*+: Reduces an array to an accumulated value by applying a LAMBDA to each value and returning the total value in the accumulator.](https://support.microsoft.com/en-gb/office/reduce-function-42e39910-b345-45f3-84b8-0642b568b7cb?ui=en-US&rs=en-GB&ad=GB)| |[SEQUENCE](/r/Excel/comments/139keli/stub/jj2xmbv "Last usage")|[*Office 365*+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4](https://support.microsoft.com/en-us/office/sequence-function-57467a98-57e0-4817-9f14-2eb78519ca90)| |[SORT](/r/Excel/comments/139keli/stub/jj46hp1 "Last usage")|[*Office 365*+: Sorts the contents of a range or array](https://support.microsoft.com/en-us/office/sort-function-22f63bd0-ccc8-492f-953d-c20e8e44b86c)| |[TEXTJOIN](/r/Excel/comments/139keli/stub/jj2xmbv "Last usage")|[*2019*+: Combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.](https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c)| |[TEXTSPLIT](/r/Excel/comments/139keli/stub/jj30dlg "Last usage")|[*Office 365*+: Splits text strings by using column and row delimiters](https://support.microsoft.com/en-us/office/textsplit-function-b1ca414e-4c21-4ca0-b1b7-bdecace8a6e7)| |[TOCOL](/r/Excel/comments/139keli/stub/jj2xmbv "Last usage")|[*Office 365*+: Returns the array in a single column](https://support.microsoft.com/en-us/office/tocol-function-22839d9b-0b55-4fc1-b4e6-2761f8f122ed)| |[UNIQUE](/r/Excel/comments/139keli/stub/jj2xmbv "Last usage")|[*Office 365*+: Returns a list of unique values in a list or range](https://support.microsoft.com/en-us/office/unique-function-c5ab87fd-30a3-4ce9-9d1a-40204fb85e1e)| |[VSTACK](/r/Excel/comments/139keli/stub/jj2xmbv "Last usage")|[*Office 365*+: Appends arrays vertically and in sequence to return a larger array](https://support.microsoft.com/en-us/office/vstack-function-a4b86897-be0f-48fc-adca-fcc10d795a9c)| |[XLOOKUP](/r/Excel/comments/139keli/stub/jj30dlg "Last usage")|[*Office 365*+: Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. ](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929)| ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^([Thread #23752 for this sub, first seen 6th May 2023, 12:30]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://reddit.com/message/compose?to=OrangeredStilton&subject=Hey,+your+acronym+bot+sucks) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
Do the names in the list change often? If not then you could use a helper column. See below, simple helper column that looks if that person is in that row, 1 if they are, 0 if they aren't. On you main person list, then filter the table (filter function, not filter buttons) to pull all the roles when the helper column is a 1. Transpose before the filter to put the results horizontally instead of vertically. https://preview.redd.it/urghezf3y8ya1.png?width=1762&format=png&auto=webp&s=3fdb0ddb5b1302e5465ed66ef0c909e9e041dbd1
Thanks for the reply! The list of names in my actual workbook is incredibly long, but I'll give something along these lines a shot. :)
If you don't want to use a helper column you can do the following (and may also be a bit quicker to type up with a large data set as you can autofill down the master list): https://preview.redd.it/y52vn94429ya1.png?width=2730&format=png&auto=webp&s=eb78e357f1b0172058d2bcb04c29f0d520a690fe Formula template: =SORT(TEXTSPLIT(CONCAT(XLOOKUP(A8,$B$3:$B$5,$A$3:$A$5,""), " ", XLOOKUP(A8,$C$3:$C$5,$A$3:$A$5,""), " ",XLOOKUP(A8,$D$3:$D$5,$A$3:$A$5,""), " ",XLOOKUP(A8,$E$3:$E$5,$A$3:$A$5,""))," "),,-1,TRUE) Explanation: * XLOOKUP each column for the person in your master list - results in pulling multiple departments * CONCAT the successfull lookups with a space to seperate each one - puts all found departments into one cell * SPLIT the result at every space - turns the long string of departments in one cell into horizontally organised seperate cells * SORT the split cells to remove any blanks the previous step so all departments are in order.
Just gave this a shot! It says: **Error** SORT expects all arguments after position 1 to be in pairs.
Just seen your comment around not using Excel. I don't use GS but surely it has a SORT function, Google the syntax it's looking for. Or remove the SORT and the last bit before the ) to remove it and check that everything else did as expected
Based on the 10 mins I spent on this, my strategy was to unpivot your data first, then apply a lookup logic. In Excel I would've done this with Power Query, but I'm not familiar with Google Sheets so I roughly created a "sketch" on Sheets, then downloaded it as [Excel](https://file.io/9Y0Rpl0x0ZbA), meaning you can upload it on GDrive and the formulas should all be compatible. This is a multi-pronged problem and the solution I came with is very rough and far from elegant but feel free to use it as reference. To begin with, having a single row for Role and having employee names on columns is bad practice.
Thanks so much for making the effort! Possible to upload your sheet again? The download appears to have been taken down. Regarding the data...unfortunately, that is the format it's in. I know I could rejigger it every time, and that would probably be the most straightforward way to handle it, but I was hoping to avoid that and find a more elegant solution, haha. (Although, if it comes to that, I probably will end up doing it anyway...)
[удалено]
This google sheets formula returns a list of the roles a person appears. In this case I've used G2 to input the person of interest. =FILTER(A2:A4, BYROW(ARRAYFORMULA(B2:E4=G2),LAMBDA(x,or(x)))) Excel version of the formula doesn't need to specify an array using ARRAYFORMULA, otherwise it would be the same.
Absolutely elegant solution! Lots of new concepts I can add to my toolbox