LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 6
Default Selectively replace cells based on two ranges of criteria -nested IF() statements?

Help anyone? Did I scare you all away because the post is so long?
I was only trying to give a clear picture of my problem...

On May 20, 1:50 pm, MCSmarties wrote:
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 a
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!


 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Counting Criteria in Cells in Multiple Ranges WalterK Excel Worksheet Functions 4 May 20th 08 03:33 AM
Can I automatically add or delete cells based on IF statements Mike in OH Excel Discussion (Misc queries) 3 September 29th 06 01:02 AM
sum the values of a range based upon multiple ranges and criteria LiveIt... Excel Worksheet Functions 1 July 17th 06 09:23 PM
Replace a spreadsheets named cells/ranges with exact cell address. David McRitchie Excel Discussion (Misc queries) 0 September 28th 05 08:59 PM
Add cells from a range based on 2 conditions from 2 other ranges Kelly Excel Worksheet Functions 3 July 7th 05 07:40 PM


All times are GMT +1. The time now is 03:46 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"