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

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

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

Dave Peterson wrote:

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

--

Dave Peterson

Dave Peterson Wrote:
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 |
+-------------------------------------------------------------------+

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

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.



flub wrote:

Dave Peterson Wrote:
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 |
+-------------------------------------------------------------------+

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

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,


"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

Jim May Wrote:
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
------------------------------------------------------------------------
flub's Profile: http://www.excelforum.com/member.php...o&userid=30322
View this thread: http://www.excelforum.com/showthread...hreadid=499850

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



"flub" wrote:


Jim May Wrote:
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
------------------------------------------------------------------------
flub's Profile: http://www.excelforum.com/member.php...o&userid=30322
View this thread: http://www.excelforum.com/showthread...hreadid=499850

"flub" wrote:
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.

I wrote:
"flub" wrote:
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)

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.

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

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.


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

"flub" wrote:
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.