Combine 2 tables into one

Hello,

I have two Excel tables, A and B. There is redundant data in both tables as well as unique data.
All I want is to combine both with no redundant records automatically. (A+B-Redundant= Uniq complete data). I have attached the result I want.

Please help.

Thanks.

You have apple listed twice, is that correct?
Which values do you want returned?

If I were doing this, I would put each table on a different worksheet. Then import each table into Access and create a UNION ALL Query to join the two tables.

Capture.PNG

maybe:

1. create 4 lists because it's easier (see in attachment)
2. helper column, K3 (with Control+Shift+Enter):

=IFERROR(IFERROR(INDEX(list11&list12,MATCH(0,COUNTIF($K$2:K2,list11&list12),0)), INDEX(list21&list22,MATCH(0,COUNTIF($K$2:K2,list21&list22),0))), "")

3. col. Name, L3:

=IFERROR(LEFT(K3,LEN(K3)-3),"")

4. col. Amount, M3:

=RIGHT(K3,3)

EDIT:
another way:
1. Select table A with headers
2. Click Data tab (Ex2016) or Power Query (Ex less then 2016)
3. Click From Table
4. In the window that opens click Close and Load
5. New sheet (sheet2) is inserted with the data from first table (table A)
6. Repeat the steps to add table B
7. Click on "new" table A and from Query Tools - Query (Ex2016) or Power Query (Ex less then 2016)...
8. Select Append
9. In new window select a table from each drop down then click OK
10. in Query Editor window select Remove Duplicates
11. Close and Load
12. On sheet3 you will get result.

sandy Book1-2.xlsx