M
MCSmarties
Hello,
I need to process tables containing somewhat ambiguous data.
Briefly, I want to replace specific positions in a cell
by a "wildcard" character if certain conditions apply.
Let me illustrate and I apologize for being verbose!
(you may need to display this post in courier to preserve the
formatting)
-MOST data I have in my table is in the following format:
4 HN 4 HB2 2.308 0.608 5.692
26 HN 26 HB2 2.478 0.768 0.768
e.g. 3 discrete coordinates that apply to separate data references.
-However, some of the data is "ambiguous" in the sense that the
coordinates apply to several references.
The data is currently represented in such a case as follows:
17 HN 16 HG22 2.136 0.570 0.570
17 HN 16 HG21 0 0 0
17 HN 16 HG23 0 0 0
What I want to do is to rewrite the table while modifying the
"top" line (with coordinates) in this ambiguous statement to:
17 HN 16 HG2# 2.136 0.570 0.570
I have managed to do solve the problem for MOST cases by adding
a serial number in the last column to distinguish between
"unambiguous" (0), "top line of an ambiguous statement" (1) and
"ambiguous" (2) data and then including this information in
formulae.
Specifically, what I now have is something like:
(sheet 1):
A B C D E F G H
-----------------------------------------------
1| 4 HN 4 HB2 2.308 0.608 5.692 0
2| 26 HN 26 HB2 2.478 0.768 0.768 0
3| 17 HN 16 HG22 2.136 0.570 0.570 1
4| 17 HN 16 HG21 0 0 0 2
5| 17 HN 16 HG23 0 0 0 2
6| 43 HE1 43 HZ 2.127 0.566 0.566 1
7| 43 HE2 43 HZ 0 0 0 2
with the values in column H assigned by:
H1=if(E1+F1+G1=0,2,if(E2+F2+G2=0,1,0))
(in sheet2):
A B C D E F G
-------------------------------------------
1| 4 HN 4 HB2 2.308 0.608 5.692
1| 26 HN 26 HB2 2.478 0.768 0.768
3| 17 HN 16 HG2# 2.136 0.570 0.570
4| 17 HN 16 HG21 0 0 0
5| 17 HN 16 HG23 0 0 0
6| 43 HE# 43 HZ 2.127 0.566 0.566
7| 43 HE2 43 HZ 0 0 0
A1=sheet1!A1
B1=if($H1=1,if(A2=A1,A1,if(iserr(value(right(A1))),A1,concatenate(left(A1,len(A1)-1),"#"))),A1)
C1=sheet1!C1
D1=if($H1=1,if(D2=D1,D1,if(iserr(value(right(D1))),D1,concatenate(left(D1,len(D1)-1),"#"))),D1)
E1=sheet1!E1
F1=sheet1!F1
G1=sheet1!G1
Notice how cells D3 and B6 have changed!
I can now simply sort this new table to get rid of the useless rows
4,5 and 7.
Why do I need your help?
------------------------
Because this method doesn't work in some cases and I need a more
"universal"
solution to avoid having to double-check gobs of output manually!
As you can see, my method compares 2 lines at a time.
Problems arise when I have data like for example:
(sheet1):
A B C D E F G H
----------------------------------------------
1| 75 HD1 63 HE1 1.899 0.451 0.451 1
2| 75 HD1 63 HE2 0 0 0 2
3| 75 HD2 63 HE1 0 0 0 2
4| 75 HD2 63 HE2 0 0 0 2
With my method, I get (sheet2):
A B C D E F G
------------------------------------------
1| 75 HD1 63 HE# 1.899 0.451 0.451
2| 75 HD1 63 HE2 0 0 0
3| 75 HD2 63 HE1 0 0 0
4| 75 HD2 63 HE2 0 0 0
What I WANT is:
A B C D E F G
------------------------------------------
1| 75 HD# 63 HE# 1.899 0.451 0.451
2| 75 HD1 63 HE2 0 0 0
3| 75 HD2 63 HE1 0 0 0
4| 75 HD2 63 HE2 0 0 0
(note the value in cell B1!)
The problem appears to be that only lines 1+2 are compared,
but the formula would need to take lines 3+4 into account as well.
I need to specify something like:
"while (columnA)=(A1) and (columnH)=2, apply (formula)"
e.g. that the formula compares ALL "ambiguous" statements
for a particular serial number - here, 75.
I need to carry out this "cleanup" in many tables containing
up to 10'000 lines each. A solution involving only formulae
(no macros) would be preferred but is not essential.
This obscure dataset comes from the output of a molecular structure
analysis program (they are ambiguous proton-proton distance
restraints).
Possible values for columns A and C range from 1 to about 150.
Possible values for columns B and D are:
Hx, Hx? and Hx??, where x={A,B,D,E,G,H,N,Z} and ?={1,2,3}
I know it's a tricky question, I'd be very grateful for any help you
can give!
I need to process tables containing somewhat ambiguous data.
Briefly, I want to replace specific positions in a cell
by a "wildcard" character if certain conditions apply.
Let me illustrate and I apologize for being verbose!
(you may need to display this post in courier to preserve the
formatting)
-MOST data I have in my table is in the following format:
4 HN 4 HB2 2.308 0.608 5.692
26 HN 26 HB2 2.478 0.768 0.768
e.g. 3 discrete coordinates that apply to separate data references.
-However, some of the data is "ambiguous" in the sense that the
coordinates apply to several references.
The data is currently represented in such a case as follows:
17 HN 16 HG22 2.136 0.570 0.570
17 HN 16 HG21 0 0 0
17 HN 16 HG23 0 0 0
What I want to do is to rewrite the table while modifying the
"top" line (with coordinates) in this ambiguous statement to:
17 HN 16 HG2# 2.136 0.570 0.570
I have managed to do solve the problem for MOST cases by adding
a serial number in the last column to distinguish between
"unambiguous" (0), "top line of an ambiguous statement" (1) and
"ambiguous" (2) data and then including this information in
formulae.
Specifically, what I now have is something like:
(sheet 1):
A B C D E F G H
-----------------------------------------------
1| 4 HN 4 HB2 2.308 0.608 5.692 0
2| 26 HN 26 HB2 2.478 0.768 0.768 0
3| 17 HN 16 HG22 2.136 0.570 0.570 1
4| 17 HN 16 HG21 0 0 0 2
5| 17 HN 16 HG23 0 0 0 2
6| 43 HE1 43 HZ 2.127 0.566 0.566 1
7| 43 HE2 43 HZ 0 0 0 2
with the values in column H assigned by:
H1=if(E1+F1+G1=0,2,if(E2+F2+G2=0,1,0))
(in sheet2):
A B C D E F G
-------------------------------------------
1| 4 HN 4 HB2 2.308 0.608 5.692
1| 26 HN 26 HB2 2.478 0.768 0.768
3| 17 HN 16 HG2# 2.136 0.570 0.570
4| 17 HN 16 HG21 0 0 0
5| 17 HN 16 HG23 0 0 0
6| 43 HE# 43 HZ 2.127 0.566 0.566
7| 43 HE2 43 HZ 0 0 0
A1=sheet1!A1
B1=if($H1=1,if(A2=A1,A1,if(iserr(value(right(A1))),A1,concatenate(left(A1,len(A1)-1),"#"))),A1)
C1=sheet1!C1
D1=if($H1=1,if(D2=D1,D1,if(iserr(value(right(D1))),D1,concatenate(left(D1,len(D1)-1),"#"))),D1)
E1=sheet1!E1
F1=sheet1!F1
G1=sheet1!G1
Notice how cells D3 and B6 have changed!
I can now simply sort this new table to get rid of the useless rows
4,5 and 7.
Why do I need your help?
------------------------
Because this method doesn't work in some cases and I need a more
"universal"
solution to avoid having to double-check gobs of output manually!
As you can see, my method compares 2 lines at a time.
Problems arise when I have data like for example:
(sheet1):
A B C D E F G H
----------------------------------------------
1| 75 HD1 63 HE1 1.899 0.451 0.451 1
2| 75 HD1 63 HE2 0 0 0 2
3| 75 HD2 63 HE1 0 0 0 2
4| 75 HD2 63 HE2 0 0 0 2
With my method, I get (sheet2):
A B C D E F G
------------------------------------------
1| 75 HD1 63 HE# 1.899 0.451 0.451
2| 75 HD1 63 HE2 0 0 0
3| 75 HD2 63 HE1 0 0 0
4| 75 HD2 63 HE2 0 0 0
What I WANT is:
A B C D E F G
------------------------------------------
1| 75 HD# 63 HE# 1.899 0.451 0.451
2| 75 HD1 63 HE2 0 0 0
3| 75 HD2 63 HE1 0 0 0
4| 75 HD2 63 HE2 0 0 0
(note the value in cell B1!)
The problem appears to be that only lines 1+2 are compared,
but the formula would need to take lines 3+4 into account as well.
I need to specify something like:
"while (columnA)=(A1) and (columnH)=2, apply (formula)"
e.g. that the formula compares ALL "ambiguous" statements
for a particular serial number - here, 75.
I need to carry out this "cleanup" in many tables containing
up to 10'000 lines each. A solution involving only formulae
(no macros) would be preferred but is not essential.
This obscure dataset comes from the output of a molecular structure
analysis program (they are ambiguous proton-proton distance
restraints).
Possible values for columns A and C range from 1 to about 150.
Possible values for columns B and D are:
Hx, Hx? and Hx??, where x={A,B,D,E,G,H,N,Z} and ?={1,2,3}
I know it's a tricky question, I'd be very grateful for any help you
can give!