Concatenate with an If Statement?

lrchapman

New member
Joined
Dec 19, 2018
Messages
1
Reaction score
0
Points
0
Excel Version(s)
16.16
View attachment orders_export (7).xls

Hi there! Does anyone know a quick, easy formula to concatenate the colours that a person has chosen, separated by a comma, then a space, and then the next colour chosen? I want it to be that every person's choice is found in one cell and not in multiple cells. Do I need to use an IF statement? There could be 10 colours chosen per person so I don't want to keep having to choose the concatenate formula where I have to manually choose each cell to group together.

For ex. I want it to look like this:

Elena Kazakevic (cell A2) | Nude Pink, Grape, Yellow (cell C2)
Mallory McKewen (cell A2| Nude Pink, Grape, Sky (cell C5)
etc.

I have attached the file for anyone to review and provide their input!

Thank you!
Lana
 
For ex. I want it to look like this:
Elena Kazakevic (cell A2) | Nude Pink, Grape, Yellow (cell C2)
Mallory McKewen (cell A2| Nude Pink, Grape, Sky (cell C5)
etc.
Try
Code:
=CONCATENATE(A2;"|";B2;", ";B3;", ";B4)

or

=A2&"|"&B2&", "&B3&", "&B4
 

Attachments

  • lrchapman-navic9679.xls
    28.5 KB · Views: 5
why some people don't say thank you ?!!!!

For starters, you cannot open your query to view the Mcode or the PQ steps as the connection has been disabled. It would be helpful, if instead of just showing the results, you allowed someone to view the actual PQ steps. Perhaps you would like to reload the workbook with these functions viewable as a learning experience for others who are not so well versed in PQ.
 
@alansidman ok, thank you. If the guy who asked the question has any problem to understand the solution, he can ask for explanation. the guy disappeared ...
In my first comment, I advised him to learn PQ, then I uploaded the solution.
The connection is disabled ?!!! it's curious if you are using versions 2016, 365 or 2019 of Excel. Let me know if you want me to upload it again.
 
Yes. Very curious. I am using 2019. Would be great to see your MCode and/steps so I would appreciate it if you uploaded again.

As to the TY. This is very common in these type forums. I have been active on several for over 7 years and it is not unusual to never get a Thank you or even a response. Don't take it personally, just know that you are helping someone out there and feel good about that. Thank you for being part of this helping community.
 
Just realized why I could not see your query. I have recently installed 2019 and the default was to not allow connections. I have resolved that and was able to see your steps and MCode. Thank you. No need to re-upload.

Alan
 
Hi alansidman, hereafter the M code and the file re-uploaded. The trick in this solution is to use the M function Table.Column()


Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Name", type text}, {"Colour", type text}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Name"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Name"}, {{"All", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "All colors", each Table.Column([All], "Colour")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"All"}),
    #"Extracted Values" = Table.TransformColumns(#"Removed Columns", {"All colors", each Text.Combine(List.Transform(_, Text.From), ","), type text})
in
    #"Extracted Values"



View attachment orders_export (7) solution.xls


To learn more, visit my personal blog http://numidiabi.wordpress.com
 
Back
Top