Formula or code for combines numbers with comma

[Vishaal]

Hi All,

We have the following sheet

Excel 2010 32 bit

	A​	B​	C​
1​	​	​	​
2​	1​	25​	75​
3​	2​	36​	74​
4​	3​	65​	71​
5​	4​	45​	72​
6​	5​	82​	7​
7​	6​	36​	37​
8​	7​	59​	64​
9​	8​	54​	98​
10​	9​	52​	7​
11​	10​	14​	8​
12​	11​	58​	1​
13​	12​	47​	2​

Sheet: Sheet1

We want to combine the data B2:C13 in D2 and its paste special value in E2

something like this 25,36,65,45,82

data will be increase from B2:C160

 

[sandy666]

with Ex2010 and PowerQuery free add-in from MS site

B	C		List
25​	75​		25,36,65,45,82,36,59,54,52,14,58,47,75,74,71,72,7,37,64,98,7,8,1,2
36​	74​
65​	71​
45​	72​
82​	7​
36​	37​
59​	64​
54​	98​
52​	7​
14​	8​
58​	1​
47​	2​

[SIZE=1]// Table1
let
    Source = Table.ToColumns(Excel.CurrentWorkbook(){[Name="Table1"]}[Content]),
    C2T = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Extract = Table.TransformColumns(C2T, {"Column1", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    Temp1 = Table.AddColumn(Extract, "Custom", each 1),
    Group = Table.Group(Temp1, {"Custom"}, {{"Count", each _, type table}}),
    List = Table.AddColumn(Group, "List", each Table.Column([Count],"Column1")),
    ExtractComma = Table.TransformColumns(List, {"List", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
    ROC = Table.SelectColumns(ExtractComma,{"List"})
in
    ROC[/SIZE]

 

[Peter_SSs]

Another option is to go back to basics. only this option...

Yes, not very feasible since your range may extend to about 160 rows.

So options are sandy666's suggestion or the aconcat suggestion in post 2 or perhaps the following udf. This one assumes just 2 columns, equal in length and no empty cells (as per your sample).
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Function JoinEm(r As Range) As String
  JoinEm = Join(Application.Transpose(r.Columns(1).Value), ",") & "," & Join(Application.Transpose(r.Columns(2).Value), ",")
End Function

<b>Join</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:8pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:33px;" /><col style="width:33px;" /><col style="width:28px;" /><col style="width:477px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >B</td><td >C</td><td >D</td><td >E</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:10pt; text-align:right; ">25</td><td style="font-size:10pt; text-align:right; ">75</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; text-align:right; ">25,36,65,45,82,36,59,54,52,14,58,47,75,74,71,72,7,37,64,98,7,8,1,2</td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:10pt; text-align:right; ">36</td><td style="font-size:10pt; text-align:right; ">74</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:10pt; text-align:right; ">65</td><td style="font-size:10pt; text-align:right; ">71</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:10pt; text-align:right; ">45</td><td style="font-size:10pt; text-align:right; ">72</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:10pt; text-align:right; ">82</td><td style="font-size:10pt; text-align:right; ">7</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:10pt; text-align:right; ">36</td><td style="font-size:10pt; text-align:right; ">37</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:10pt; text-align:right; ">59</td><td style="font-size:10pt; text-align:right; ">64</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:10pt; text-align:right; ">54</td><td style="font-size:10pt; text-align:right; ">98</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:10pt; text-align:right; ">52</td><td style="font-size:10pt; text-align:right; ">7</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:10pt; text-align:right; ">14</td><td style="font-size:10pt; text-align:right; ">8</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:10pt; text-align:right; ">58</td><td style="font-size:10pt; text-align:right; ">1</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr><tr style="height:21px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-size:10pt; text-align:right; ">47</td><td style="font-size:10pt; text-align:right; ">2</td><td style="font-size:10pt; "> </td><td style="font-size:10pt; "> </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E2</td><td >=JoinEm(B2:C13)</td></tr></table></td></tr></table>

 

[Vishaal]

ok...we will check and update

thanks for your effort and help us

 

[mohadin]

Hi

Sub test()
    Dim lr, a, b, x
    lr = Cells(Rows.Count, 1).End(xlUp).Row - 1
    Cells(2, 3) = Join(Application.Transpose(Cells(2, 1).Resize(lr)), ",") _
                  & "," & Join(Application.Transpose(Cells(2, 2).Resize(lr)), ",")
End Sub

 

[Jon von der Heyden]

Hi

It would appear you asked this question here (and received an answer): https://www.excelforum.com/excel-fo...bine-the-numbers-from-cells-with-comma-2.html

In future please keep let us know where else you have asked the same question (and please provide link to those questions) so that our members can see what other solutions have been offered and indeed whether or not you already have the answer you are looking for.

Thanks.

 

[Vishaal]

sure, we have get the solution

thanks for your support

 

[Vishaal]

Hi

It would appear you asked this question here (and received an answer): https://www.excelforum.com/excel-fo...bine-the-numbers-from-cells-with-comma-2.html

In future please keep let us know where else you have asked the same question (and please provide link to those questions) so that our members can see what other solutions have been offered and indeed whether or not you already have the answer you are looking for.

Thanks.

and please provide link to those questions) so that our members can see what other solutions have been offered
great lines