Add all numbers in a range to every other number in the range

[Metserv]

Greetings.

I have a range of values in column A, I wish to add all numbers by all other numbers, i.e for a specific value in the range, I want to add it to all the other values in the range, and output the values in a new range.

For example for values 1,2,3,4 in range A2:A5, I want to output a new range the values;

1+2 =3
1+3 =4
1+4 =5
2+1 =3
2+3 =5
2+4 =6

e.t.c, beginning in range B2. No number can be added to itself.

Thank you in anticipation.

 

[Fotis1991]

Cross posting here.

Add all numbers in a range to every other number in the range

 

[cml19722000]

Try this...

https://www.box.com/s/gcbk5gb4n7bjxydbq908

 

[Metserv]

THANK YOU !! That worked absolutely perfectly.

Now, for a separate problem but of the same format, I want to have all the numbers added to each other, but different combinations, such as, for the numbers range 1,2,3,4,5 I want to have the following result:

1+2
1+2+3
1+2+3+4
1+2+4+4+5

e.t.c

I have tried to think it through but have hit a brick wall. Please advice.

My sincere apologies for cross-posting. Thank you in anticipation.

 

[cml19722000]

Try this for Q2...

https://www.box.com/s/gcbk5gb4n7bjxydbq908

 

[aru_aakash]

Hello CML,

Can you please post the solution from Box.com website. This site is not reachable from my PC. Thanks

 

[cml19722000]

As per your request...

Sub Example2()


lr1 = Cells(Rows.Count, "A").End(xlUp).Row
w = 1


Range("C:C").ClearContents


Cells(1, 3) = "Result"


For x = 2 To lr1 - 1
Cells(w + 1, 3) = "=SUM(R2C1:R[1]C[-2])"
w = w + 1
Next x
End Sub

 

[Metserv]

Greetings, CML

Thank you for your assistance, but that doesn't work, at least the way I hoped.It only adds all the other numbers to the first number. I hoped it would do the same for all the numbers.

By the way, I also realized that I may have given an inaccurate output range, my concern was to actually generate ALL possible combinations, i.e

For a range 1,2,3,4,5 I want an output range of:

1
1+2
1+3
1+4
1+5
1+2+3
1+2+4
1+2+5
1+3+4
1+3+5
1+4+5
2
2+1
.....................

and so on.

I had initially requested combinations of two, I now realize that I want ALL possible combinations, beginning with a single value.

I was thinking of formulating the problem as some sort of progression or series(arithmetic or geometric), but came up short.

I have had this issue for a while now, I REALLY hope you help me crack it.

Thanks in anticipation.

 

[cml19722000]

Sorry, I can't get you.

What is the logic after 2+5?
If you can provide the pattern for 5 variables, that will be great. Thanks.

 

[cml19722000]

Try this... https://www.box.com/s/gcbk5gb4n7bjxydbq908

Sub OneDimension()


Dim lr1, Z, x, w, y1 As Variant


lr1 = Cells(Rows.Count, "A").End(xlUp).Row
w = 2


Range("C:C").ClearContents
Cells(1, 3) = "Result"


For Z = 2 To lr1
Cells(w, 3) = Cells(Z, 1)
w = w + 1
'1st level combination
For x = 2 To lr1
If Cells(Z, 1) <> Cells(x, 1) Then
Cells(w, 3) = Cells(Z, 1) + Cells(x, 1)
w = w + 1
End If
Next x

'2nd level combination
For x = 2 To lr1 - 1
If Cells(Z, 1) <> Cells(x, 1) Then
y1 = x + 1
For y = y1 To lr1
If Cells(Z, 1) <> Cells(y, 1) Then
Cells(w, 3) = Cells(Z, 1) + Cells(x, 1) + Cells(y, 1)
w = w + 1
y1 = y1 + 1
End If
Next y
End If
Next x
Next Z


End Sub