Match Inconsistent Data between 2 Inputs

[tahertalib]

Hi 
I have 2 datasets which i need to merge:

Input 1:

Description	ID	A-Loc ID	Z-Loc ID
Bose	123	ABC12	TAT456
Surface	874	TTT543	BGKKK134

Input 2(This input has the address for the Loc ID's in Input 1)

ID	Use	Address
ABC123456	Headphones	New York
TAT456X12	Laptop	Los Angeles
AATTT54378	Test	Seattle
BGKKK	Test1	Denver

Output i need:

Description	ID	Actual A-Loc ID	Mathced A-Loc ID	Actual Z-Loc ID	Matched Z-loc ID	A-Address	Z-Address
Bose	123	ABC12	ABC123456	TAT456	TAT456X12	New York	Los Angeles

I tried using Find Replace tool for the above, but that doesn't seem to work.

The 'Find Replace' tool only works fine when i use it for A-Location. But when i use the output of A-Loc as an input to find match for Z-loc, it doesn't work.

Is there any better way to do this?

Thanks

[AngelosPachis]

Hi @tahertalib ,

What you might have to do is use a transpose tool after your number 1 input because you want to look for both values contained in columns A-Loc ID and Z-Loc ID

Then you can create the new column headers in a formula tool and pivot the table as necessary to get the required output.

Hope that helps, let me  know if that worked for you.

Regards,

Angelos