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

P

Pete van Almond

New Member
Joined
Aug 24, 2022
Messages
1
Office Version
  1. 2016
Platform
  1. 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


Basis Table.xlsx
ABCDEFGHIJKLMNOPQRSTU
38Total Funcionário182,491.207,901.025,41
39
40005Clinton Byrne
41
42Vencimento2.930,00
43Subsídio Alimentação - Géneros153,47
44Coverflex tributável IRS550,00
45Segurança Social322,30
46IRS995,00
47Desconto Espécie153,47
48Descontos Coverflex550,00
49Compensação do trabalho0,00
50Garantia Comp. do trabalho0,00
51
52
53Total Funcionário1.612,703.633,472.020,77
54
55006Nasir Galvan
56
57Vencimento3.080,00
58Subsídio Alimentação - Géneros153,47
59Segurança Social338,80
60IRS816,00
61Desconto Espécie153,47
62Compensação do trabalho0,00
63Garantia Comp. do trabalho0,00
64
65
66Total Funcionário1.925,203.233,471.308,27
67
68007Mila Schneider
69
70Vencimento5.000,00
71Subsídio Alimentação - Géneros153,47
72Coverflex isento5,59
73Coverflex tributável IRS988,90
74Segurança Social550,00
75IRS2.114,00
76Desconto Espécie153,47
77Descontos Urban Sport45,51
78Descontos Coverflex994,49
79Compensação do trabalho0,00
80Garantia Comp. do trabalho0,00
81
82
83Total Funcionário2.290,496.147,963.857,47
Sheet1
Cell Formulas
RangeFormula
D40,D68,D55D40=Z40




Objective Table

Objective Table.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAA
1SUMMARY (08- 2022)
2Company Name
3
4Translator ControllerVencimentoSubsídio Alimentação - GénerosAjudas de Custo - NacionalPrémioKms Viatura PrópriaCoverflex isentoCoverflex tributável IRSSegurança SocialIRSDescontos Seguro de saúdeDescontos Urban SportDescontos Coverflex
5Employee CodeNameBasic SalaryFOOD AllowancesHoliday PayChristmas PayTrav. Allow.Extra PaymentBonusCompensationTranp. Allow.KM/PerDiemCoverflex freeCoverflex IRSFormationIndemnityVacation not enjoyedAdvanceTotal GrossNetPaySocial Security - EESocial Security - ERTaxesHealth InsuranceUrban SportCoverflexF.G.C.T.
61Caoimhe Mosley329,2020,930,000,000,000,000,00139,20718,571.207,90203,4236,2178,1992,0018,50857,773,292
75Clinton Byrne2.930,00153,470,000,000,000,000,00550,003.633,471.766,17322,30695,88995,000,00550,0029,3
86Nasir Galvan3.080,00153,470,000,000,000,000,000,003.233,472.078,67338,80731,50816,000,000,0030,8
97Mila Schneider5.000,00153,475,59988,906.147,962.443,96550,001187,502.114,000,0045,51994,4950
108Dillon Salas0,000,001.302,501.302,50-197,000,000,00197,000,001.302,500
1111Myla Valencia3.250,00153,47200,0050,003.653,473.245,97357,50929,000,0050,0032,5
1212Elis Martin2.080,00153,471.463,003.696,47960,77228,80494,001.006,0037,901.463,0020,8
1313Nishat Dillard1.925,00153,47245,001.300,003.623,47929,02211,75457,19915,0022,701.545,0017,25
1415Kean Contreras3.820,00153,47200,00336,984.510,452.619,27420,20907,251.134,000,00336,9838,2
1517Shola Prince1.950,00153,47200,00621,342.924,811.425,97214,50463,13663,000,00621,3419,5
1621Maureen Jenkins1.300,00153,47403,931.857,40994,47143,00308,75316,000,00403,9313
1722Manahil Mason1.350,00153,47403,931.907,401.005,97148,50320,63349,000,00403,9313,5
1823Kayan Burris2.925,00153,473.078,471.929,72321,75694,69827,000,000,0029,25
Summary_08
Cell Formulas
RangeFormula
S6:S18S6=SUM(C6:Q6)
T6:T18T6=S6-U6-W6-X6-Y6-Z6-R6
U6:U18U6=+(C6+E6+F6+K6+H6+Q6)*0.11
V12:V18,V10,V6:V8V6=(C6+E6+F6+H6+I6+K6+Q6)*23.75%
V9V9=(C9+E9+F9+H9+K9+Q9)*23.75%
Z6:Z18Z6=M6+N6
AA14:AA18,AA6:AA12AA6=C6*1%
AA13AA13=1725*1%
C11C11=2900+350
C14C14=3560+260
 

Attachments

  • Snippet of Basis and Objective Tables.png
    Snippet of Basis and Objective Tables.png
    141.3 KB · Views: 5

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Trying to input values into a clear table from a Primavera BSS generated output
and trying_to_input_values_into_a_clear_table_from_a
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Conditional Formatting of Green vs. Green/White, etc; Diagonal Border Not Possible
2
Replies
11
Views
763
MARK858
MARK858
V
  • Question
Returning Randomly Selected Value from Table based on Criteria
Replies
4
Views
595
Viau1337
V
R
  • Question
How to get a list of Distinct values from a Table that is itself the output form a query (variable length)
Replies
0
Views
872
Reshaw
R
C
  • Question
Sort data from one table into two automatically - No VBA
Replies
2
Views
907
Veritan
V
M
  • Question
Complicated transpose of data with conditions
Replies
3
Views
972
mahmed1
M

Forum statistics

Threads
1,215,013
Messages
6,122,690
Members
449,092
Latest member
snoom82

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top