array v concatenate including a ", " seperator

P

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
 
Sort by date Sort by votes
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.
 
Upvote 0

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Fluff said:
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.
Click to expand...
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?
 
Upvote 0
Can you post some sample data.
 
Upvote 0
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)),"")
 
Upvote 0
Solution
Fluff said:
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)),"")
Click to expand...
Thanks, Sorted now. Much Appreciated.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
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
 
Upvote 0
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,"")),"")
 
Upvote 0
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.
 
Upvote 0
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)))),""))
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
64 Bit Array Formula
Replies
5
Views
328
RoryA
RoryA
S
  • Question
Trendline values using Dynamic Range in Excel - comparing with LINEST function
Replies
1
Views
194
Domenic
D
U
  • Solved
Newbie Seeking Help with Ranges, Loops, and Select
2 3
Replies
27
Views
339
USNA91
U
C
  • Question
How to Delete "Blank" Cells En Masse So They Are Truly Empty?
2 3
Replies
23
Views
2K
chasfh
C
D
  • Question
Include Path's with more than 260 characters
Replies
4
Views
587
Bosquedeguate
Bosquedeguate

Forum statistics

Threads
1,214,792
Messages
6,121,612
Members
449,038
Latest member
apwr

Share this page

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
Back
Top