simplification problem (only for expert!)

[Mark]

Hi,
I struggling with the following problem.
I have in two column data:
column1 column2
200 10
30 50
50 40
10 20
....etc. thousand records

I'd like find max sum (two elements) of column1.
Nacessary condition: sum of these two element in column2

70.

E.G. satisfy condition is:
sum of column1: 30+50 = 80
becouse condition is fulfil, sum of column2: 50+40 >70

How resolve this task in VBA by selection and iteration??
Many thanks in anticipation.
Regards
Mark

 

[Frank Kabel]

Hi
why not use a formula for this?. e.g.
=SUM(LARGE(A1:A100,{1,2}))

 

[Mark]

becouse exist just nacessary condition...
read carefully my post
i'd like resolve in VBA..

 

[Frank Kabel]

Hi
the worksheet formula is simply to adapt:
=IF(SUM(LARGE(A1:a100,{1,2}))>70,SUM(LARGE(A1:a100,
{1,2})),"no sum >70 found")

And in VBA you could use the same approach without
iterating through the range. e.g.
Sub find_large_sum()
Dim rng As Range
Dim result
Set rng = ActiveSheet.Range("A:A")
With Application.WorksheetFunction
result = .Large(rng, 1) + .Large(rng, 2)
End With
If result > 70 Then
MsgBox "Result is: " & result
Else
MsgBox "no sum larger than 70 found"
End If
End Sub

 

[Mark]

no no no!
it's complicated task!
Your function is abnormal.. don't find in column2
condition.
Frank sum exist always - look my expample in early post!
VBA must doing sum! If sum of column2 is <70 then look
another element in column1 perform condition.
Sum of two elements in column1 must be optimal max! ! !
Resolve my problem is by selection and iteration, but i
don't know how.
Regards
mark

 

[Tom Ogilvy]

Can the data be sorted?

--
Regards,
Tom Ogilvy

Hi,
I struggling with the following problem.
I have in two column data:
column1 column2
200 10
30 50
50 40
10 20
...etc. thousand records

I'd like find max sum (two elements) of column1.
Nacessary condition: sum of these two element in column2
E.G. satisfy condition is:
sum of column1: 30+50 = 80
becouse condition is fulfil, sum of column2: 50+40 >70

How resolve this task in VBA by selection and iteration??
Many thanks in anticipation.
Regards
Mark

 

[Mark]

it need recurrence..

-----Original Message-----
Hi
the worksheet formula is simply to adapt:
=IF(SUM(LARGE(A1:a100,{1,2}))>70,SUM(LARGE(A1:a100,
{1,2})),"no sum >70 found")

And in VBA you could use the same approach without
iterating through the range. e.g.
Sub find_large_sum()
Dim rng As Range
Dim result
Set rng = ActiveSheet.Range("A:A")
With Application.WorksheetFunction
result = .Large(rng, 1) + .Large(rng, 2)
End With
If result > 70 Then
MsgBox "Result is: " & result
Else
MsgBox "no sum larger than 70 found"
End If
End Sub


.

 

[Mark]

Hi TOM!!!
Data can't be sort..there are thousands.
I'd like resolve in VBA (by recurrence).
Best regards
mark

 

[Jarek]

Is that what you are looking for?
Example for data in A2:B100

Sub test()
Dim cell1 As Range, cell2 As Range

For Each cell1 In Range("B2:B100")
For Each cell2 In Range("B2:B100")
If cell1 + cell2 > 70 And cell1.Address <> cell2.Address Then
max_sum = Application.Max(max_sum, cell1.Offset(0, -1)
cell2.Offset(0, -1))
End If
Next cell2
Next cell1
MsgBox "Maximum of two elements in column A is " & max_sum
End Sub

Jare

 

[NickHK]

Mark,
What's wrong with this, starting in A1:
200 10
30 50 =IF(AND((A1+A2)>70,(B1+B2)>70),A1+A2,0)
50 40 =IF(AND((A2+A3)>70,(B2+B3)>70),A2+A3,0)
10 20 =IF(AND((A3+A4)>70,(B3+B4)>70),A3+A4,0)
=MAX(C2:C4)

 

[Frank Kabel]

Hi
now I'm confused. First you asked for the maximum sum in
ONE column. Could you please explain again on a set of
example data what you're trying to do and what you mean
with recurrence

 

[Tom Ogilvy]

it only looks at sums of adjacent cells.

 

[Dana DeLouis]

My guess would be that it would be hard to do without sorting in this case.
The number of comparison's becomes large with 1000's of records. Here is
just an idea. If you can, I would attempt to sort on Column 1. Here, I
assume your data is in A1:B20.
Column C is an array formula that looked for numbers that Sum >=70, and
picked the value from column A. Column D Sums 2 numbers, and E1 hopefully
will be the Max of those Sums. This is not fully tested, but may give you
an idea.

Sub Demo()
Dim Rng As Range

For Each Rng In [C1:C20].Cells
Rng.FormulaArray = _
"=MAX(IF(R[1]C[-1]:R20C2>=70-RC[-1],R[1]C[-2]:R20C1,0))"
Next Rng

[D120].FormulaR1C1 = "=RC[-3]+RC[-1]"
[E1].FormulaArray = "=MAX(IF(RC[-2]:R[19]C[-2]<>0,RC[-1]:R[1]C[-1]))"
End Sub

HTH
Dana DeLouis

 

[Mark]

Hi Frank!
See Jarek's post, there is solution of my simplification
problem. Sum of column1 has to be max, but condition (sum
of elements in column2 >70) must by perform too.

Thanks for your engage!
Best Regards!
mark

 

[Mark]

Dear EXPERT!
You be at home in VBA!
My simplification problem (warm-up)has resolved.
I have wanted to show selected elements and i have written
after line "max_sum" code:

If max_s <> max_sum Then
Cells(2, 4) = cell1.Offset(0, -1)
Cells(3, 4) = cell2.Offset(0, -1)
Cells(2, 5) = cell1
Cells(3, 5) = cell2
max_s = max_sum
End If

My problem is complex. I'd like to show you a level highly:
column1 column2 column3 column4
1 A 200 10
1 A 30 50
1 B 40 40
2 A 50 40
2 B 10 20
2 A 50 50

.....etc. thousand records

For each name (A, B, and someone else) in column2 i
looking for optimal sum_max of two elements in column3.
Nacessary conditions (important):
-sum of these two elements in column4 >70.
-these two elements can't have the same number in column1

Result could be show in another sheet in shape:

Name in column2 "Sum_max is" .....
name of column1 name of column3 name of column4
element of col1 element of col3 element in col4
element of col1 element of col3 element in col4
Sum of two elements Sum of two elements

e.g.(hypothetical):

A Sum_max is 1200
column1 column3 column4
1 500 50
5 700 30
1200 80


below next names of column2
B ......


I would be very happy if you could help me in this.
Would you be possible use in this task table's variable?

Best wishes for Jarek
mark

 

[Jarek]

Hi,
not very elegant, not fully tested, but may work.
Assume your data in columns A
column A = not equal elements
column B = names
column C = elements to maximize
column D = condition elements (>70)

Sub test()
Dim cell1 As Range, cell2 As Range, column2_name As Range

Columns("F:J").Clear
Range(Range("B1"), Range("B1").End(xlDown)).AdvancedFilte
Action:=xlFilterCopy, CopyToRange:=Range( _
"F1"), Unique:=True
Range("F1:J1") = Array("name", "max_sum", "max_elements"
"cond_elements", "from rows")

i = 0
For Each column2_name In Range(Range("F2"), Range("F2").End(xlDown))
For Each cell1 In Range(Range("D2"), Range("D2").End(xlDown))
If cell1.Offset(0, -2) = column2_name Then
For Each cell2 In Range(Range("D2")
Range("D2").End(xlDown))
If cell2.Offset(0, -2) = column2_name Then
If cell1 + cell2 > 70 And cell1.Address <
cell2.Address And cell1.Offset(0, -3) <> cell2.Offset(0, -3) Then
If cell1.Offset(0, -1) + cell2.Offset(0, -1)
max_sum Then
max_sum = cell1.Offset(0, -1)
cell2.Offset(0, -1)
col3_values = cell1.Offset(0, -1) & " & "
cell2.Offset(0, -1)
col4_values = cell1 & " & " & cell2
rows_numb = cell1.Row & " & " & cell2.Row
End If
End If
End If
Next cell2
End If
Next cell1
Range("G2:J2").Offset(i) = Array(max_sum, col3_values, col4_values
rows_numb)
i = i + 1
max_sum = 0
col3_values = ""
col4_values = ""
rows_numb = ""
Next column2_name

End Sub


Jare

 

[John]

It seems best to work backwards and start by finding all the dyads in
column 4 that sum to > 70 then test the column 1 and then the a's & b's
etc. That would be a pretty simple for/next loop. Of course, you better
have a fast processor if the number of tests is high.

Like below except I didn't pay attention to the A's and B's and its in
rudimentary basic not vb.

If you knew something about the range of values in column 4 you could
probably speed things up a lot.

John

For n = 1 to length of column 4
For m = n+1 to length of column 4
IF Column4(n) + Coloumn4(m) > 70 Then
IF Coloumn4(n) <> Column1(n) or Column1(m) AND Coloumn4(m) <>
Column1(n) or Column1(m) THEN

TestSum = Column3(n) + Column3(m)
IF TestSum > Oldsum Then
Best_n = n : Best_m = M
Oldsum = TestSum

End if
End IF
End IF
Next

Print "The max sum is" Oldsum " of items" n","m

 

[Mark]

Hi Jarek!

Your smart code show result, but itsn't optimalize sum
like it was in your reply early.
I will remind about my problem in new post (soon).
Best regards
mark