Forum Discussion
Help with Formula
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?
=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.
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.
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?
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!
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.
