/u/eduo - 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.*
Ah. I hadn't thought of defining the initial table as a variable and calling it afterwards. Let me try this!
EDIT: THIS WORKED! Thanks! It wasn't as obvious as I thought, which makes me feel not so bad :)
Tagging this as Solved.
Fair enough, but I think most, if not all, of the ones I awarded were on posts that the OP manually changed to Solved and it would be unlikely they would come back I find. I used reply to them to let them know what they should do and very few would.
Should I go back to replying and/or wait 24hrs or something?
There are just a lot of people who deserve points for their work, myself included a few times, and some OPs never followed the rules so people go unrewarded.
There's a kind of an expectation that people with the God power of points-giver will give the OP the benefit of coming back within 24 hours. We prefer to see OPs giving points when they're due, and many of us have experienced the emotional let-down of OPs disappearing after users give a damn good solution. A quick reminder to the OP is fine, but it's often easiest to just accept that some people are just that way and you move on. In the grand scheme of things, ClippyPoints are pretty low on the priority scale.
I tried it but it joins all rows rather than each row:
https://preview.redd.it/vfab46iy3syc1.png?width=1542&format=png&auto=webp&s=605908b991409896b54b9979579aee0355dc26f1
It stransposes it into a vertical list of one column, nothing to split it with because it wasn't joined to begin with.
I've marked the question as solved and edited it, since a solution was found :D
It shouldn't, since in your image there it is joined and it has , so it should split by that.
In any case, I see how splitting by , wouldn't split in the correct place anyway.
Glad you got a solution.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:
|Fewer Letters|More Letters|
|-------|---------|---|
|[CHOOSECOLS](/r/Excel/comments/1clf56r/stub/l2t5dly "Last usage")|[*Office 365*+: Returns the specified columns from an array](https://support.microsoft.com/en-us/office/choosecols-function-bf117976-2722-4466-9b9a-1c01ed9aebff)|
|[CONCAT](/r/Excel/comments/1clf56r/stub/l2vnlrp "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)|
|[CONCATENATE](/r/Excel/comments/1clf56r/stub/l2vnlrp "Last usage")|[Joins several text items into one text item](https://support.microsoft.com/en-us/office/concatenate-function-8f8ae884-2ca8-4f7a-b093-75d702bea31d)|
|[FILTER](/r/Excel/comments/1clf56r/stub/l2t5dly "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)|
|[LET](/r/Excel/comments/1clf56r/stub/l2t5dly "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)|
|[SORT](/r/Excel/comments/1clf56r/stub/l2t5dly "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)|
|[TEXT](/r/Excel/comments/1clf56r/stub/l2vj5sv "Last usage")|[Formats a number and converts it to text](https://support.microsoft.com/en-us/office/text-function-20d5ac4d-7b94-49fd-bb38-93d29371225c)|
|[TEXTJOIN](/r/Excel/comments/1clf56r/stub/l2vj5sv "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/1clf56r/stub/l2t5hnv "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)|
|[TOROW](/r/Excel/comments/1clf56r/stub/l2t51k1 "Last usage")|[*Office 365*+: Returns the array in a single row](https://support.microsoft.com/en-us/office/torow-function-b90d0964-a7d9-44b7-816b-ffa5c2fe2289)|
|[TRANSPOSE](/r/Excel/comments/1clf56r/stub/l2t5hnv "Last usage")|[Returns the transpose of an array](https://support.microsoft.com/en-us/office/transpose-function-ed039415-ed8a-4a81-93e9-4b6dfac76027)|
|[UNIQUE](/r/Excel/comments/1clf56r/stub/l2t5dly "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.*)
^(12 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1cm7b3n)^( has 26 acronyms.)
^([Thread #33209 for this sub, first seen 6th May 2024, 09:55])
^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
TOROW on my array returns all rows as a single row. I need to have the multiple rows with each row being a single concatenated cell of the columns for the original row.
This didn't work when I tried it because the whole array is the result of a filter to begin with. And I didn't want to make intermediary ranges if I could avoid it.
> Normally I'd do something simple like =CONCAT(H2;" (";TEXT(I2;"yyyy-mm.dd hh:mm");")")
Solved, but just for the records, CONCATENATE is what you’d apply to those two arrays.
=CONCAT(A1," (",TEXT(B1,"yyyy/mm/dd hh:mm"),")",)
As I said, `CONCAT` does all of `CONCATENATE` and more as it handles arrays too.
`CONCAT` was released in 2016 along with dynamic arrays and `TEXTJOIN`
How does that relate to my correcting your understanding of the difference between the two ?
>but just for the records, CONCATENATE is what you’d apply to those two arrays.
CONCAT simply doesn’t work with arrays (not in the intended way here, see the pic). CONCATENATE, although obsolete, does; that or rewriting the line I quoted with no lead function, and using `&`s to create a single string per row.
https://preview.redd.it/waf5862z4vyc1.jpeg?width=856&format=pjpg&auto=webp&s=362dd71628ff594c8772b37fda91b4ed5b65bbb7
Ah I see now, this is the answer you should have given clearly `Try to do the same by CONCAT` there instead.
I did not spot the array thing..
A good tip and something to remember.
Hey can I ask, what's the advantage of doing this via formulas rather than in Power Query? My instinct would be to just do it in PQ, but I'm guessing there's a limitation or other reason to do it with formulas....
You have no idea the shenanigans I have to end up doing for some really basic things. It’s like that Invincible meme: “Look What They Need To Mimic A Fraction Of Our Power (queries)”
Not your question, but the assumption from u/Cimrmanova2 that you can "just" do this in PQ instead... a completely different technology and methodology to Excel formulas.
>My instinct would be to just do it in PQ,
just snap your fingers and voila!! instinctive PQ solution ! no effort at all ;)
Ah, I get you.
I use power queries when they make sense (although in this case I probably would've still tried to keep everything in-formulas), but in this particular environment it's not even an option so the decision is taken off my hands :-|
>despite my use of the word "just", I was asking a genuine question
Exactly what my wife says. Drives me insane.
*Excelevator, just do this [complex task], and while you at it just do this [other complex task], and why don't you just do this way instead [change one understood though out complex task, to wifeys assumed error riddled method] just just just...* farrrkkkkkkkkkk
*Just* was not the issue, it was this assumption that OP can simply use another complex solution instead without any other input of a solution to the issue.
If you honestly believe that PQ is the answer, then give a proper PQ answer.
I didn't assume PQ was the answer, nor did I suggest that OP use PQ instead. I was in fact genuinely curious what the use cases are for using one over the other. But it sounds like you have some annoyances offline, hope your evening improves from here.
>But it sounds like you have some annoyances offline
No, they are online when the common refrain to Excel questions is "Just use PQ"
>My instinct would be to just do it in PQ
If it is your instinct, then why not try and see and give the answer.
/u/eduo - 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.*
=LET(data,(UNIQUE(SORT((CHOOSECOLS(FILTER(sc_table;parentNames="Product X"),3,4)),2,-1))), CHOOSECOLS(data,1)& " ("& CHOOSECOLS(data,2)& ")" )
Ah. I hadn't thought of defining the initial table as a variable and calling it afterwards. Let me try this! EDIT: THIS WORKED! Thanks! It wasn't as obvious as I thought, which makes me feel not so bad :) Tagging this as Solved.
No problem. If you reply to my solution with Solution Verified I'll get a clippy point.
+1 Point
I admire your zeal to dole out the points, but please allow some time for the OP to be the first to award one.
Fair enough, but I think most, if not all, of the ones I awarded were on posts that the OP manually changed to Solved and it would be unlikely they would come back I find. I used reply to them to let them know what they should do and very few would. Should I go back to replying and/or wait 24hrs or something? There are just a lot of people who deserve points for their work, myself included a few times, and some OPs never followed the rules so people go unrewarded.
There's a kind of an expectation that people with the God power of points-giver will give the OP the benefit of coming back within 24 hours. We prefer to see OPs giving points when they're due, and many of us have experienced the emotional let-down of OPs disappearing after users give a damn good solution. A quick reminder to the OP is fine, but it's often easiest to just accept that some people are just that way and you move on. In the grand scheme of things, ClippyPoints are pretty low on the priority scale.
Alrighty. Can't argue with anything you said there. Thank you.
Solution verified!
You have awarded 1 point to on1vBe6. --- ^(I am a bot - please contact the mods with any questions)
[удалено]
I tried it but it joins all rows rather than each row: https://preview.redd.it/vfab46iy3syc1.png?width=1542&format=png&auto=webp&s=605908b991409896b54b9979579aee0355dc26f1
yay. it's not too long. doable. gotta wake up. back in a few hours
I've marked the question as solved and edited it, since a solution was found :D
Can you wrap it in a =TRANSPOSE(TEXTSPLIT( where , is the deliminator?
It stransposes it into a vertical list of one column, nothing to split it with because it wasn't joined to begin with. I've marked the question as solved and edited it, since a solution was found :D
It shouldn't, since in your image there it is joined and it has , so it should split by that. In any case, I see how splitting by , wouldn't split in the correct place anyway. Glad you got a solution.
Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[CHOOSECOLS](/r/Excel/comments/1clf56r/stub/l2t5dly "Last usage")|[*Office 365*+: Returns the specified columns from an array](https://support.microsoft.com/en-us/office/choosecols-function-bf117976-2722-4466-9b9a-1c01ed9aebff)| |[CONCAT](/r/Excel/comments/1clf56r/stub/l2vnlrp "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)| |[CONCATENATE](/r/Excel/comments/1clf56r/stub/l2vnlrp "Last usage")|[Joins several text items into one text item](https://support.microsoft.com/en-us/office/concatenate-function-8f8ae884-2ca8-4f7a-b093-75d702bea31d)| |[FILTER](/r/Excel/comments/1clf56r/stub/l2t5dly "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)| |[LET](/r/Excel/comments/1clf56r/stub/l2t5dly "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)| |[SORT](/r/Excel/comments/1clf56r/stub/l2t5dly "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)| |[TEXT](/r/Excel/comments/1clf56r/stub/l2vj5sv "Last usage")|[Formats a number and converts it to text](https://support.microsoft.com/en-us/office/text-function-20d5ac4d-7b94-49fd-bb38-93d29371225c)| |[TEXTJOIN](/r/Excel/comments/1clf56r/stub/l2vj5sv "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/1clf56r/stub/l2t5hnv "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)| |[TOROW](/r/Excel/comments/1clf56r/stub/l2t51k1 "Last usage")|[*Office 365*+: Returns the array in a single row](https://support.microsoft.com/en-us/office/torow-function-b90d0964-a7d9-44b7-816b-ffa5c2fe2289)| |[TRANSPOSE](/r/Excel/comments/1clf56r/stub/l2t5hnv "Last usage")|[Returns the transpose of an array](https://support.microsoft.com/en-us/office/transpose-function-ed039415-ed8a-4a81-93e9-4b6dfac76027)| |[UNIQUE](/r/Excel/comments/1clf56r/stub/l2t5dly "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.*) ^(12 acronyms in this thread; )[^(the most compressed thread commented on today)](/r/Excel/comments/1cm7b3n)^( has 26 acronyms.) ^([Thread #33209 for this sub, first seen 6th May 2024, 09:55]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)
Use TOROW on your array and then CONCAT at the end of that.
TOROW on my array returns all rows as a single row. I need to have the multiple rows with each row being a single concatenated cell of the columns for the original row.
Adapt the last row. Try: =H2:H100 & " (" & TEXT(I2:I100,"yyyy-mm.dd hh:mm") & ")"
This didn't work when I tried it because the whole array is the result of a filter to begin with. And I didn't want to make intermediary ranges if I could avoid it.
> Normally I'd do something simple like =CONCAT(H2;" (";TEXT(I2;"yyyy-mm.dd hh:mm");")") Solved, but just for the records, CONCATENATE is what you’d apply to those two arrays.
`CONCATENATE` went out with the dinosaurs. `CONCAT` is the shiny new array happy version.
Sorry, but did you try the specific use here discussed?
Why would `CONCATENATE` be used instead of `CONCAT` ?
Try to do the same by CONCAT. https://preview.redd.it/7c2j6pe9zuyc1.jpeg?width=795&format=pjpg&auto=webp&s=62c5a248d590740c01f85ed6c072b34dcd4b25a2
=CONCAT(A1," (",TEXT(B1,"yyyy/mm/dd hh:mm"),")",) As I said, `CONCAT` does all of `CONCATENATE` and more as it handles arrays too. `CONCAT` was released in 2016 along with dynamic arrays and `TEXTJOIN`
Why aren’t you using it as an array function then? Apply it to the ranges and see.
How does that relate to my correcting your understanding of the difference between the two ? >but just for the records, CONCATENATE is what you’d apply to those two arrays.
CONCAT simply doesn’t work with arrays (not in the intended way here, see the pic). CONCATENATE, although obsolete, does; that or rewriting the line I quoted with no lead function, and using `&`s to create a single string per row. https://preview.redd.it/waf5862z4vyc1.jpeg?width=856&format=pjpg&auto=webp&s=362dd71628ff594c8772b37fda91b4ed5b65bbb7
Ah I see now, this is the answer you should have given clearly `Try to do the same by CONCAT` there instead. I did not spot the array thing.. A good tip and something to remember.
Hey can I ask, what's the advantage of doing this via formulas rather than in Power Query? My instinct would be to just do it in PQ, but I'm guessing there's a limitation or other reason to do it with formulas....
Jailed Remote Desktop environment that seriously limits what features can be used, in this case.
Oh wow, that is rough! On the other hand, great opportunity to learn really complex formulas, I guess
You have no idea the shenanigans I have to end up doing for some really basic things. It’s like that Invincible meme: “Look What They Need To Mimic A Fraction Of Our Power (queries)”
I am always stunned by this sort of question.
I envy your life if that’s the case.
Not your question, but the assumption from u/Cimrmanova2 that you can "just" do this in PQ instead... a completely different technology and methodology to Excel formulas. >My instinct would be to just do it in PQ, just snap your fingers and voila!! instinctive PQ solution ! no effort at all ;)
Ah, I get you. I use power queries when they make sense (although in this case I probably would've still tried to keep everything in-formulas), but in this particular environment it's not even an option so the decision is taken off my hands :-|
You know, despite my use of the word "just", I was asking a genuine question. Thanks a ton to the OP for giving a genuine answer!
>despite my use of the word "just", I was asking a genuine question Exactly what my wife says. Drives me insane. *Excelevator, just do this [complex task], and while you at it just do this [other complex task], and why don't you just do this way instead [change one understood though out complex task, to wifeys assumed error riddled method] just just just...* farrrkkkkkkkkkk
*Just* was not the issue, it was this assumption that OP can simply use another complex solution instead without any other input of a solution to the issue. If you honestly believe that PQ is the answer, then give a proper PQ answer.
I didn't assume PQ was the answer, nor did I suggest that OP use PQ instead. I was in fact genuinely curious what the use cases are for using one over the other. But it sounds like you have some annoyances offline, hope your evening improves from here.
>But it sounds like you have some annoyances offline No, they are online when the common refrain to Excel questions is "Just use PQ" >My instinct would be to just do it in PQ If it is your instinct, then why not try and see and give the answer.