This is beyond what is probably normally asked as far as help on Experts Exchange.
I thought I would take a shot and ask anyway. Attached is a spreadsheet.
At the top of the spreadsheet you will see a sample of the two tables involved in the query. TableA and TableB
There is a common identifier with the AccountNumber column between the two tables.
The accounts basically have the same services listed. The way the services are listed is completely different unfortunately.
The services translate between the tables though. Please refer to the excel cells highlighted under the title.
"Service Option Translation - Column 'N' Corresponds to Column 'O'. Highlighted with bright orange in background and Red Bold Text.
The desired Results table in the bottom left shows the desired information deduced from TableA and TableB.
Table A columns data is specified in blue and Table B columns data highlighted in Yellow.
The columns used in the original tables are highlighted in Dark Orange to help.
The match needs to be based on two variables. A.Accountnumber = B.Accountnumber and also A.Service = B.ServiceOptionsKey
Problem.... A.Service and B.ServiceOptionskey are not the same data but they are consistently translated if that makes sense.
Please refer to excel section labeled "Service Option Translation" highlighted in Orange with Red Text for the Translation "key"
I would also need for line items to show up that do not have a match. This is highlighted in light green. this occurs when an account
has a service that does not translate.... Meaning... there isn't a corresponding service set up on Table B
I am open to creating views or a new table to provide the translation.