matching with a particular error

[copyt]

Hello all, I would like to match (Ai+Aj) to An with a certain error and give the out put to column F, G, H as shown in the example file. could somebody help me with the code that can do this?

Thanks,

[Bob Phillips]

I don't get the error bit, how can you add in an error?

[copyt]

I am sorry, the error in the example file is in range D3 so in the equation should be D3 instead of C3. The error is a number that user can change manually.

(An + D2)- D3≤ Ai + Aj ≤ (An + D2) + D3


for example

if i = 2 , So

Ai will be 2, then the code will match 2 with Aj which can be any position where the sum between 2 + Aj is more or less the same with An (+/- the values assigned in D2 and D3).


(5 + 0.01) - 0.02 ≤ 2 + 3 ≤ (5 + 0.01) + 0.01

[Aussiebear]

There's something strange about the logic of this request.

The An value is equal to the sum of Ai value & Aj value in each row of the spreadsheet. So providing you always minus a value in the first part of the equation, and you always add both values in the third section then First section will always be lowers than the second second which will always be lower than the third section.

So you effectively double the values in F2,G2 + H2. by adding themselves to themselves...... (unless the data supplied is co-incidental).

Then the possible formula could be something along the lines like the following

=If(And((An+D2-D3)<(Ai +Aj),(Ai +Aj)<(An + D2+ D3) Then
Cell F2 = F2+Ai,
Cell G2 = G2+Aj,
Cell H2 = H2 + An

(I couldn't find the Smaller than or Equal to sign, but you'll get my drift.)

However I'm lost when I try to follow the logic of the spreadsheet. Are you wanting the process to go through each line until there is no more data?

Maybe someone will make sense of this better than I can.

[copyt]

@ Aussiebear

Thank you for your response, data in F2, G2 and H2 in the example file are just the example out put. My idea is just finding a certain number that it matches to another number and then the sum between first and second numbers is (more or less) equal to the third number. All numbers are in the same column. If there is a matched then separately add the first, second and the third number in particular columns and loop until the last number in the column. Thanks,

[copyt]

I need VBA but my friend who knows Perl but VBA created a code that can do the same work. so if anybody has some idea about perl please help me to transcode. Any help would be appreciated.

[VBA]#!/usr/bin/perl
$constant = 0.95;
$b1 = 0.02;
$n=0;
@a=split "\n", `cat file_a`;
@c=();
@d=();
@e=();
for($i=0;$i<@a;$i++)
{
for($j=0;$j<@a;$j++){
for($k=$j;$k<@a;$k++){
if ((($a[$i]-($constant+$b1))<=($a[$j]+$a[$k])) && (($a[$j]+$a[$k])<=($a[$i]+($constant+$b1))))
{$c[$n]=$a[$j];
$d[$n]=$a[$k];
$e[$n]=$a[$i];
$n++;}
}
}
}
open F, ">outputfile";
for ($i=0;$i<@c;$i++)
{
print F "$c[$i] $d[$i] $e[$i]\n";
}
close F;[/VBA]

[copyt]

I have a code that works only when it's separated to be 2 equations. If I combine them as a single code (shown below) it doesn't give a correct out put.

for example,
In case that
Cells(1, 3) = 0
Cells(2, 3) = 0

Data in column A
A1 = 2
A2 = 3
A3 = 4

So the expected out put will be only 4,2,2 <---Cells(i, 1) & Cells(x, 1) & Cells(j, 1)

Sub Looping()
finalrow = Cells(Rows.Count, 1).End(xlUp).Row
For x = 1 To finalrow
    For j = 1 To finalrow
        For i = 1 To finalrow
            If (Cells(i, 1) + Cells(1, 3) - Cells(2, 3)) <= (Cells(x, 1) + Cells(j, 1)) <= (Cells(i, 1) + Cells(1, 3) + Cells(2, 3)) = True Then
                MsgBox "hello" & Cells(i, 1) & Cells(x, 1) & Cells(j, 1)
            End If
        Next I
    Next j
Next x
End Sub

Could anybody tell me what's wrong with the code? Any help would be appreciated.