array v concatenate including a ", " seperator

PaulBr2

New Member
Joined
Apr 16, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I am trying to write an array formula that is versatile to work a variable number of adjacent cells to be part of the array (click and drag to highlight is fine) but need to ensure each cell content is seperated with a comma then space before the next cell contents are included.
The issue I am having or trying to solve is that the concatenated content is from a variable number of adjacent cells. Which is why I was looking at array instead. but the seperator has to be a comma then space.
Any help for an excel dufus would be super appreciated....
1618570555814.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,339
Office Version
  1. 365
Platform
  1. Windows
In your image col V has True for every change in value of col A, are you saying that that is pure coincidence?

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

PaulBr2

New Member
Joined
Apr 16, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
In your image col V has True for every change in value of col A, are you saying that that is pure coincidence?

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
Hi Fluff, the content change in column a is consistent with the entries in column v and y, but the requirement to merge the specific content from column y into a single cell in column x separated by a comma space is dependent upon the true cell and then the empty cells below it. Then it restarts at the next true cell down. By adding $ symbols the formula is referring to a fixed range which is not relevant immediately you surpass that range. Could it be that I need to change the final reference in the formula to the last row reference entry on the spreadsheet?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,339
Office Version
  1. 365
Platform
  1. Windows
Can you post some sample data.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,339
Office Version
  1. 365
Platform
  1. Windows
Is this what you want?
+Fluff 1.xlsm
AUVWXY
1CountyWard
2CheshireTRUEDisleyDisley
3County DurhamTRUEWeardale, Delves Lane, Barnard Castle West, Consett North, Barnard Castle West, Annfield Plain, Weardale, Delves LaneWeardale
4County Durham Delves Lane
5County Durham Barnard Castle West
6County Durham Consett North
7County Durham Barnard Castle West
8County Durham Annfield Plain
9County Durham Weardale
10County Durham Delves Lane
11CumbriaTRUEAlston Moor, Ullswater, BoltonsAlston Moor
12Cumbria Ullswater
13Cumbria Boltons
14DerbyshireTRUECorbar, Limestone Peak, Temple, Corbar, Tideswell, Winster and South Darley, Matlock All SaintsCorbar
15Derbyshire Limestone Peak
16Derbyshire Temple
17Derbyshire Corbar
18Derbyshire Tideswell
19Derbyshire Winster and South Darley
20Derbyshire Matlock All Saints
21DevonTRUEMoorland, Combe MartinMoorland
22Devon Combe Martin
23GloucestershireTRUEBisleyBisley
24Greater ManchesterTRUESt James', Saddleworth North, Saddleworth NorthSt James'
25Greater Manchester Saddleworth North
26Greater Manchester Saddleworth North
27LancashireTRUEGoodshaw, Greensclough, Irwell, East Rural, Worsley, StacksteadsGoodshaw
28Lancashire Greensclough
29Lancashire Irwell
30Lancashire East Rural
31Lancashire Worsley
32Lancashire Stacksteads
Sheet1
Cell Formulas
RangeFormula
X2:X32X2=IF(V2=TRUE,TEXTJOIN(", ",,FILTER($Y$2:$Y$100,$A$2:$A$100=A2)),"")
 
Solution

PaulBr2

New Member
Joined
Apr 16, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Is this what you want?
+Fluff 1.xlsm
AUVWXY
1CountyWard
2CheshireTRUEDisleyDisley
3County DurhamTRUEWeardale, Delves Lane, Barnard Castle West, Consett North, Barnard Castle West, Annfield Plain, Weardale, Delves LaneWeardale
4County Durham Delves Lane
5County Durham Barnard Castle West
6County Durham Consett North
7County Durham Barnard Castle West
8County Durham Annfield Plain
9County Durham Weardale
10County Durham Delves Lane
11CumbriaTRUEAlston Moor, Ullswater, BoltonsAlston Moor
12Cumbria Ullswater
13Cumbria Boltons
14DerbyshireTRUECorbar, Limestone Peak, Temple, Corbar, Tideswell, Winster and South Darley, Matlock All SaintsCorbar
15Derbyshire Limestone Peak
16Derbyshire Temple
17Derbyshire Corbar
18Derbyshire Tideswell
19Derbyshire Winster and South Darley
20Derbyshire Matlock All Saints
21DevonTRUEMoorland, Combe MartinMoorland
22Devon Combe Martin
23GloucestershireTRUEBisleyBisley
24Greater ManchesterTRUESt James', Saddleworth North, Saddleworth NorthSt James'
25Greater Manchester Saddleworth North
26Greater Manchester Saddleworth North
27LancashireTRUEGoodshaw, Greensclough, Irwell, East Rural, Worsley, StacksteadsGoodshaw
28Lancashire Greensclough
29Lancashire Irwell
30Lancashire East Rural
31Lancashire Worsley
32Lancashire Stacksteads
Sheet1
Cell Formulas
RangeFormula
X2:X32X2=IF(V2=TRUE,TEXTJOIN(", ",,FILTER($Y$2:$Y$100,$A$2:$A$100=A2)),"")
Thanks, Sorted now. Much Appreciated.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,339
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

PaulBr2

New Member
Joined
Apr 16, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

So Fluff.. I am hoping you are not too exasperated with my dufusness from the other day, and you most certainly speeded up my process. However, I have a follow up question.
My devs have identified an issue with the file upload (image references) and I need to now remove the first image file reference from the concatenated cells. e.g.
https://cdn.shopify.com/s/files/1/0...rishing-coconut-oil-terre-de-mars-cuemars.jpg, https://cdn.shopify.com/s/files/1/0...k-soothing-camomile-terre-de-mars-cuemars.jpg, https://cdn.shopify.com/s/files/1/0...egradable-face-mask-terre-de-mars-cuemars.jpg

So is there a find and replace string, that can find the first reference up to the comma and delete it in favour of the remaining contents of the cell? Or will I need to reconsider the first concat solution you helped me with above to exclude the first reference?

Sorry again for dufusness, but I am working in a startup and haven't hired the biz analyst yet, who are my usual goto for this stuff. :)

Kindly in advance.. PaulBr2
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,339
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff 1.xlsm
AUVWXY
1CountyWard
2CheshireTRUE Disley
3County DurhamTRUEDelves Lane, Barnard Castle West, Consett North, Barnard Castle West, Annfield Plain, Weardale, Delves LaneWeardale
4County Durham Delves Lane
5County Durham Barnard Castle West
6County Durham Consett North
7County Durham Barnard Castle West
8County Durham Annfield Plain
9County Durham Weardale
10County Durham Delves Lane
11CumbriaTRUEUllswater, BoltonsAlston Moor
12Cumbria Ullswater
13Cumbria Boltons
14DerbyshireTRUELimestone Peak, Temple, Corbar, Tideswell, Winster and South Darley, Matlock All SaintsCorbar
15Derbyshire Limestone Peak
16Derbyshire Temple
17Derbyshire Corbar
18Derbyshire Tideswell
19Derbyshire Winster and South Darley
20Derbyshire Matlock All Saints
21DevonTRUECombe MartinMoorland
22Devon Combe Martin
23GloucestershireTRUE Bisley
24Greater ManchesterTRUESaddleworth North, Saddleworth NorthSt James'
25Greater Manchester Saddleworth North
26Greater Manchester Saddleworth North
27LancashireTRUEGreensclough, Irwell, East Rural, Worsley, StacksteadsGoodshaw
28Lancashire Greensclough
29Lancashire Irwell
30Lancashire East Rural
31Lancashire Worsley
32Lancashire Stacksteads
33
Staff
Cell Formulas
RangeFormula
X2:X32X2=IF(V2=TRUE,TEXTJOIN(", ",,FILTER($Y3:$Y$100,$A3:$A$100=A2,"")),"")
 

PaulBr2

New Member
Joined
Apr 16, 2021
Messages
10
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
you're a force of nature.. thank you... the only challenge is where there is only one image reference, it's missing those out, but a small price to pay for such a time save. Thanks!! Paul.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,339
Office Version
  1. 365
Platform
  1. Windows
If you want to keep the first value if there is only one, try
Excel Formula:
=LET(Fltr,FILTER($Y$2:$Y$100,$A$2:$A$100=A2,""),Rws,ROWS(Fltr),IF(V2=TRUE,TEXTJOIN(", ",,IF(Rws=1,Fltr,INDEX(Fltr,SEQUENCE(Rws-1,,2)))),""))
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,578
Messages
5,770,955
Members
425,653
Latest member
UNSING

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top