I have a spreadsheet with the following columns: CustAcctNum; TransactDate;
TransactCode; TransactAmt; OfficeLocation.

What I want to find out - for every account touched multiple times on
multiple dates, which OfficeLocation touched it first, then what was the last
TransactCode (text field) used on the account by a different OfficeLocation.
I have sorted the information by 1) CustAcctNum - Ascending 2) TransactDate
(Ascending) and 3)TransactAmt (Descending). My issue is that there is not a
set number of times an account was touched. Also, the last OfficeLocation
that touched it could have touched it multiple times on that date using
multiple codes. I am okay with getting the TransactCode for highest dollar
amount on the last date the account was touched.
Example of data:

CustAcctNum TransactDate TransactCode TransactAmt OfficeLocation.
456 10/21/06 ADJ1 -5.00 NY
456 10/23/06 CRED1 -5.00 TX
456 10/23/06 CRED2 -7.50 TX
789 10/03/06 CRED1 -5.50 CO
789 10/03/06 CRED1 -3.40 CO
789 10/10/06 CRED1 -2.33 TX
789 10/22/06 ADJ1 -4.50 PA
789 10/22/06 ADJ2 -8.00 PA
789 10/22/06 CRED3 -10.22 PA

Basically my end result needs to be The office that touched it first and the
code that was used last by a different office. There will not be an instance
where the office that touched it first will also have touched it last. So
with the example above I would want my results to be

NY CRED2
CO CRED3

I have about 10,000 records to search through, but I can't figure out a
formula to return these values in columns side by side. Eventually, I will
run another set of formulas against all the codes and locations I have to see
how many times these instances occured. I have that formula already.

Is this possible?

Thanks for any help,

Rob

Robert
I can't follow your example. You say "for every account....". By
"account", do you mean the CustAccNum? If so, then, in your example, NY
doesn't go with CRED2 and CO doesn't go with CRED3.
Apparently I don't understand your example.
From what I do understand about what you want, you will have to this with
VBA. Please post back and tell me where I got on the wrong track. HTH
Otto
"Robert" wrote in message
...
I have a spreadsheet with the following columns: CustAcctNum;
TransactDate;
TransactCode; TransactAmt; OfficeLocation.

What I want to find out - for every account touched multiple times on
multiple dates, which OfficeLocation touched it first, then what was the
last
TransactCode (text field) used on the account by a different
OfficeLocation.
I have sorted the information by 1) CustAcctNum - Ascending 2)
TransactDate
(Ascending) and 3)TransactAmt (Descending). My issue is that there is not
a
set number of times an account was touched. Also, the last OfficeLocation
that touched it could have touched it multiple times on that date using
multiple codes. I am okay with getting the TransactCode for highest
dollar
amount on the last date the account was touched.
Example of data:

CustAcctNum TransactDate TransactCode TransactAmt OfficeLocation.
456 10/21/06 ADJ1 -5.00 NY
456 10/23/06 CRED1 -5.00 TX
456 10/23/06 CRED2 -7.50 TX
789 10/03/06 CRED1 -5.50 CO
789 10/03/06 CRED1 -3.40 CO
789 10/10/06 CRED1 -2.33 TX
789 10/22/06 ADJ1 -4.50 PA
789 10/22/06 ADJ2 -8.00 PA
789 10/22/06 CRED3 -10.22 PA

Basically my end result needs to be The office that touched it first and
the
code that was used last by a different office. There will not be an
instance
where the office that touched it first will also have touched it last. So
with the example above I would want my results to be

NY CRED2
CO CRED3

I have about 10,000 records to search through, but I can't figure out a
formula to return these values in columns side by side. Eventually, I
will
run another set of formulas against all the codes and locations I have to
see
how many times these instances occured. I have that formula already.

Is this possible?

Thanks for any help,

Rob

Assuming that A2:E10 contains the data, let G2 and G3 contain 456 and
789, then try...

H2, copied down:

=INDEX($E$2:$E$10,MATCH(G2,$A$2:$A$10,0))

I2, copied down:

=INDEX($C$2:$C$10,MATCH(G2,$A$2:$A$10))

Note that you can use 'Advanced Filter' to generate a list of unique
Customer Account Numbers. Make sure you check 'Unique records only'.

Hope this helps!

In article ,
Robert wrote:

I have a spreadsheet with the following columns: CustAcctNum; TransactDate;
TransactCode; TransactAmt; OfficeLocation.

What I want to find out - for every account touched multiple times on
multiple dates, which OfficeLocation touched it first, then what was the last
TransactCode (text field) used on the account by a different OfficeLocation.
I have sorted the information by 1) CustAcctNum - Ascending 2) TransactDate
(Ascending) and 3)TransactAmt (Descending). My issue is that there is not a
set number of times an account was touched. Also, the last OfficeLocation
that touched it could have touched it multiple times on that date using
multiple codes. I am okay with getting the TransactCode for highest dollar
amount on the last date the account was touched.
Example of data:

CustAcctNum TransactDate TransactCode TransactAmt OfficeLocation.
456 10/21/06 ADJ1 -5.00 NY
456 10/23/06 CRED1 -5.00 TX
456 10/23/06 CRED2 -7.50 TX
789 10/03/06 CRED1 -5.50 CO
789 10/03/06 CRED1 -3.40 CO
789 10/10/06 CRED1 -2.33 TX
789 10/22/06 ADJ1 -4.50 PA
789 10/22/06 ADJ2 -8.00 PA
789 10/22/06 CRED3 -10.22 PA

Basically my end result needs to be The office that touched it first and the
code that was used last by a different office. There will not be an instance
where the office that touched it first will also have touched it last. So
with the example above I would want my results to be

NY CRED2
CO CRED3

I have about 10,000 records to search through, but I can't figure out a
formula to return these values in columns side by side. Eventually, I will
run another set of formulas against all the codes and locations I have to see
how many times these instances occured. I have that formula already.

Is this possible?

Thanks for any help,

Rob

Thanks - this should do the trick!

"Domenic" wrote:

Assuming that A2:E10 contains the data, let G2 and G3 contain 456 and
789, then try...

H2, copied down:

=INDEX($E$2:$E$10,MATCH(G2,$A$2:$A$10,0))

I2, copied down:

=INDEX($C$2:$C$10,MATCH(G2,$A$2:$A$10))

Note that you can use 'Advanced Filter' to generate a list of unique
Customer Account Numbers. Make sure you check 'Unique records only'.

Hope this helps!

In article ,
Robert wrote:

I have a spreadsheet with the following columns: CustAcctNum; TransactDate;
TransactCode; TransactAmt; OfficeLocation.

What I want to find out - for every account touched multiple times on
multiple dates, which OfficeLocation touched it first, then what was the last
TransactCode (text field) used on the account by a different OfficeLocation.
I have sorted the information by 1) CustAcctNum - Ascending 2) TransactDate
(Ascending) and 3)TransactAmt (Descending). My issue is that there is not a
set number of times an account was touched. Also, the last OfficeLocation
that touched it could have touched it multiple times on that date using
multiple codes. I am okay with getting the TransactCode for highest dollar
amount on the last date the account was touched.
Example of data:

CustAcctNum TransactDate TransactCode TransactAmt OfficeLocation.
456 10/21/06 ADJ1 -5.00 NY
456 10/23/06 CRED1 -5.00 TX
456 10/23/06 CRED2 -7.50 TX
789 10/03/06 CRED1 -5.50 CO
789 10/03/06 CRED1 -3.40 CO
789 10/10/06 CRED1 -2.33 TX
789 10/22/06 ADJ1 -4.50 PA
789 10/22/06 ADJ2 -8.00 PA
789 10/22/06 CRED3 -10.22 PA

Basically my end result needs to be The office that touched it first and the
code that was used last by a different office. There will not be an instance
where the office that touched it first will also have touched it last. So
with the example above I would want my results to be

NY CRED2
CO CRED3

I have about 10,000 records to search through, but I can't figure out a
formula to return these values in columns side by side. Eventually, I will
run another set of formulas against all the codes and locations I have to see
how many times these instances occured. I have that formula already.

Is this possible?

Thanks for any help,

Rob