Dynamically pulling allowances and ignoring certain allowance for each employee

[hananak]

Dear All,

I have a file, in which the data tab fetches the data via Power Query in a table. There is second tab, in which we have to layout the data in a certain way to do some calculations for each employee based on the allowances they are getting. The complications are that certain allowances needs to be ignored when dynamically pulling the allowances and then use xlookup to pull the amounts from the data table based on allowances.

I have attached the picture, the first table reflects they way the data is there in the "Data tab" and the second table shows the way I want the result.

I hoping if this can be done either via Power query or Formulas, all I am trying to do is to avoid doing manual calculations.

Your help would be really appreciated.

Thanks.

 

[hananak]

The formula has an little issue, The R2:R4 and Q2:Q4 is the list of allowances to be excluded. For most of the employees, I have added the allowances to be excluded, then the formula works flawlessly but the moment there is an employee, who has no allowances that should be excluded and whatever the allowances they are getting is fine. The formula does not work because I did not put anything for this particular employee on the R2:R4 and Q2:Q4 - list of allowances, as there is nothing to exclude for them.

I would appreciate if you could fix it.

Thanks for your help!

Another thing - on R2:R4 and Q2:Q4 - list of allowances. If I put for an employee same allowance twice to exclude, then the formula does not exclude that allowance at all. If this can be fixed as well.

Thanks for your help.

 

[hagia_sofia]

The formula has an little issue, The R2:R4 and Q2:Q4 is the list of allowances to be excluded. For most of the employees, I have added the allowances to be excluded, then the formula works flawlessly but the moment there is an employee, who has no allowances that should be excluded and whatever the allowances they are getting is fine. The formula does not work because I did not put anything for this particular employee on the R2:R4 and Q2:Q4 - list of allowances, as there is nothing to exclude for them.

I would appreciate if you could fix it.

Thanks for your help!

Hopefully, e.g. wrapping the original formula into IFERROR could do the trick:

=IFERROR(FILTER(FILTER(B2:B19,(A2:A19=F22)),BYROW(--(FILTER(B2:B19,(A2:A19=F22))=TRANSPOSE(FILTER(R2:R4,Q2:Q4=F22))),LAMBDA(a,SUM(a)))<>1),FILTER(B2:B19,A2:A19=F22))

 

[hananak]

Hopefully, e.g. wrapping the original formula into IFERROR could do the trick:

=IFERROR(FILTER(FILTER(B2:B19,(A2:A19=F22)),BYROW(--(FILTER(B2:B19,(A2:A19=F22))=TRANSPOSE(FILTER(R2:R4,Q2:Q4=F22))),LAMBDA(a,SUM(a)))<>1),FILTER(B2:B19,A2:A19=F22))

Thanks for your reply. There are still two problems.

1) If I do not put anything for an employee in the exclude allowance table, the formula works but when I add employees allowances in the exclude table. It picks the last allowance and show it twice in the final result.

2) If we populate an allowance for same employee twice in the exclude table, it does not exclude that allowance in the final result --> Duplications fails the formula to work.

Please help!

 

[hagia_sofia]

The easiest way to address this would be to create a helper column next to "Amount" column with two values where 1 = the allowance is included, 0 = the allowance is excluded; then you could use a simple FILTER with two conditions, i.e. Employee No and Helper Column.