Take data from 2 sheets, search and combine

[wlof]

Hi all,

Hoping this has a solution. My workplace uses some fairly dated software and I need to complete a lot of manual data tasks which I feel may be able to be automated.

Basically, we use two systems and I need to combine data from 2 separate sheets into one.

Sheet One - Staff names with a few results.
Sheet Two - Same again, different data.

Sheet 3 - a combined version.



Best to show by an example, perhaps? A complication would be the names on both Data1 and Data2 can vary position. Plus, sometimes 'Dave' may be listed on data2, but not in data1 or vice-versa.

Is this possible to have something Start on Data1 and copy Alf's time/emails to the correct location on Data3, repeat for Bob, Charlie and Dave

THEN

Do the same for Dave, Bob, Charlie and Alf from Data2?



Cheers!

 

[alansidman]

Join the two tables with Power Query

let
    T1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    T2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    MQ=Table.NestedJoin(T1,{"Agent"},T2,{"Agent"},"Merged",JoinKind.LeftOuter),
    #"Expanded Merged" = Table.ExpandTableColumn(MQ, "Merged", {"Sales", "Calls"}, {"Sales", "Calls"})
in
    #"Expanded Merged"

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.