Pete van Almond
New Member
- Joined
- Aug 24, 2022
- Messages
- 1
- Office Version
- 2016
- Platform
- Windows
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 snippets of 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!
Basis Table
Objective Table
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 snippets of 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!
Basis Table
Basis Table.xlsx | |||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | |||
38 | Total Funcionário | 182,49 | 1.207,90 | 1.025,41 | |||||||||||||||||||
39 | |||||||||||||||||||||||
40 | 005 | Clinton Byrne | |||||||||||||||||||||
41 | |||||||||||||||||||||||
42 | Vencimento | 2.930,00 | |||||||||||||||||||||
43 | Subsídio Alimentação - Géneros | 153,47 | |||||||||||||||||||||
44 | Coverflex tributável IRS | 550,00 | |||||||||||||||||||||
45 | Segurança Social | 322,30 | |||||||||||||||||||||
46 | IRS | 995,00 | |||||||||||||||||||||
47 | Desconto Espécie | 153,47 | |||||||||||||||||||||
48 | Descontos Coverflex | 550,00 | |||||||||||||||||||||
49 | Compensação do trabalho | 0,00 | |||||||||||||||||||||
50 | Garantia Comp. do trabalho | 0,00 | |||||||||||||||||||||
51 | |||||||||||||||||||||||
52 | |||||||||||||||||||||||
53 | Total Funcionário | 1.612,70 | 3.633,47 | 2.020,77 | |||||||||||||||||||
54 | |||||||||||||||||||||||
55 | 006 | Nasir Galvan | |||||||||||||||||||||
56 | |||||||||||||||||||||||
57 | Vencimento | 3.080,00 | |||||||||||||||||||||
58 | Subsídio Alimentação - Géneros | 153,47 | |||||||||||||||||||||
59 | Segurança Social | 338,80 | |||||||||||||||||||||
60 | IRS | 816,00 | |||||||||||||||||||||
61 | Desconto Espécie | 153,47 | |||||||||||||||||||||
62 | Compensação do trabalho | 0,00 | |||||||||||||||||||||
63 | Garantia Comp. do trabalho | 0,00 | |||||||||||||||||||||
64 | |||||||||||||||||||||||
65 | |||||||||||||||||||||||
66 | Total Funcionário | 1.925,20 | 3.233,47 | 1.308,27 | |||||||||||||||||||
67 | |||||||||||||||||||||||
68 | 007 | Mila Schneider | |||||||||||||||||||||
69 | |||||||||||||||||||||||
70 | Vencimento | 5.000,00 | |||||||||||||||||||||
71 | Subsídio Alimentação - Géneros | 153,47 | |||||||||||||||||||||
72 | Coverflex isento | 5,59 | |||||||||||||||||||||
73 | Coverflex tributável IRS | 988,90 | |||||||||||||||||||||
74 | Segurança Social | 550,00 | |||||||||||||||||||||
75 | IRS | 2.114,00 | |||||||||||||||||||||
76 | Desconto Espécie | 153,47 | |||||||||||||||||||||
77 | Descontos Urban Sport | 45,51 | |||||||||||||||||||||
78 | Descontos Coverflex | 994,49 | |||||||||||||||||||||
79 | Compensação do trabalho | 0,00 | |||||||||||||||||||||
80 | Garantia Comp. do trabalho | 0,00 | |||||||||||||||||||||
81 | |||||||||||||||||||||||
82 | |||||||||||||||||||||||
83 | Total Funcionário | 2.290,49 | 6.147,96 | 3.857,47 | |||||||||||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
D40,D68,D55 | D40 | =Z40 |
Objective Table
Objective Table.xlsx | |||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | |||
1 | SUMMARY (08- 2022) | ||||||||||||||||||||||||||||
2 | Company Name | ||||||||||||||||||||||||||||
3 | |||||||||||||||||||||||||||||
4 | Translator Controller | Vencimento | Subsídio Alimentação - Géneros | Ajudas de Custo - Nacional | Prémio | Kms Viatura Própria | Coverflex isento | Coverflex tributável IRS | Segurança Social | IRS | Descontos Seguro de saúde | Descontos Urban Sport | Descontos Coverflex | ||||||||||||||||
5 | Employee Code | Name | Basic Salary | FOOD Allowances | Holiday Pay | Christmas Pay | Trav. Allow. | Extra Payment | Bonus | Compensation | Tranp. Allow. | KM/PerDiem | Coverflex free | Coverflex IRS | Formation | Indemnity | Vacation not enjoyed | Advance | Total Gross | NetPay | Social Security - EE | Social Security - ER | Taxes | Health Insurance | Urban Sport | Coverflex | F.G.C.T. | ||
6 | 1 | Caoimhe Mosley | 329,20 | 20,93 | 0,00 | 0,00 | 0,00 | 0,00 | 0,00 | 139,20 | 718,57 | 1.207,90 | 203,42 | 36,21 | 78,19 | 92,00 | 18,50 | 857,77 | 3,292 | ||||||||||
7 | 5 | Clinton Byrne | 2.930,00 | 153,47 | 0,00 | 0,00 | 0,00 | 0,00 | 0,00 | 550,00 | 3.633,47 | 1.766,17 | 322,30 | 695,88 | 995,00 | 0,00 | 550,00 | 29,3 | |||||||||||
8 | 6 | Nasir Galvan | 3.080,00 | 153,47 | 0,00 | 0,00 | 0,00 | 0,00 | 0,00 | 0,00 | 3.233,47 | 2.078,67 | 338,80 | 731,50 | 816,00 | 0,00 | 0,00 | 30,8 | |||||||||||
9 | 7 | Mila Schneider | 5.000,00 | 153,47 | 5,59 | 988,90 | 6.147,96 | 2.443,96 | 550,00 | 1187,50 | 2.114,00 | 0,00 | 45,51 | 994,49 | 50 | ||||||||||||||
10 | 8 | Dillon Salas | 0,00 | 0,00 | 1.302,50 | 1.302,50 | -197,00 | 0,00 | 0,00 | 197,00 | 0,00 | 1.302,50 | 0 | ||||||||||||||||
11 | 11 | Myla Valencia | 3.250,00 | 153,47 | 200,00 | 50,00 | 3.653,47 | 3.245,97 | 357,50 | 929,00 | 0,00 | 50,00 | 32,5 | ||||||||||||||||
12 | 12 | Elis Martin | 2.080,00 | 153,47 | 1.463,00 | 3.696,47 | 960,77 | 228,80 | 494,00 | 1.006,00 | 37,90 | 1.463,00 | 20,8 | ||||||||||||||||
13 | 13 | Nishat Dillard | 1.925,00 | 153,47 | 245,00 | 1.300,00 | 3.623,47 | 929,02 | 211,75 | 457,19 | 915,00 | 22,70 | 1.545,00 | 17,25 | |||||||||||||||
14 | 15 | Kean Contreras | 3.820,00 | 153,47 | 200,00 | 336,98 | 4.510,45 | 2.619,27 | 420,20 | 907,25 | 1.134,00 | 0,00 | 336,98 | 38,2 | |||||||||||||||
15 | 17 | Shola Prince | 1.950,00 | 153,47 | 200,00 | 621,34 | 2.924,81 | 1.425,97 | 214,50 | 463,13 | 663,00 | 0,00 | 621,34 | 19,5 | |||||||||||||||
16 | 21 | Maureen Jenkins | 1.300,00 | 153,47 | 403,93 | 1.857,40 | 994,47 | 143,00 | 308,75 | 316,00 | 0,00 | 403,93 | 13 | ||||||||||||||||
17 | 22 | Manahil Mason | 1.350,00 | 153,47 | 403,93 | 1.907,40 | 1.005,97 | 148,50 | 320,63 | 349,00 | 0,00 | 403,93 | 13,5 | ||||||||||||||||
18 | 23 | Kayan Burris | 2.925,00 | 153,47 | 3.078,47 | 1.929,72 | 321,75 | 694,69 | 827,00 | 0,00 | 0,00 | 29,25 | |||||||||||||||||
Summary_08 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
S6:S18 | S6 | =SUM(C6:Q6) |
T6:T18 | T6 | =S6-U6-W6-X6-Y6-Z6-R6 |
U6:U18 | U6 | =+(C6+E6+F6+K6+H6+Q6)*0.11 |
V12:V18,V10,V6:V8 | V6 | =(C6+E6+F6+H6+I6+K6+Q6)*23.75% |
V9 | V9 | =(C9+E9+F9+H9+K9+Q9)*23.75% |
Z6:Z18 | Z6 | =M6+N6 |
AA14:AA18,AA6:AA12 | AA6 | =C6*1% |
AA13 | AA13 | =1725*1% |
C11 | C11 | =2900+350 |
C14 | C14 | =3560+260 |