Trying to input values into a clear table from a Primavera BSS generated output

Cross-posted at
https://www.mrexcel.com/board/thread...utput.1214636/
https://www.reddit.com/r/excel/comme..._table_from_a/

Good afternoon,


Our biggest payroll client at our accounting firm has roughly 70 active employees whose payslips and benefits we calculate, and every month their representative asks for a salary map (Objective Table), which today I just learned is done by hand, meaning our accountants have to go into the accounting software generated compensation breakdown (Basis Table) and begrudgingly copy and paste every single value from Basis to Objective. As I was tasked to build this map this month, and discovered how infuriatingly it is being done in my company, I decided to give a shot at automating this by use of lookup functions (VLOOKUP, HLOOKUP and I had to use an add-in for XLOOKUP, as the Excel version in our company is too old for that function), however my knowledge of functions and tables is slim and I couldn't find a way to get past the fact that the Basis table is full of merged cells and messy data where it shouldn't be (lookup values in the same column mixed with employee names).

I would greatly appreciate some help in finding a way to fetch the values from Basis Table and input them into Objective Table, so as to do this faster in the future and eliminate human error.
You may see both tables attached, with the original names scrubbed out and replaced with randomly generated names, for client confidentiality.
In the Objective Table, which is in English, I have added the corresponding row/column Portuguese name in use for the Basis Table, since it is in Portuguese, and to facilitate lookup. I was hoping that a lookup function looking for a person's name, then looking for that translated column name, then fetching the value and pasting it in the Objective Table would do, but I don't think I've quite grasped how to do it yet.

Many many thanks!

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

I have added a link to your post because it is your first post.

Yes, new users cannot post live links but I think the URL will be OK if you omit the http prefix.

I also added the Reddit link.

Please Login or Register  to view this content.

The above treats "Objective Table" as a blank canvas so it completes all fields required from "Basis Table": all other fields are left blank.

Thank you for thee feedback.

If you are satisfied with the solution, can you please mark the thread as SOLVED: see "Thread Tools" at top of the page.