Match values in a colum based on criteria from other column

[lcaseybsa]

Hello,

Is there a way to find duplicate user id's from a single column based on the values of two other columns?

Example:

col1 = userid

col2 = Table Name

col 3 - record id

What I want to do is create a colum called Userid2

A formula or something like this:

IF Table Name = PM10000 find the same Userid where table name = PM20000 and return the user id where the table = PM 20000

I am trying to create an audit report that shows IF the same user entered a transaction and Posted it I could Identify the record.

[nathansav]

something like this using an array formula

=INDIRECT(ADDRESS(IF(B2="PM1000",MAX((A2:A4=A2)*(B2:B4="PM2000")*ROW(A2:A4)),""),3))

[nathansav]

you can mess with that a bit, to do some checking before like moving the PM1000 check to the front

[lcaseybsa]

Hello ,

I have attached the spreadsheet,

If you look in the all transactions tab, could you show me an example?

[nathansav]

sure, what fields do you want to check USERID, RSA_TECHNICAL_TABLE_NAME as this only has one type in PM_Transaction_OPEN

[lcaseybsa]

RSA_Table_Physical_Name

Column O

So I need to check all user Id's for rows containing RSA_Table_Physical_Name "Pm10000" against all user Id's in RSA_Table_Physical_Name "Pm20000" and make sure that there are no matches. The only way to check this is against the Key field which is column D

The Key Field contains the exact record ID . A Key field will alwaysd have (2) duplicates because the PM10000 is the entry and PM20000

[lcaseybsa]

Or, would it be easier to check the "Key" field Column D which will always have two entries

1 for Transaction entered, 1 for transaction posted

Then do a lookup on all keys that have two entries and determine if a different User ID performed both transactions

[nathansav]

Hi,

Sorry, I have to duck out for a bit. I have attached an example for you to look at.