Since you are using Excel 2013, Power Query is available for you to download from the Excel Site. Using PQ, I loaded each table to it and then Close and Loaded to a Connection. Then, I merged the two tables with a Left Inner Join on the Identifier and Date. The results were:
Excel 2016 (Windows) 32 bit
|
A |
B |
C |
D |
E |
F |
G |
H |
1 |
Idenitifier |
Forename |
Surname |
Date |
Basic Rate |
Holiday Pay |
Basic earnings total |
paid pension at % |
2 |
603624 |
Pete |
Smith |
6/1/2020 |
700 |
800 |
1500 |
5.8 |
3 |
603625 |
Pete |
Smith |
4/1/2020 |
600 |
200 |
800 |
5.6 |
4 |
603626 |
Pete |
Smith |
2/1/2020 |
450 |
200 |
650 |
5.6 |
5 |
603627 |
Pete |
Smith |
12/1/2019 |
800 |
200 |
1000 |
5.3 |
Review PQ
In the attached file
Click on any cell in the new table
On the Data Tab, click on Queries & Connections
In the right window, double click to open Query
Review PQ steps
M-code basics:
- "let" is the start of a query
- "in" is the closing of a query
- each transformation step sits in between those 2 lines
- each step line is ended by a comma, except the last one
- "Source" is always the first step (Source Data)
- After "in" you have the last step referenced
File attached
The Mcode that resulted from my steps is as follows.
Bookmarks