Can you help a NEWBIE please

[flub]

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.

 

[Dave Peterson]

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.))

 

[Guest]

Assume your first row 5 and T
are in cells A2 and B2 respectfully..
In cell C2 enter:

=if(B2="T","AA",if(B2="H","BB",B2))
and copy down.

HTH,

 

[Dave Peterson]

Put this in C1:

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

(I copied from the wrong cell, sorry.)

 

[flub]

Assume your first row 5 and T
are in cells A2 and B2 respectfully..
In cell C2 enter:

=if(B2="T","AA",if(B2="H","BB",B2))
and copy down.

Thanks Jim. I did that and it NEARLY worked.The output was as shown
below.

Transaction ID COMP CODE FINAL CODE
5 T AA
5 AA AA
6 H BB
6 BB BB
7 AA AA
7 T AA
8 T AA
8 BB BB

As you can see Transcation ID 8 has a FINAL CODE of AA and BB, it
should be just BB for both.

 

[flub]

Put this in C1:

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

Dave Peterson

Dave I tried that and got a "#N/A".

I've uploaded my example file. It is very small


+-------------------------------------------------------------------+
|Filename: ExampleFLUB.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4200 |
+-------------------------------------------------------------------+

 

[Dave Peterson]

I don't use excelforum, so I don't see your attachment--but I wouldn't open the
workbook anyway.

I'd guess that you didn't array enter the formula.

 

[Guest]

Try this in column C2 and copy down.
=IF(B2="T",IF(A2=A3,B3,B1),IF(B2="H",IF(A2=A3,B3,B1),B2))
Not a very elegant solution but should work for you.

 

[Guest]

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

It might if someone answered your question(!). One
way to do it (example: in C3):

=OFFSET(C3,-1,0)

See the OFFSET Help text for explanation and options.

 

[Guest]

It might if someone answered your question(!). One
way to do it (example: in C3):
=OFFSET(C3,-1,0)

On second thought, if you can write C3, it is just as easy
to write C2 instead of the OFFSET(...) expression above.
But maybe OFFSET() will help you in some other way.
Sorry, I am not paying attention to your application.

 

[flub]

Thanks for that. When using the Offset function how do I reference th
current cell that the formula is in.

In your example you use C3 etc but what if I want to place a formula i
any cell. How do I reference the "current" cell

 

[Guest]

Thanks for that. When using the Offset function how
do I reference the current cell that the formula is in.

That's what I was going for originally. But I cannot remember,
if there is even a way. I hope someone will answer your
question.

In your example you use C3 etc but what if I want to
place a formula in any cell. How do I reference the
"current" cell.

I realized that you can simply refer to the current cell by
name. When you copy the formula, the relative reference
will be updated to reflect the new cell location. Won't that
work for you?

And that is when I realized, klunk!, that for the same reason,
there is really no need to use OFFSET() at all in this context
-- at least to the extent that I understand it (not much!).

If you start with "IF(C3 = C2, ...)" in C3, when you copy
that to C4, for example, it will be changed to "IF(C4 = C3, ...)".
If you copy it to Z7, it will be changed to "IF(Z7 = Z6, ...)".

But honestly, I am not sure if any of this meets your needs.
To be honest, I did not completely follow your requirements.
I suspect you want if-then-else contructs, not a sequence
of if-statements. But as I said before, I am not paying close
enough attention to your application to really comment.
I should not have "butted in", and I wouldn't have were it
not for the fact that you seem to be at a dead-end with the
other ideas. ("Killing an ant with a sledgehammer" comes
to mind. But again, perhaps I am simply not familiar enough
with your requirements.)

I am not thinking clearly. I just finished 3(!) hours of intense
exercise.