copyt

04-03-2012, 12:19 AM

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,

Thanks,

View Full Version : matching with a particular error

copyt

04-03-2012, 12:19 AM

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,

Thanks,

xld

04-03-2012, 12:59 AM

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

copyt

04-03-2012, 01:23 AM

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

(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

04-03-2012, 02:47 AM

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.

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

04-03-2012, 03:07 AM

@ Aussiebear (http://www.vbaexpress.com/forum/member.php?u=3907)

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, :)

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

04-03-2012, 03:09 AM

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.

#!/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;

#!/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;

copyt

04-03-2012, 11:08 PM

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.

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.

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.