Can you help with concatenation?

[frank265]

Hi, I have a spreadsheet like the one below with data in the first two columns. I am trying to achieve the data in the third column. Currently I am doing this through manual concatenation, i.e. typing =concatenate(B1,",",B2,",",B3). etc. This is quite time consuming and prone to errors when working with a lot of data.

Is there a way to automate this, so excel can find each unique product and concatenate the relevant recipe. Or is there a better formula other than concatenation?

NB, it doesn't matter what seperates each recipe (comma, semi colon, pipe symbol etc), but there has to be a seperator as this is needed to identify each record later when importing to another system.

I would be super grateful for any help with this.

Thanks so much for reading.

Frank

Product	Recipes Using this Product	All Recipes Using Product
Chilli Oil	Chilli Con Carne	Chilli Con Carne,Spaghetti Bolognese,Egg Fried Rice
	Spaghetti Bolognese
	Egg Fried Rice
Avokado Oil	Avokado Salad	Avokado Salad,Mozzarella Salad
	Mozzarella Salad
Groundnut Oil	Stewed Chicken	Stewed Chicken
Rapeseed Oil	Zucchini Fritters	Zucchini Fritters,Plantain,Omelete
	Plantain
	Omelete

 

[alansidman]

Using Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Recipes Using this Product", type text}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Product"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Product"}, {{"Data", each _, type table [Product=text, Recipes Using this Product=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data], "Index", 1, 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Product", "Recipes Using this Product", "Index"}, {"Custom.Product", "Custom.Recipes Using this Product", "Custom.Index"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"Custom.Product"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns1", {{"Custom.Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns1", {{"Custom.Index", type text}}, "en-US")[Custom.Index]), "Custom.Index", "Custom.Recipes Using this Product"),
    #"Merged Columns" = Table.CombineColumns(#"Pivoted Column",{"1", "2", "3"},Combiner.CombineTextByDelimiter(", ", QuoteStyle.None),"All Recipes Using Product")
in
    #"Merged Columns"

Book12
	A	B
1	Product	All Recipes Using Product
2	Avokado Oil	Avokado Salad, Mozzarella Salad,
3	Chilli Oil	Chilli Con Carne, Spaghetti Bolognese, Egg Fried Rice
4	Groundnut Oil	Stewed Chicken, ,
5	Rapeseed Oil	Zucchini Fritters, Plantain, Omelete
Sheet4

 

[frank265]

Using Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Recipes Using this Product", type text}}),
    #"Filled Down" = Table.FillDown(#"Changed Type",{"Product"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Product"}, {{"Data", each _, type table [Product=text, Recipes Using this Product=text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Data], "Index", 1, 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Data"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns", "Custom", {"Product", "Recipes Using this Product", "Index"}, {"Custom.Product", "Custom.Recipes Using this Product", "Custom.Index"}),
    #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"Custom.Product"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Removed Columns1", {{"Custom.Index", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Removed Columns1", {{"Custom.Index", type text}}, "en-US")[Custom.Index]), "Custom.Index", "Custom.Recipes Using this Product"),
    #"Merged Columns" = Table.CombineColumns(#"Pivoted Column",{"1", "2", "3"},Combiner.CombineTextByDelimiter(", ", QuoteStyle.None),"All Recipes Using Product")
in
    #"Merged Columns"

Book12
	A	B
1	Product	All Recipes Using Product
2	Avokado Oil	Avokado Salad, Mozzarella Salad,
3	Chilli Oil	Chilli Con Carne, Spaghetti Bolognese, Egg Fried Rice
4	Groundnut Oil	Stewed Chicken, ,
5	Rapeseed Oil	Zucchini Fritters, Plantain, Omelete
Sheet4

Thanks so much, this looks like exactly what I want to achieve. I am totally new to Power Query, never used it before. Is there a way to copy this formula and implement it on my spreadsheet?

 

[Peter_SSs]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Until then, assuming that you have Excel 365 or Excel 2019 try this

20 07 06.xlsm
	A	B	C
1	Product	Recipes Using this Product	All Recipes Using Product
2	Chilli Oil	Chilli Con Carne	Chilli Con Carne,Spaghetti Bolognese,Egg Fried Rice
3		Spaghetti Bolognese
4		Egg Fried Rice
5	Avokado Oil	Avokado Salad	Avokado Salad,Mozzarella Salad
6		Mozzarella Salad
7	Groundnut Oil	Stewed Chicken	Stewed Chicken
8	Rapeseed Oil	Zucchini Fritters	Zucchini Fritters,Plantain,Omelete
9		Plantain
10		Omelete
11
Concat

Cell Formulas
Range	Formula
C2:C10	C2	=IF(A2="","",IF(A3<>"",B2,TEXTJOIN(",",1,B2:INDEX(B3:B$2000,IFERROR(MATCH("?*",A3:A$2000,0)-1,ROWS(A3:A$2000))))))

 

[frank265]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Until then, assuming that you have Excel 365 or Excel 2019 try this

20 07 06.xlsm
	A	B	C
1	Product	Recipes Using this Product	All Recipes Using Product
2	Chilli Oil	Chilli Con Carne	Chilli Con Carne,Spaghetti Bolognese,Egg Fried Rice
3		Spaghetti Bolognese
4		Egg Fried Rice
5	Avokado Oil	Avokado Salad	Avokado Salad,Mozzarella Salad
6		Mozzarella Salad
7	Groundnut Oil	Stewed Chicken	Stewed Chicken
8	Rapeseed Oil	Zucchini Fritters	Zucchini Fritters,Plantain,Omelete
9		Plantain
10		Omelete
11
Concat

Cell Formulas
Range	Formula
C2:C10	C2	=IF(A2="","",IF(A3<>"",B2,TEXTJOIN(",",1,B2:INDEX(B3:B$2000,IFERROR(MATCH("?*",A3:A$2000,0)-1,ROWS(A3:A$2000))))))

THANK YOU, THANK YOU, THANK YOU! That worked a charm. So grateful for the help, it's saved me a lot of time and nice that this will now be more accurate. Thanks again & I have updated my profile as advised. Hope you have a great day ahead

 

[Peter_SSs]

You're welcome - glad it worked for you.

.. and thanks for the profile update.

 

[alansidman]

With your worksheet open. Go to Data Tab. HIghlight your table. On Get and Transform. From Table/Range. Once the PQ editor opens. View. Advance Editor. Paste my Mcode into the Editor replacing what is there. Close and Load on the Home Tab.

Check out the link in my signature for more information and guidelines.