software

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.

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

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 quot;AAquot;
or BB depending on what the other value is for the same Transaction ID.
ie this would be the final output.

5TAA
5AAAA
6HBB
6BBBB
7AAAA
7TAA
8TBB
8BBBB

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

if Cx = C(x-1) and Bx = quot;AAquot; then = quot;AAquot;
if Cx = C(x-1) and Bx = quot;BBquot; then = quot;BBquot;
if Cx = C(x 1) and Bx = quot;AAquot; then = quot;AAquot;
if Cx = C(x 1) and Bx = quot;BBquot; then = quot;BBquot;

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: www.excelforum.com/member.php...oamp;userid=30322
View this thread: www.excelforum.com/showthread...hreadid=499850I 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:
gt;
gt; Hi,
gt;
gt; I need a little help, as I'm trying to help my father with a
gt; spreadsheet but have got stuck. I have 2 Columns, TransactionID and
gt; TransactionCode.
gt;
gt; 5 T
gt; 5 AA
gt; 6 H
gt; 6 BB
gt; 7 AA
gt; 7 T
gt; 8 T
gt; 8 BB
gt;
gt; What I want to be able to do it to create/compute a 3rd column based on
gt; the other 2. Now this is where I am getting stuck. The Rule is that for
gt; a TransactionCode of T or H this needs to be changed to either a quot;AAquot;
gt; or BB depending on what the other value is for the same Transaction ID.
gt; ie this would be the final output.
gt;
gt; 5 T AA
gt; 5 AA AA
gt; 6 H BB
gt; 6 BB BB
gt; 7 AA AA
gt; 7 T AA
gt; 8 T BB
gt; 8 BB BB
gt;
gt; The sort of code I was think was something like this pseudo code.
gt;
gt; if Cx = C(x-1) and Bx = quot;AAquot; then = quot;AAquot;
gt; if Cx = C(x-1) and Bx = quot;BBquot; then = quot;BBquot;
gt; if Cx = C(x 1) and Bx = quot;AAquot; then = quot;AAquot;
gt; if Cx = C(x 1) and Bx = quot;BBquot; then = quot;BBquot;
gt;
gt; But I have no Idea how to make these references to a Row PLUS/MINUS the
gt; one I am currently on.
gt;
gt; Would someone be so kind as to help me?
gt;
gt; Many Many thanks in advance.
gt;
gt; Mike
gt;
gt; Thanks.
gt;
gt; --
gt; flub
gt; ------------------------------------------------------------------------
gt; flub's Profile: www.excelforum.com/member.php...oamp;userid=30322
gt; View this thread: 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=quot;Tquot;,quot;AAquot;,if(B2=quot;Hquot;,quot;BBquot;,B2))
and copy down.

HTH,quot;flubquot; wrote:

gt;
gt; Hi,
gt;
gt; I need a little help, as I'm trying to help my father with a
gt; spreadsheet but have got stuck. I have 2 Columns, TransactionID and
gt; TransactionCode.
gt;
gt; 5T
gt; 5AA
gt; 6H
gt; 6BB
gt; 7AA
gt; 7T
gt; 8T
gt; 8BB
gt;
gt; What I want to be able to do it to create/compute a 3rd column based on
gt; the other 2. Now this is where I am getting stuck. The Rule is that for
gt; a TransactionCode of T or H this needs to be changed to either a quot;AAquot;
gt; or BB depending on what the other value is for the same Transaction ID.
gt; ie this would be the final output.
gt;
gt; 5TAA
gt; 5AAAA
gt; 6HBB
gt; 6BBBB
gt; 7AAAA
gt; 7TAA
gt; 8TBB
gt; 8BBBB
gt;
gt; The sort of code I was think was something like this pseudo code.
gt;
gt; if Cx = C(x-1) and Bx = quot;AAquot; then = quot;AAquot;
gt; if Cx = C(x-1) and Bx = quot;BBquot; then = quot;BBquot;
gt; if Cx = C(x 1) and Bx = quot;AAquot; then = quot;AAquot;
gt; if Cx = C(x 1) and Bx = quot;BBquot; then = quot;BBquot;
gt;
gt; But I have no Idea how to make these references to a Row PLUS/MINUS the
gt; one I am currently on.
gt;
gt; Would someone be so kind as to help me?
gt;
gt; Many Many thanks in advance.
gt;
gt; Mike
gt;
gt;
gt;
gt;
gt; Thanks.
gt;
gt;
gt; --
gt; flub
gt; ------------------------------------------------------------------------
gt; flub's Profile: www.excelforum.com/member.php...oamp;userid=30322
gt; View this thread: www.excelforum.com/showthread...hreadid=499850
gt;
gt;

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:
gt;
gt; I think I'd actually create a new worksheet with just the Id and the codes for
gt; the ones I wanted.
gt;
gt; Then I could use =vlookup() to return that code.
gt;
gt; =vlookup(a1,sheet2!a:b,2,false)
gt;
gt; But if all the codes to keep are length 2 and all the codes to ignore are not
gt; length 2, then put this in C1:
gt;
gt; =INDEX($B$1:$B$8,MATCH(1,($A$1:$A$8=A8)*(LEN($B$1: $B$8)=2),0))
gt;
gt; This is an array formula. Hit ctrl-shift-enter instead of enter. If you do it
gt; correctly, excel will wrap curly brackets {} around your formula. (don't type
gt; them yourself.)
gt;
gt; Adjust the range to match--but you can't use the whole column.))
gt;
gt; flub wrote:
gt; gt;
gt; gt; Hi,
gt; gt;
gt; gt; I need a little help, as I'm trying to help my father with a
gt; gt; spreadsheet but have got stuck. I have 2 Columns, TransactionID and
gt; gt; TransactionCode.
gt; gt;
gt; gt; 5 T
gt; gt; 5 AA
gt; gt; 6 H
gt; gt; 6 BB
gt; gt; 7 AA
gt; gt; 7 T
gt; gt; 8 T
gt; gt; 8 BB
gt; gt;
gt; gt; What I want to be able to do it to create/compute a 3rd column based on
gt; gt; the other 2. Now this is where I am getting stuck. The Rule is that for
gt; gt; a TransactionCode of T or H this needs to be changed to either a quot;AAquot;
gt; gt; or BB depending on what the other value is for the same Transaction ID.
gt; gt; ie this would be the final output.
gt; gt;
gt; gt; 5 T AA
gt; gt; 5 AA AA
gt; gt; 6 H BB
gt; gt; 6 BB BB
gt; gt; 7 AA AA
gt; gt; 7 T AA
gt; gt; 8 T BB
gt; gt; 8 BB BB
gt; gt;
gt; gt; The sort of code I was think was something like this pseudo code.
gt; gt;
gt; gt; if Cx = C(x-1) and Bx = quot;AAquot; then = quot;AAquot;
gt; gt; if Cx = C(x-1) and Bx = quot;BBquot; then = quot;BBquot;
gt; gt; if Cx = C(x 1) and Bx = quot;AAquot; then = quot;AAquot;
gt; gt; if Cx = C(x 1) and Bx = quot;BBquot; then = quot;BBquot;
gt; gt;
gt; gt; But I have no Idea how to make these references to a Row PLUS/MINUS the
gt; gt; one I am currently on.
gt; gt;
gt; gt; Would someone be so kind as to help me?
gt; gt;
gt; gt; Many Many thanks in advance.
gt; gt;
gt; gt; Mike
gt; gt;
gt; gt; Thanks.
gt; gt;
gt; gt; --
gt; gt; flub
gt; gt; ------------------------------------------------------------------------
gt; gt; flub's Profile: www.excelforum.com/member.php...oamp;userid=30322
gt; gt; View this thread: www.excelforum.com/showthread...hreadid=499850
gt;
gt; --
gt;
gt; Dave Peterson

--

Dave Peterson


Jim May Wrote:
gt; Assume your first row 5 and T
gt; are in cells A2 and B2 respectfully..
gt; In cell C2 enter:
gt;
gt; =if(B2=quot;Tquot;,quot;AAquot;,if(B2=quot;Hquot;,quot;BBquot;,B2))
gt; 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: www.excelforum.com/member.php...oamp;userid=30322
View this thread: www.excelforum.com/showthread...hreadid=499850
Dave Peterson Wrote:
gt; Put this in C1:
gt;
gt; =INDEX($B$1:$B$8,MATCH(1,($A$1:$A$8=A1)*(LEN($B$1: $B$8)=2),0))
gt;
gt; Dave Peterson

Dave I tried that and got a quot;#N/Aquot;.

I've uploaded my example file. It is very small -------------------------------------------------------------------
|Filename: ExampleFLUB.zip |
|Download: www.excelforum.com/attachment.php?postid=4200 |
-------------------------------------------------------------------

--
flub
------------------------------------------------------------------------
flub's Profile: www.excelforum.com/member.php...oamp;userid=30322
View this thread: www.excelforum.com/showthread...hreadid=499850I 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:
gt;
gt; Dave Peterson Wrote:
gt; gt; Put this in C1:
gt; gt;
gt; gt; =INDEX($B$1:$B$8,MATCH(1,($A$1:$A$8=A1)*(LEN($B$1: $B$8)=2),0))
gt; gt;
gt; gt; Dave Peterson
gt;
gt; Dave I tried that and got a quot;#N/Aquot;.
gt;
gt; I've uploaded my example file. It is very small
gt;
gt; -------------------------------------------------------------------
gt; |Filename: ExampleFLUB.zip |
gt; |Download: www.excelforum.com/attachment.php?postid=4200 |
gt; -------------------------------------------------------------------
gt;
gt; --
gt; flub
gt; ------------------------------------------------------------------------
gt; flub's Profile: www.excelforum.com/member.php...oamp;userid=30322
gt; View this thread: www.excelforum.com/showthread...hreadid=499850

--

Dave Peterson

Try this in column C2 and copy down.
=IF(B2=quot;Tquot;,IF(A2=A3,B3,B1),IF(B2=quot;Hquot;,IF(A2=A3,B3,B 1),B2))
Not a very elegant solution but should work for you.
--
quot;flubquot; wrote:

gt;
gt; Jim May Wrote:
gt; gt; Assume your first row 5 and T
gt; gt; are in cells A2 and B2 respectfully..
gt; gt; In cell C2 enter:
gt; gt;
gt; gt; =if(B2=quot;Tquot;,quot;AAquot;,if(B2=quot;Hquot;,quot;BBquot;,B2))
gt; gt; and copy down.
gt;
gt; Thanks Jim. I did that and it NEARLY worked.The output was as shown
gt; below.
gt;
gt; Transaction ID COMP CODE FINAL CODE
gt; 5 T AA
gt; 5 AA AA
gt; 6 H BB
gt; 6 BB BB
gt; 7 AA AA
gt; 7 T AA
gt; 8 T AA
gt; 8 BB BB
gt;
gt; As you can see Transcation ID 8 has a FINAL CODE of AA and BB, it
gt; should be just BB for both.
gt;
gt;
gt; --
gt; flub
gt; ------------------------------------------------------------------------
gt; flub's Profile: www.excelforum.com/member.php...oamp;userid=30322
gt; View this thread: www.excelforum.com/showthread...hreadid=499850
gt;
gt;

quot;flubquot; wrote:
gt; But I have no Idea how to make these references
gt; 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:
gt; quot;flubquot; wrote:
gt; gt; But I have no Idea how to make these references
gt; gt; to a Row PLUS/MINUS the one I am currently on.
gt;
gt; It might if someone answered your question(!). One
gt; way to do it (example: in C3):
gt; =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.