Bringing together 2 sets of data

[wrightyrx7]

Hi all,

I have to set of data. Both lists have a Employee reference which is unique to each person.

First: List of all positions people have ever had in the company with start and end date.

Second: List of all the salaries people have had within the company with a start and end date.

I need to bring this data together. However, if someone has had more than one salary between the start and end date of a position i need to insert a row with the same position details but each row will contain the 2 different salaries.

I have tried to do an example below.

Basically Employee 1 has had 2 positions but only 1 salary - so out the outcome they have 2 lines for each position but the same salary for both.

Employee 3 has had 2 positions but had a change in salary in the first one - so the outcome shows 2 lines for the first position because they had a salary change. Then the second position has one line because although its a new salary it started the same day as the position change.

I hope this makes sense. I just chant get my head round how to do this.

Thanks
Chris

 

[WinteE]

PowerQuery would be a good solution for this.

 

[wrightyrx7]

PowerQuery would be a good solution for this.

I have never used power query before

 

[WinteE]

What version of Excel do you use ?

 

[Fluff]

Cross posted http://www.vbaexpress.com/forum/showthread.php?64709-Bringing-together-2-sets-of-data

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[wrightyrx7]

What version of Excel do you use ?

Excel 2013 and I use the Office 365 version when im in the office.

Cross posted http://www.vbaexpress.com/forum/showthread.php?64709-Bringing-together-2-sets-of-data

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

Apologies, noted for next time. Thanks.