Formula or code for combines numbers with comma

V

Vishaal

Well-known Member
Joined
Mar 16, 2019
Messages
543
Office Version
  1. 2010
  2. 2007
Platform
  1. Windows
  2. Web
Hi All,

We have the following sheet

Excel 2010 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td]
​
[/td][td]
​
[/td][td]
​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]
1​
[/td][td]
25​
[/td][td]
75​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]
2​
[/td][td]
36​
[/td][td]
74​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]
3​
[/td][td]
65​
[/td][td]
71​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]
4​
[/td][td]
45​
[/td][td]
72​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]
5​
[/td][td]
82​
[/td][td]
7​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]
6​
[/td][td]
36​
[/td][td]
37​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]
7​
[/td][td]
59​
[/td][td]
64​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td]
8​
[/td][td]
54​
[/td][td]
98​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td]
9​
[/td][td]
52​
[/td][td]
7​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td]
10​
[/td][td]
14​
[/td][td]
8​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td]
11​
[/td][td]
58​
[/td][td]
1​
[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13​
[/td][td]
12​
[/td][td]
47​
[/td][td]
2​
[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

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
 
Sort by date Sort by votes
with Ex2010 and PowerQuery free add-in from MS site

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]B[/td][td=bgcolor:#5B9BD5]C[/td][td][/td][td=bgcolor:#70AD47]List[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
25​
[/td][td=bgcolor:#DDEBF7]
75​
[/td][td][/td][td=bgcolor:#E2EFDA]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=bgcolor:#FFFFFF][td]
36​
[/td][td]
74​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
65​
[/td][td=bgcolor:#DDEBF7]
71​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
45​
[/td][td]
72​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
82​
[/td][td=bgcolor:#DDEBF7]
7​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
36​
[/td][td]
37​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
59​
[/td][td=bgcolor:#DDEBF7]
64​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
54​
[/td][td]
98​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
52​
[/td][td=bgcolor:#DDEBF7]
7​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
14​
[/td][td]
8​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]
58​
[/td][td=bgcolor:#DDEBF7]
1​
[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]
47​
[/td][td]
2​
[/td][td][/td][td][/td][/tr]
[/table]

Code: 
[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]
 
Last edited:
Upvote 0

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Vishaal said:
Another option is to go back to basics. :smile: only this option...:rofl:
Click to expand...
Yes, not very feasible since your range may extend to about 160 rows. :biggrin:

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)

Rich (BB code): 
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>
 
Upvote 0
ok...we will check and update

thanks for your effort and help us
 
Upvote 0
Hi
Code: 
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
 
Upvote 0
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.
 
Upvote 0
sure, we have get the solution

thanks for your support
 
Upvote 0
Jon von der Heyden said:
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.
Click to expand...

and please provide link to those questions) so that our members can see what other solutions have been offered
great lines
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question Question
copy from source sheet1 to destination sheet2 vba code
Replies
1
Views
250
Vishaal
V
P
  • Question Question
Help with Vba Code
Replies
3
Views
463
Coyotex3
C
L
  • Solved
VBA remove leading zeros
2
Replies
14
Views
2K
Joe4
Joe4
D
  • Solved
Code to move rows to a new sheet.
Replies
4
Views
705
dandee14k
D
D
  • Question Question
Tally that shifts cells daily.
Replies
3
Views
789
portews
P

Forum statistics

Threads
1,223,898
Messages
6,175,272
Members
452,628
Latest member
dd2

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