Concatenate a long list

[gheyman]

I have a Table that will vary in size each time the workbook is used. In the first column of the table (ItemID), the user enters Part Numbers. I need to concatenate each part number with a "," in between each (no spaces). Is there a formula that can be used that adjusts to the size of the table. Sometimes the table is 10 rows sometimes over 100 rows.
Thanks for the help - very much appreciate your time.

 

[VBE313]

Assuming you have Office 365, you can use the formula =TEXTJOIN

For my example, I put "Test" and the row number for column A. In column B, the formula is

=TEXTJOIN(",",1,A:A)

 

[sandy666]

with Power Query aka Get&Transform

PartNumbers		PartNumbers
885		885,726,528,191,337,670,448,859,968,328,493,196,691,882,647,121
726
528
191
337
670
448
859
968
328
493
196
691
882
647
121

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Grp = Table.Group(Table.AddColumn(Source, "Custom", each 1), {"Custom"}, {{"Count", each _, type table}}),
    Extract = Table.TransformColumns(Table.AddColumn(Grp, "PartNumbers", each [Count][PartNumbers]), {"PartNumbers", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    TSC = Table.SelectColumns(Extract,{"PartNumbers"})
in
    TSC

and NO, this is NOT vba

 

[Peter_SSs]

@gheyman
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’)

Assuming you have Office 365, you can use the formula =TEXTJOIN

Office 365 or Excel 2019.

I have a Table

If that is a formal table then the following (adjust for correct table name) will automatically adjust to the size of the table.
If it is not a formal table and you have TEXTJOIN, why not make it into a formal table?

gheyman 2020-08-06 1.xlsm
	A	B	C	D	E
1	ItemID	Hdr 2	Hdr 3		Id1,Id2,Id3,Id6,Id7
2	Id1	4	6
3	Id2	5	6
4	Id3	6	5
5		6	0
6		7	5
7	Id6	9	4
8	Id7	7	3
9
10
Sheet1

Cell Formulas
Range	Formula
E1	E1	=TEXTJOIN(",",1,Table1[ItemID])

 

[gheyman]

Office 2016. Profile has been updated. Thanks!

 

[sandy666]

so post#3 is ok for you

 

[Peter_SSs]

Profile has been updated.

Thanks.

Another option would be this short user-defined function (post back if you need help with implementation).

Function IDList(r As Range) As String
  IDList = Replace(Join(Filter(Split("|" & Join(Application.Transpose(r), "|#|") & "|", "#"), "||", False), ","), "|", "")
End Function

You didn't confirm whether or not your table was a formal table so this time I have assumed not.
You said you may have say about 10 to 100 rows. If you allow for say 1000 as I have done below that should allow sufficiently for expansion.

gheyman 2020-08-06 1.xlsm
	A	B	C	D	E
1	ItemID	Hdr 2	Hdr 3		Id1,Id2,Id3,Id6,Id7
2	Id1	4	6
3	Id2	5	6
4	Id3	6	5
5		6	0
6		7	5
7	Id6	9	4
8	Id7	7	3
9
10
Sheet2

Cell Formulas
Range	Formula
E1	E1	=IDList(A2:A1000)