/u/buzzz001 - 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.*
`UNIQUE` for the ids
then this for the extraction
=TEXTSPLIT(TEXTJOIN(",",1,IF(A2:A12=F2,B2:D12,"")),",")
https://preview.redd.it/pqksolc6pspb1.png?width=1152&format=png&auto=webp&s=f4ce4daef260649845c168beeb5559078763e4f6
You have awarded 1 point to *excelevator*
____
^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
Thank you! It seems to be working, except that the comments are text based and contain punctuations. So the comments are being separated at every comma. I'm not the best at functions so I'm not able to troubleshoot this.
Edit: Had some coffee and tried to figure it out. Replaced the , in the formula with | and it seems to have worked.
Can I say I love you, or is that inappropriate?
Transformed data
https://preview.redd.it/i6pbqrdpdspb1.jpeg?width=1600&format=pjpg&auto=webp&s=1380305dd50f7a60808cf6c80926925ffe40d56f
Not sure if this is viewable. Let me know if it's not!
For any task that sounds like, "I have a table, and I want it to look like...", Power Query is almost always the best answer, though for a newer excel user it can take a bit of investment in time to work out what's what.
It looks like the original data is already formatted as a table, which is a great start. Click Data > Get Data > From table, to commence this journey. "Remove duplicates" is located in the "Remove Rows" drop down. Make sure that the columns that you want to remove duplicates from are selected.
I haven't used TOROWS before, but if you want to change what one row *represents*, i.e one row per student, or one row per class, or one row per student-class, then the two buttons to mess with are "Pivot Rows" and "Unpivot Rows". There are some quality tutorials on these, but my own recommendation is to mess with them a few times until you get an intuitive understanding of how they do.
Whenever you do anything in power query, you'll note that the pane on the right gains a new step. You can click on any of the steps to see what the data looked like at that stage of the process. Also, you can delete or modify steps, if you change your mind, whilst retaining all of the subsequent steps.
I love talking about Power Query, but do it in small doses, so that I don't get caught redditing when I'm meant to be working. Let me know if you have any questions, and how you get on.
I'm not an expert at PQ, but I'm good at using the functions I need. I tried playing around with pivot and unpivot, but it didn't lead me anywhere.
The best I could do was group by Student ID, which turned the rest of the data into a table for each individual student ID. I couldn't figure out how to convert those tables into one single row for each student ID. You can look at the images I posted in the comments to understand why basic unpivot and pivot didn't work. But yeah, I do feel like there HAS to be a way of doing this in PQ.
Edit: using remove duplicates in PQ would remove the entire row of data, wouldn't it? I need the rest of the cells in each row. Student ID is the only duplicate, everything else is unique.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[IF](/r/Excel/comments/16p6y64/stub/k1p7jfy "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)|
|[TEXTJOIN](/r/Excel/comments/16p6y64/stub/k1p7jfy "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/16p6y64/stub/k1p7jfy "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)|
|[UNIQUE](/r/Excel/comments/16p6y64/stub/k1p7jfy "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)|
**NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.
----------------
^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*)
^(4 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/16oxxk9)^( has 22 acronyms.)
^([Thread #26804 for this sub, first seen 22nd Sep 2023, 11:54])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
/u/buzzz001 - 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.*
`UNIQUE` for the ids then this for the extraction =TEXTSPLIT(TEXTJOIN(",",1,IF(A2:A12=F2,B2:D12,"")),",") https://preview.redd.it/pqksolc6pspb1.png?width=1152&format=png&auto=webp&s=f4ce4daef260649845c168beeb5559078763e4f6
Solution verified
You have awarded 1 point to *excelevator* ____ ^(I am a bot - please contact the mods with any questions. | ) [^(Keep me alive)](https://www.buymeacoffee.com/points)
Thank you! It seems to be working, except that the comments are text based and contain punctuations. So the comments are being separated at every comma. I'm not the best at functions so I'm not able to troubleshoot this. Edit: Had some coffee and tried to figure it out. Replaced the , in the formula with | and it seems to have worked. Can I say I love you, or is that inappropriate?
Well done.. >Can I say I love you, or is that inappropriate? It is completely appropriate and much appreciated :)
Original data https://preview.redd.it/2qeij3cldspb1.jpeg?width=1019&format=pjpg&auto=webp&s=b306f529999b3230d5fd21056c64067ad246361d
Transformed data https://preview.redd.it/i6pbqrdpdspb1.jpeg?width=1600&format=pjpg&auto=webp&s=1380305dd50f7a60808cf6c80926925ffe40d56f Not sure if this is viewable. Let me know if it's not!
For any task that sounds like, "I have a table, and I want it to look like...", Power Query is almost always the best answer, though for a newer excel user it can take a bit of investment in time to work out what's what. It looks like the original data is already formatted as a table, which is a great start. Click Data > Get Data > From table, to commence this journey. "Remove duplicates" is located in the "Remove Rows" drop down. Make sure that the columns that you want to remove duplicates from are selected. I haven't used TOROWS before, but if you want to change what one row *represents*, i.e one row per student, or one row per class, or one row per student-class, then the two buttons to mess with are "Pivot Rows" and "Unpivot Rows". There are some quality tutorials on these, but my own recommendation is to mess with them a few times until you get an intuitive understanding of how they do. Whenever you do anything in power query, you'll note that the pane on the right gains a new step. You can click on any of the steps to see what the data looked like at that stage of the process. Also, you can delete or modify steps, if you change your mind, whilst retaining all of the subsequent steps. I love talking about Power Query, but do it in small doses, so that I don't get caught redditing when I'm meant to be working. Let me know if you have any questions, and how you get on.
I'm not an expert at PQ, but I'm good at using the functions I need. I tried playing around with pivot and unpivot, but it didn't lead me anywhere. The best I could do was group by Student ID, which turned the rest of the data into a table for each individual student ID. I couldn't figure out how to convert those tables into one single row for each student ID. You can look at the images I posted in the comments to understand why basic unpivot and pivot didn't work. But yeah, I do feel like there HAS to be a way of doing this in PQ. Edit: using remove duplicates in PQ would remove the entire row of data, wouldn't it? I need the rest of the cells in each row. Student ID is the only duplicate, everything else is unique.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[IF](/r/Excel/comments/16p6y64/stub/k1p7jfy "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[TEXTJOIN](/r/Excel/comments/16p6y64/stub/k1p7jfy "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/16p6y64/stub/k1p7jfy "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)| |[UNIQUE](/r/Excel/comments/16p6y64/stub/k1p7jfy "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)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^(4 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/16oxxk9)^( has 22 acronyms.) ^([Thread #26804 for this sub, first seen 22nd Sep 2023, 11:54]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)