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!