Comparing data from 3 reports

[JustynaMZ]

Hi,

I have 3 reports with a lot of columns.

Nr of columns in each report is different.

Some of the columns have the same name some of not.

Nr of rows in each column differs.

As an example:

Report A (20 rows)

ID	Date	Symbol
A	29.07.2019	PL
A	01.08.2019	PL
A	05.11.2018	PL
A	01.10.2019	PL
A	02.09.2019	PL
A	01.10.2018	PL
B	02.09.2019	PL
B	07.09.2020	PL
B	01.07.2019	PL
B	01.09.2020	PL
B	20.12.2018	PL
B	21.01.2019	PL
C	31.08.2020	PL
C	23.09.2019	PL
C	01.03.2020	PL
C	01.07.2018	PL
C	16.03.2019	PL
C	17.03.2019	PL
D	16.03.2019	PL
D	17.03.2019	PL

Report B

ID	Date	Symbol	Color	Days
A	29.07.2019	PL	black	10
A	01.08.2019	PL	red	2
A	05.11.2018	PL	white	3
E	01.10.2019	PL	black	4
E	02.09.2019	PL	red	5
F	01.10.2018	PL	white	6
F	02.09.2019	PL	black	7
B	07.09.2020	PL	red	7
B	01.07.2019	PL	white	8
B	01.09.2020	PL	black	4
G	20.12.2018	PL	red	5
G	21.01.2019	PL	white	6
H	31.08.2020	PL	black	8
H	23.09.2019	PL	red	1
I	01.03.2020	PL	white	2
J	01.07.2018	PL	black	0
K	16.03.2019	PL	red	2
C	17.03.2019	PL	white	3
D	16.03.2019	PL	black	4
D	17.03.2019	PL	red	6

Report C

ID	Date	Symbol	code
A	29.07.2019	g	Sss
A	01.08.2019	g	eee
A	05.11.2018	g	Ttt
A	01.10.2019	g	qqq
A	02.09.2019	PL	ggg
A	01.10.2018	PL	yyy
B	02.09.2019	PL	uuu
B	07.09.2020	g	Iii
B	01.07.2019	g	ooo
B	01.09.2020	g	ppp
B	20.12.2018	g	aaa
B	21.01.2019	g	Zzz
C	31.08.2020	g	Sss
C	23.09.2019	g	xxx
C	01.03.2020	PL	eeef
C	01.07.2018	PL	Cs
C	16.03.2019	PL	dsa

I need to compare/match:

-Report A and Report B, i.e. : ID(from A)=ID(from B) & (Date(from A)=Date(from B) & Symbol(from A)=Symbol(from B)

Based on this comparison I want to add to the Report A 2 columns:

1. ‘Color from comparison’ – which returns ‘color’ for matched rows
2. ‘Days from comparison’ – which returns ‘color’ for matched rows

-Report A and Report C, , i.e. : ID(from A)=ID(from C) & (Date(from A)=Date(from C) & Symbol(from A)=Symbol(from C)

Based on this comparison I want to add to the Report A third column:

3. ‘Code from comparison’ – which returns ‘code’ for matched row

As a result I would like to receive one Report (with 20 rows as in initial Report A) with columns as below:

Thanks in advance for your help!

J.

[treepruner]

Don't you just need to use the join tool to join A to B and then use it again to join that result to C?

If you right click on the join tool you can open an example

Sarah

[Qiu]

@JustynaMZ 
You dont really need the color right?😁

[JustynaMZ]

Hi, 

I did this a little bit differently by using Find and Replace.

But  thanks for sharing your solutions - I am just a beginner so it is good to know other ways 🙂

J.