Help with Formula

[Peter_Browning]

I have a set of data like this:

Reference     Code               Type

12502           FR19511107    Salary

12502           FR19511101    NI Conts

12502           FR19511161    Call Out

12502           FR19511101    Pension

11302           FR19511107    Salary

11302           FR19511101    NI Conts

11302           FR19511161    Overtime

11302           FR19511101    Pension

What I want is to add a new column using a formula where I show the Code for showing for salary for each Reference if the Code ends in 1101 or Type = "Salary"

Here's what I would want returned for this set of data:

Reference     Code               Type        New Code

12502           FR19511107    Salary      FR19511107

12502           FR19511101    NI Conts  FR19511107

12502           FR19511161    Call Out   FR19511161

12502           FR19511101    Pension    FR19511107

11302           FR19611109    Salary       FR19611109

11302           FR19611101    NI Conts   FR19611109

11302           FR19611161    Overtime  FR19611161   

11302           FR19611101    Pension    FR19611109   

Any suggestions as to how this could be done?

[#2]

@Peter_Browning 

=IF(OR(RIGHT(B2,4)="1101",C2="Salary"),INDEX($B$2:$B$9,MATCH(1,($A$2:$A$9=A2)*($C$2:$C$9="Salary"),0)),B2)

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.

[#3]

@Peter_Browning 

In D2:

=IF(OR(RIGHT(B2,4)="1101",C2="salary"),"FR19511107",B2)

Fill down.

[#4]

@Peter_Browning It looks like there's a typo in either your sample data or the expected results that could use some clarification. Regarding reference 11302, type "Salary", you're showing code "FR19511107" in the top table, but "FR19611109" in the expected results table. I'm assuming the typo is in the top table, which should be "FR19611109". If so, another option you could use is the FILTER function, if it's available in your version of Excel. For example:

=IF(RIGHT(B2, 4)="1101", FILTER($B$2:$B$9, ($A$2:$A$9=A2)*($C$2:$C$9="Salary"), B2), B2)

The only logical_test needed for the IF function in this situation is to check if the right four characters of the code are equal to "1101". It's not necessary to also check if the type is equal to "Salary" because, if true, it would just find and return its own code anyways.

The FILTER function has an optional [if_empty] parameter, which can be used to return the original code if no corresponding "Salary" record is found. If that's not even a possibility with your data, though, you can safely exclude that argument. Cheers!

[#5]

@OliverScheurich 

Thank you so much for your help with this.

I have used this formula and it looked like it had worked. I copied the formula down to include all the rows (2170) and it seemed to stop working further down the rows. I copied out a sample for one person and the results of the formula:

The column R shows the results when the data was included in the full listing. The Column U is when I tried applying the formula to the data independently in this cut of it.

Here's the formula for the first row above used in the main data:

=IF(OR(RIGHT(C106,4)="1101",G106="Salary"),INDEX($C$1:$C$2170,MATCH(1,($B$1:$B$2170=B106)*($G$1:$G$2170="Salary"),0)),C106)

Here's the formula used in Column U above:

=IF(OR(RIGHT(C1,4)="1101",G1="Salary"),INDEX($C$1:$C$9,MATCH(1,($B$1:$B$9=B1)*($G$1:$G$9="Salary"),0)),C1)

I'm not sure why there's a difference.

[#6]

@Peter_Browning 

You are welcome. The formula in column R refers to 2170 rows and the formula in column U refers to 9 rows. The screenshot shows 9 rows therefore it's impossible to tell why there are different results. I could only guess why the results are different. Can you attach your file without sensitive data so we can have a look at this?

[#7]

@OliverScheurich It's most likely because the reference number is not unique by employee, but rather represents the pay period cutoff date, or something like that, where it will be the same reference number for all employees for the pay period. As you've stated, this is just a guess, but I'd be willing to bet on it, lol. Additional criteria for the first and last name columns is likely needed to find the unique "Salary" record for each employee...

[#8]

@Peter_Browning The MATCH function will return the first match found where the criteria evaluates to TRUE. Since the results you're getting for a particular employee are not what you expected, it means the criteria is not unique to that employee. The most likely explanation, based on the data you've shared, is the same reference number is being used for more than one employee. Try using additional criteria to return the first match where the reference number, first name and last name are equal to the values in the current row. For example, the formula you used in row 106 can be re-written as follows:

=IF(RIGHT(C106, 4)="1101", INDEX($C$1:$C$2170, MATCH(1, ($B$1:$B$2170=B106)*($D$1:$D$2170=D106)*($E$1:$E$2170=E106)*($G$1:$G$2170="Salary"), 0)), C106)

Or, with the XLOOKUP function (requires Excel 2021 or later):

=IF(RIGHT(C106, 4)="1101", XLOOKUP(1, ($B$1:$B$2170=B106)*($D$1:$D$2170=D106)*($E$1:$E$2170=E106)*($G$1:$G$2170="Salary"), $C$1:$C$2170, C106), C106)

[#9]

@Peter_Browning Can you assist me in creating a formula that works

I want to enter a name in one cell.  Once the name is added then the amount owed to auto populate the amount owing into another cell.  When I change the name, the amount moves to the owing cell.  This sound stupid.  I hope my picture helps.

[#10]

@OliverScheurich 

I am sorry for the delay in replying. Things have been a bit crazy here just at the moment.

I am attaching the data anonymised as requested. I have also found out that the original logic I gave isn't quite correct. Originally I said I wanted to show the code for each employee ending in "1101" to match that of the record for that employee where the Type is "Salary". I now learn that an employee can have either a "Salary" or a "Basic" so either of these we need to look for. It is also possible that an employee could have two "basic" Types, but I am informed in these cases both Basic types will have the same Code.

I hope that makes sense. Seeing the actual data should help.

[#11]

@Peter_Browning 

In the Test Payroll file there isn't a column with unique Reference numbers for each employee. @djclements has explained the issues of this scenario.

=COUNTIF($G$1:G1,$G$1)

I've used this formula to return a unique Reference number in column A for each employee. I simply returns the count of "Employers" from column G up to each row.

Formula in cell R1:

=IF(OR(RIGHT(C1,4)="1101",G1="Salary",G1="Basic"),INDEX($C$1:$C$2169,MATCH(1,($A$1:$A$2169=A1)*(($G$1:$G$2169="Salary")+($G$1:$G$2169="Basic")),0)),C1)

Then this formula returns the Code for Salary or Basic for each Reference if the Type is either Salary or Basic or the Code ends in 1101. As you can see in column R of the sample file the formula returns #NA in e.g. cell R20 since there isn't a Code for Salary or Basic for Reference 4. Reference 4 is the result of the COUNTIF in cell A20 for "Employers" from cell G20.

Formula in cell S1:

=IFERROR(IF(OR(RIGHT(C1,4)="1101",G1="Salary",G1="Basic"),INDEX($C$1:$C$2169,MATCH(1,($A$1:$A$2169=A1)*(($G$1:$G$2169="Salary")+($G$1:$G$2169="Basic")),0)),C1),C1)

You can wrap the formula into IFERROR in order to return the Code of the unique employee if the formula in column R returns #NA. If the formula in column S works as intended the formula from column R isn't needed anymore. Actually i added the formula from column R in order to discuss the potential #NA issue.

[#12]

@OliverScheurich 

Thanks again for looking into this. However, I think the "Employers" is misleading as this stands for Employers Contributions and is just another Pay Element Type - an employee can have 2 as in the case with Payroll Number 12643. The Payroll Numbers (column F) are unique for each employee.