Merge 2 tables in different sheet

[directx]

Hello everyone,

I have a quick question, I have 2 sheets with 2 tabels looking like this:

Table 1 - sheet1


Table 2 - sheet2


I want to somehow do the following:

1. Do all job in new sheet1
2. From Table1 (A column) find match in Table2 (E column)
3. Copy content from Table 2 (D column)
4. Paste it in Table 1 column B
5. For all cells value in Table1 that don't have a match in Table2 write down DNP in D column

Final table should look like this:



Any1 have any idea how to achive this?

Best regards and keep a good work!

 

[sandy666]

you didn't show your Excel version but I'll try...

use PowerQuery (Get&Transform)

in short

• load both tables into PowerQuery
• Merge tables by Designator
• Expand table
• load to the sheet

 

[directx]

I'm using Microsoft Office Professional Plus 2013, when i click on Addins i have only Microsoft Power Map for Excel and PowerPivot?

 

[sandy666]

try this: Power Query add-in
choose correct version (32 or 64 bit)

 

[directx]

Sorry it's my first time using this addon, can you explain a little bit in detail how to use this thing?
Btw thanks for the link

 

[sandy666]

maybe read here: Excel Power Query (Get & Transform) Tutorial for Beginners
pay attention on Merge feature

 

[directx]

Wow, what a powerful addon. Thanks a lot mate, it's working like a charm!

 

[sandy666]

You are welcome