Compare tables in 2 workbooks

[sharshra]

I have to compare 2 tables in 2 workbooks & update a table1 in book1 with the values from table 2 in book2. Can the experts suggest the best way to do this?

Alternatively, what is the best way if both tables are in different worksheets in same workbook?

Consider the simplified table below to mimic the situation.
Table 1 in book1 has field rq, which is referred in table 2 in book2 in field rq ref. I want is to list the rq references made in table 2.

Table 1: At present, values in column `rq ref in d´ is filled manually referring to table 2. I want to use formula to get the same result.

Book1
	B	C
2	rq	rq ref in d
3	rq 1	d2, d4
4	rq 2	d8, d10
5	rq 3	d6
6	rq 4	d11, d13
7	rq 5	d1, d14
8	rq 6	d5, d15
9	rq 7	d4
10	rq 8	d12
11	rq 9	d7, d3, d11
12	rq 10	d3, d10, d15
table 1

Table 2:

Book1
	B	C
2	d	rq ref
3	d1	rq 5
4	d2	rq 1
5	d3	rq 10
6	d4	rq 7, rq 1
7	d5	rq 6
8	d6	rq 3
9	d7	rq 9, rq 10
10	d8	rq 2
11	d9	rq 3
12	d10	rq 2
13	d11	rq 4, rq 9
14	d12	rq 8
15	d13	rq 4
16	d14	rq 5
17	d15	rq 10, rq 6
table 2

 

[sharshra]

Hello Experts,
Any suggestions?

 

[alansidman]

I am unsure of what your expected results should like as you have not provided a manual mock up. Based upon my best "guess" as my crystal ball is broken, I have the following Mcode from Power Query

let
    T1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split1 = Table.ExpandListColumn(Table.TransformColumns(T1, {{"rq ref in d ", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "rq ref in d "),
    TT = Table.TransformColumns(Split1,{{"rq ref in d ", Text.Trim, type text}}),
    T2= Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Split2 = Table.ExpandListColumn(Table.TransformColumns(T2, {{"rq ref", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "rq ref"),
    TT2 = Table.TransformColumns(Split2,{{"d", Text.Trim, type text}}),
    MQ = Table.NestedJoin(TT, {"rq"}, TT2, {"rq ref"}, "New", JoinKind.FullOuter),
    #"Expanded New" = Table.ExpandTableColumn(MQ, "New", {"d", "rq ref"}, {"d", "rq ref"})
in
    #"Expanded New"

Book5
	G	H	I	J
1	rq	rq ref in d	d	rq ref
2	rq 1	d2	d2	rq 1
3	rq 1	d4	d2	rq 1
4	rq 5	d1	d1	rq 5
5	rq 5	d1	d14	rq 5
6	rq 5	d14	d1	rq 5
7	rq 5	d14	d14	rq 5
8	rq 2	d8	d8	rq 2
9	rq 2	d8	d10	rq 2
10	rq 2	d10	d8	rq 2
11	rq 2	d10	d10	rq 2
12	rq 10	d3	d3	rq 10
13	rq 10	d3	d15	rq 10
14	rq 10	d10	d3	rq 10
15	rq 10	d10	d15	rq 10
16	rq 10	d15	d3	rq 10
17	rq 10	d15	d15	rq 10
18	rq 7	d4	d4	rq 7
19	rq 3	d6	d6	rq 3
20	rq 3	d6	d9	rq 3
21			d4	rq 1
22	rq 4	d11	d11	rq 4
23	rq 4	d11	d13	rq 4
24	rq 4	d13	d11	rq 4
25	rq 4	d13	d13	rq 4
26	rq 6	d5	d5	rq 6
27	rq 6	d15	d5	rq 6
28	rq 9	d7	d7	rq 9
29	rq 9	d3	d7	rq 9
30	rq 9	d11	d7	rq 9
31			d7	rq 10
32	rq 8	d12	d12	rq 8
33			d11	rq 9
34			d15	rq 6
Sheet1

 

[Fluff]

Duplicate to: Filter problem

In future, please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread.