How to compare columns from two excel files?

[JustynaMZ]

Hi,

I am new here and wanted to ask you for help.

I have to xlsx files.

File 1

ID	Date	Symbol
X0001	01.04.2018	PL
X0001	01.03.2018	UA
X0001	01.02.2018	RO
X0001	01.04.2018	PL
Y0001	01.03.2018	UA
Y0001	01.02.2018	RO
Y0001	01.03.2018	PL
Y0001	01.02.2018	UA
X0001	01.04.2018	PL
X0001	01.03.2018	UA
X0001	01.03.2018	RO
X0001	01.04.2018	FR
Y0001	01.11.2018	UA
Y0001	01.02.2018	GR
Y0001	01.03.2018	PL
Y0001	01.02.2018	UA

File 2

ID	Date	Symbol
X0001	01.04.2018	PL
X0001	01.03.2018	BB
X0001	01.02.2018	RO
X0001	01.04.2018	PL
Y0001	01.11.2018	UA
Y0001	01.02.2018	RO
Y0001	01.03.2018	PL2
Y0001	01.02.2018	UA
X0001	01.12.2018	PL
X0001	01.03.2018	UA
Y0001	01.11.2018	UA
Y0001	01.02.2018	GR
M0001	01.03.2018	PL
Y0001	01.02.2018	UA

As a result in File 1 I would like to receive additional column (‘Result’), which shows comparison between 2 Files above.

So we compare 3 columns ID(File1)=ID(File2) and Date(File1)=Date(File2) and Symbol(File1)=Location(File2) – only then’Result’=1

ID	Date	Symbol	Result
X0001	01.04.2018	PL	1
X0001	01.03.2018	UA
X0001	01.02.2018	RO	1
X0001	01.04.2018	PL	1
Y0001	01.03.2018	UA
Y0001	01.02.2018	RO	1
Y0001	01.03.2018	PL
Y0001	01.02.2018	UA	1
X0001	01.04.2018	PL
X0001	01.03.2018	UA	1
X0001	01.03.2018	RO
X0001	01.04.2018	FR
Y0001	01.11.2018	UA	1
Y0001	01.02.2018	GR
Y0001	01.03.2018	PL	1
Y0001	01.02.2018	UA	1

Can you please advise what workflow shall I create?

KR,

Justyna

[Emil_Kos]

Hi @JustynaMZ,

The easiest way to achieve it is using the join tool.

I have prepared the workflow for you:

and the output:

Please mark my post as a solution if this was helpful for you!

[marcusblackhill]

Hey @JustynaMZ !

Well, actually you can do that in many ways so I think you can choose the way you feel more comfortable to do. For things like this I like more to use union and unique tool to be sure I don't will multiply my records by mistake.

See the workflow attached, hope that help you!

[JustynaMZ]

HI, 

thanks for your help - nevertheless I did not receive the result I wanted 😞 

In the File 1 I have 16 rows, in File 2 I have 14 rows.

My goal is to receive a a result table with 16 rows (File 1) with only the information which of IDs (comparison File 1 and File 2) match this 3 conditions (i.e. IDs from 2 files match, Dates match and Symbols match).

So I should receive a table with 16 rows, where only in column 'Results' I receive '1' in the row where all 3 items (ID, Date, Symbol) match.

So this should be the result I receive:

ID	Date	Symbol	Result
X0001	01.04.2018	PL	1
X0001	01.03.2018	UA
X0001	01.02.2018	RO	1
X0001	01.04.2018	PL	1
Y0001	01.03.2018	UA
Y0001	01.02.2018	RO	1
Y0001	01.03.2018	PL
Y0001	01.02.2018	UA	1
X0001	01.04.2018	PL
X0001	01.03.2018	UA	1
X0001	01.03.2018	RO
X0001	01.04.2018	FR
Y0001	01.11.2018	UA	1
Y0001	01.02.2018	GR
Y0001	01.03.2018	PL	1
Y0001	01.02.2018	UA	1

[marcusblackhill · Accepted solution]

Hey @JustynaMZ !

Sorry, I understood wrong your problem. Look if now reach what you need 🙂

[Emil_Kos]

Hi @JustynaMZ,

I believe I also misunderstand your request.

@marcusblackhill provided you with elegant solution to your problem.

[JustynaMZ]

Wow! thank you for your swift reply and help!