Comparing Values in a long list

[lcaseybsa]

Hello, I have a long list of rows that I need to compare and find values that match each other In the below table I would like to do the following:


1. Compare all rows with PM10000 against all rows with PM20000 using the Record #
2. See if the same Username is listed twice for each record
3. If not, then Posting User would be the user id for records with PM20000




ID Username Type Record # Date Time Table
13643 svc_gpweb 0 VCHRKT0000000141 5/14/2013 0:00 10:18:28 AM PM10000
3644 svc_gpweb 0 VCHRKT0000000140 5/14/2013 0:00 10:18:28 AM PM10000
3645 svc_gpweb 0 VCHRKT0000000144 5/14/2013 0:00 10:18:29 AM PM10000
3646 svc_gpweb 0 VCHRKT0000000145 5/14/2013 0:00 10:18:29 AM PM10000
3647 xiaol 0 VCHRKT0000000146 5/14/2013 0:00 10:18:30 AM PM10000
3648 svc_gpweb 0 VCHRKT0000000580 5/14/2013 0:00 10:19:08 AM PM10000
3649 svc_gpweb 0 VCHRKT0000000428 5/14/2013 0:00 10:19:20 AM PM10000
3650 svc_gpweb 0 VCHRKT0000000429 5/14/2013 0:00 10:19:20 AM PM10000
3651 svc_gpweb 0 VCHRKT0000000508 5/14/2013 0:00 10:20:09 AM PM10000
3652 svc_gpweb 0 VCHRKT0000000603 5/14/2013 0:00 10:35:16 AM PM10000
3653 svc_gpweb 0 VCHRKT0000000604 5/14/2013 0:00 10:35:16 AM PM10000
3654 svc_gpweb 0 VCHRKT0000000605 5/14/2013 0:00 10:35:17 AM PM10000
3655 svc_gpweb 0 VCHRKT0000000606 5/14/2013 0:00 10:35:18 AM PM10000
3656 svc_gpweb 0 VCHRKT0000000612 5/14/2013 0:00 10:35:24 AM PM10000
3657 svc_gpweb 0 VCHRKT0000000626 5/14/2013 0:00 10:35:52 AM PM10000
3658 svc_gpweb 0 VCHRKT0000000608 5/14/2013 0:00 10:36:14 AM PM10000
3692 xiaol 1 VCHRKT0000000144 5/14/2013 0:00 1:32:38 PM PM20000
3693 xiaol 1 VCHRKT0000000145 5/14/2013 0:00 1:32:38 PM PM20000
3694 xiaol 1 VCHRKT0000000146 5/14/2013 0:00 1:32:38 PM PM20000
3734 xiaol 1 VCHRKT0000000140 5/14/2013 0:00 5:14:08 PM PM20000
3735 xiaol 1 VCHRKT0000000141 5/14/2013 0:00 5:14:09 PM PM20000
3736 xiaol 1 VCHRKT0000000428 5/14/2013 0:00 5:14:09 PM PM20000
3737 xiaol 1 VCHRKT0000000429 5/14/2013 0:00 5:14:09 PM PM20000
3738 xiaol 1 VCHRKT0000000508 5/14/2013 0:00 5:14:09 PM PM20000
3739 xiaol 1 VCHRKT0000000580 5/14/2013 0:00 5:14:09 PM PM20000


Would end up looking Like


13643 svc_gpweb xiaol 0 VCHRKT0000000141 5/14/2013 0:00 10:18:28 AM PM10000
3644 svc_gpweb xiaol 0 VCHRKT0000000140 5/14/2013 0:00 10:18:28 AM PM10000
3645 svc_gpweb xiaol 0 VCHRKT0000000144 5/14/2013 0:00 10:18:29 AM PM10000
3646 svc_gpweb xiaol 0 VCHRKT0000000145 5/14/2013 0:00 10:18:29 AM PM10000
3647 xiaol xiaol 0 VCHRKT0000000146 5/14/2013 0:00 10:18:30 AM PM10000
3648 svc_gpweb xiaol 0 VCHRKT0000000580 5/14/2013 0:00 10:19:08 AM PM10000
3649 svc_gpweb xiaol 0 VCHRKT0000000428 5/14/2013 0:00 10:19:20 AM PM10000
3650 svc_gpweb xiaol 0 VCHRKT0000000429 5/14/2013 0:00 10:19:20 AM PM10000
3651 svc_gpweb xiaol 0 VCHRKT0000000508 5/14/2013 0:00 10:20:09 AM PM10000
3652 svc_gpweb xiaol 0 VCHRKT0000000603 5/14/2013 0:00 10:35:16 AM PM10000
3653 svc_gpweb xiaol 0 VCHRKT0000000604 5/14/2013 0:00 10:35:16 AM PM10000
3654 svc_gpweb xiaol 0 VCHRKT0000000605 5/14/2013 0:00 10:35:17 AM PM10000
3655 svc_gpweb xiaol 0 VCHRKT0000000606 5/14/2013 0:00 10:35:18 AM PM10000
3656 svc_gpweb xiaol 0 VCHRKT0000000612 5/14/2013 0:00 10:35:24 AM PM10000
3657 svc_gpweb xiaol 0 VCHRKT0000000626 5/14/2013 0:00 10:35:52 AM PM10000
3658 svc_gpweb xiaol 0 VCHRKT0000000608 5/14/2013 0:00 10:36:14 AM PM10000
3692 xiaol 1 VCHRKT0000000144 5/14/2013 0:00 1:32:38 PM PM20000
3693 xiaol 1 VCHRKT0000000145 5/14/2013 0:00 1:32:38 PM PM20000
3694 xiaol 1 VCHRKT0000000146 5/14/2013 0:00 1:32:38 PM PM20000
3734 xiaol 1 VCHRKT0000000140 5/14/2013 0:00 5:14:08 PM PM20000
3735 xiaol 1 VCHRKT0000000141 5/14/2013 0:00 5:14:09 PM PM20000
3736 xiaol 1 VCHRKT0000000428 5/14/2013 0:00 5:14:09 PM PM20000
3737 xiaol 1 VCHRKT0000000429 5/14/2013 0:00 5:14:09 PM PM20000
3738 xiaol 1 VCHRKT0000000508 5/14/2013 0:00 5:14:09 PM PM20000
3739 xiaol 1 VCHRKT0000000580 5/14/2013 0:00 5:14:09 PM PM20000

[HaHoBe]

Hi, lcaseybsa,

could you be good enough to post a workbook with the sniplets you posted directly to the forum and show the situation as is as well as the result you want from the code? Thanks in advance.

Ciao,
Holger

[lcaseybsa]

Hello,

In the attached spreadsheet I am trying to find any users who have entered transaction as well as posted a transaction and be able to filter them to show which ones.


In the all transaction tab I am pulling in all audit transactions.

There are Entry tables and posting tables. All data entry is done and audited on the work file, for example: PM10000 it records the username who did it. Then when the transaction gets posted it will create a record for the PM20000 table with the user ID who posted. All these audits create rows in the spreadsheet Identifying UserID and which table

What I want to do is compare all user id's for rows that contain table PM100000 against all user ids were rows contain PM20000 and if the user id of rows containing PM10000 = the user id of rows containing PM20000 then flag a new column with yes so I can filter on it and show all the transactions were users not only entered a transaction but also posted a transaction.

[HaHoBe]

Hi, lcaseybsa,

if I glimpsed through the data correctly no code needed as userID for PM10000 is say svc_gpwe while for PM20000 svc_gpwe doesn´t exist. Maybe you should check for the Key intead?

Ciao,
Holger

[lcaseybsa]

Hmm right... Becouse the Key should have two entries, one for the entry and one for the posted.

So I wouild need to look on the Key field, then determine if the userid shows up twice

[HaHoBe]

Hi, lcaseybsa,

maybe give this a try:

Please Login or Register  to view this content.

Ciao,
Holger