Lookup and Pairing Data from different Tables

[Let-Me-Know]

Dear all,

Would You please help with the lookup issue between data from different tables and specific returning values.

Here I have two different tables:

• “Employees and Company Data” – includes Employees, their IDs and Companies, and Company Account No.

Employee	Employee ID	Company	Company Account No.
John	4523	ABC Ltd.	5413415
Peter	6421	DUG Ltd.	6666666
Anne	7441	URJ Ltd.	7469851
Mary	9999	OSI Ltd.	5555555
George	8888	XYZ Ltd.	4444444

• “Transactions Data” – includes Transaction Data with Buyer/Seller IDs and Account No.

Transaction ID	Buyer ID	Seller ID	Transaction Account No.
1	4523	6421	5413415
2	7441	1111	5555555
3	7441	1111	7469851
4	4523	1111	5555555
5	1111	1111	5555555

I have created connections between Tables and now would like to create visuals which would show me this: Was any of the Employees involved in the transactions (Buyer OR Seller side) and – if they did – which Accounts were there included (all of them)? Also, were there involved Accounts of their own Company?

The output would be 3rd table “Employees who were involved in the Transactions”. I have colored the input data in order to be easily identified in the output.

• "Employees who were involved in the Transactions"

Employee	Company	Transaction ID	Buyer / Seller	Transaction Account No.	Transaction using Account of her/his own Company?
John	ABC Ltd.	1	Buyer	5413415	YES
John	ABC Ltd.	4	Buyer	5555555	NO
Peter	DUG Ltd.	1	Seller	5413415	NO
Anne	URJ Ltd.	2	Buyer	5555555	NO
Anne	URJ Ltd.	3	Buyer	7469851	YES

It can also be shown in two different tables, for example:

• “Employees who were involved in the Transactions – Buyer Side”,
• “Employees who were involved in the Transactions – Seller Side”.

THANK YOU IN ADVANCE!

Let-Me-Know

[lbendlin]

What does "Transaction Account No"  mean?  The company keeping/getting the title?

Who is 1111 ?

[Let-Me-Know]

• What does "Transaction Account No" mean?  The company keeping/getting the title?

We can look at this input and output like this (hope my english would be understandable enough :D):

Each Company has its own stocks (shares) which are being traded with at the stock exchange. „Company Account No.“ stands for Official Number of the Stock.

Then we have a list of all transactions from the stock exchange (each with its “Transaction ID”) and information on which stock was the transaction about (what did Buyer buy and Seller sell). “Transaction Account No.” stands for traded stock in a specific transaction.

• Who is 1111 ?

The examples I gave were short version, so we have short list of employees, only 5 of them, therefore there were only 5 Companies mentioned (5 Companies with their own stocks issued).

In the list of transactions from the stock exchange we have a million of stocks which are being traded with, and also a million of Buyers and Sellers. That way, these employees can trade with many other stocks, and also the mentioned companies’ stocks could also be traded by any other natural and legal person (not only by employees of those companies).

So “1111” is a person who sold a lot of stocks (stock exchange transactions no. 1, 2, 3 and 4) and also he traded with (he sold) stocks of the mentioned companies (Company with stock number 7469851 – URJ Ltd. and Company with stock number 5555555 – OSI Ltd.). However, he does not work in the mentioned companies (he is not listed in the table “Employees and Company Data”).

Also, “1111” bought 1 stock (stock exchange transaction no. 5), and the stock was also of the company listed in the table (Company with stock number 5555555 – OSI Ltd.), however he does not work in the mentioned companies so that way he is not relevant.

• Summary:

The focus is on the given employees. The goal is to find them in the list of buyers and sellers on the stock exchange. And after (if we find them) we have to pair the stock which they traded with (what they bought and/or what they sold) in order to see if they have traded with the stock of their own company (their employer).

----

I the meantime I have created table which shows were any of the relevant employees on the BUY side of all transactions from the stock exchange by using LOOKUPVALUE function (you can identify the columns in my input table showed in the original post, except for the first data table and its column I mentioned in the function below):

Employeers Account No. = LOOKUPVALUE (Table with Stock Issuers Account No. [Account No.],

Table with Stock Issuers Account No. [Company], Employees and Company Data [Company])

For now it works fine. If you have other/better idea, please share it with me.

Column “Transaction using Account of her/his own Company?” with answers “YES/NO” remains unresolved, guess I can work it out with IF function but haven’t tested it yet.