I think I'd actually create a new worksheet with just the Id and the codes for
the ones I wanted.

Then I could use =vlookup() to return that code.

=vlookup(a1,sheet2!a:b,2,false)

But if all the codes to keep are length 2 and all the codes to ignore are not
length 2, then put this in C1:

=INDEX($B$1:$B$8,MATCH(1,($A$1:$A$8=A8)*(LEN($B$1: $B$8)=2),0))

This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
correctly, excel will wrap curly brackets {} around your formula. (don't type
them yourself.)

Adjust the range to match--but you can't use the whole column.))

flub wrote:

Hi,

I need a little help, as I'm trying to help my father with a
spreadsheet but have got stuck. I have 2 Columns, TransactionID and
TransactionCode.

5 T
5 AA
6 H
6 BB
7 AA
7 T
8 T
8 BB

What I want to be able to do it to create/compute a 3rd column based on
the other 2. Now this is where I am getting stuck. The Rule is that for
a TransactionCode of T or H this needs to be changed to either a "AA"
or BB depending on what the other value is for the same Transaction ID.
ie this would be the final output.

5 T AA
5 AA AA
6 H BB
6 BB BB
7 AA AA
7 T AA
8 T BB
8 BB BB

The sort of code I was think was something like this pseudo code.

if Cx = C(x-1) and Bx = "AA" then = "AA"
if Cx = C(x-1) and Bx = "BB" then = "BB"
if Cx = C(x+1) and Bx = "AA" then = "AA"
if Cx = C(x+1) and Bx = "BB" then = "BB"

But I have no Idea how to make these references to a Row PLUS/MINUS the
one I am currently on.

Would someone be so kind as to help me?

Many Many thanks in advance.

Mike

Thanks.

--
flub
------------------------------------------------------------------------
flub's Profile: http://www.excelforum.com/member.php...o&userid=30322
View this thread: http://www.excelforum.com/showthread...hreadid=499850

--

Dave Peterson