How to compare columns from two excel files?
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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
Solved! Go to Solution.
- Labels:
- Common Use Cases
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi @JustynaMZ,
I believe I also misunderstand your request.
@marcusblackhill provided you with elegant solution to your problem.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Wow! thank you for your swift reply and help!
