IF(IF(IF... more than 64 nesting level

Hello,

I have a big database containing all the employees of a company (+1000 employees).
For each employee, I have several records (each record on a different row), each record corresponding to a change of the employment contract (change of position, change of location, change of salary), etc.
However I am interested only in the salary change:

The structure for employee Jackson looks like this:

A B C

Jack Jackson 09 October 2019 3047
Jack Jackson 10 January 2020 3047
Jack Jackson 16 March 2020 3047
Jack Jackson 01 April 2020 2438
Jack Jackson 01 May 2020 2133
Jack Jackson 01 June 2020 3047
Jack Jackson 07 September 2020 3047
Jack Jackson 14 September 2020 3047
Jack Jackson 05 October 2020 3047
Jack Jackson 12 October 2020 3047
Jack Jackson 26 October 2020 3047
Jack Jackson 31 October 2020 3047
Jack Jackson 01 November 2020 3047
Jack Jackson 09 November 2020 3047
Jack Jackson 12 December 2020 3047
Jack Jackson 21 December 2020 3047
Jack Jackson 25 December 2020 3047
Jack Jackson 04 January 2021 3047
Jack Jackson 09 January 2021 3047
Jack Jackson 18 January 2021 3047
Jack Jackson 23 January 2021 3047
Jack Jackson 01 February 2021 3047
Jack Jackson 06 February 2021 3047
Jack Jackson 15 February 2021 3047
Jack Jackson 20 February 2021 3047
Jack Jackson 01 March 2021 3047
Jack Jackson 06 March 2021 3047
Jack Jackson 15 March 2021 3047
Jack Jackson 20 March 2021 3047
Jack Jackson 29 March 2021 3047
Jack Jackson 03 April 2021 3047
Jack Jackson 09 April 2021 3047
Jack Jackson 17 April 2021 3047
Jack Jackson 23 April 2021 3047
Jack Jackson 30 April 2021 3047
Jack Jackson 10 May 2021 3047
Jack Jackson 15 May 2021 3047
Jack Jackson 24 May 2021 3047
Jack Jackson 29 May 2021 3047
Jack Jackson 07 June 2021 3047
Jack Jackson 12 June 2021 3047
Jack Jackson 21 June 2021 3047
Jack Jackson 26 June 2021 3047
Jack Jackson 05 July 2021 3047
Jack Jackson 10 July 2021 3047
Jack Jackson 19 July 2021 3047
Jack Jackson 24 July 2021 3047
Jack Jackson 02 August 2021 4096
Jack Jackson 07 August 2021 3047
Jack Jackson 16 August 2021 3047
Jack Jackson 21 August 2021 3047
Jack Jackson 30 August 2021 3047
Jack Jackson 06 September 2021 3047
Jack Jackson 15 September 2021 3047

I want to find a function on column D to do the following:

Check if A1=A2; if True Check if C1<>C2; if True display value C2; if False see bellow
Check if A1=A3; if True Check if C1<>C3; if True display value C3; if False see bellow
Check if A1=A4; if True Check if C1<>C4; if True display value C4; if False see bellow
........................................................................................................................................
Check if A1=An; if True Check if C1<>Cn; if True display value Cn; if False "" where n can be up to 100

I have tried to put IF inside another IF function but my Excel is limited to 64 nesting eggs.


Basically what I want is that on the first row of each employee to display on the columns to the right the salary changes from one salary to another.
However, for one particular employee, there can be 50 records with no salary change, whereas for another there can be only 3 records and all of them to have salary change.

I have a tried also IF + VLOOKUP:

=(IF(VLOOKUP(A1,A2:C100,1,FALSE)=A1,VLOOKUP(A1,A2:C100,3,FALSE),"")<>C1,VLOOKUP(A1,A2:C100,3,FALSE),"")

Basically this functions checks to see if the first VLOOKUPed value (Name) from the range A1:C100 is identical with the value from A1 (Jack Jackson);
If this condition is true, it check to see if the second VLOOKUPed value (Salary) from the range A1:C100 is different from the value in C1 (3047); if the condition is TRUE also it brings the value from the range A1:C100; however if not, it brings nothing.

If I want to use the same function inside it, I get to the same problem, that is the limit of nesting levels limited to 64.

Any ideas?

What do you actually want as the result when there has been more than one change?

Surely for each employee you need a separate sheet with list of employees and only record dates when there has been a salary change: your current approach looks very unwieldy.

For example why keep recording the employee name?

with formulas, but is easier with VBA or PQ.
Is that an option ?

First of all thank you for all your responses.
However, the sample provided was just a small batch of the database (8000 rows in total, about 1300 employees), and maybe because of that, the objective was not presented clear enough.
Therefore, I have uploaded an extended sample.

What I need to display in the database sheet is, to the right of the table (from column D onwards) Date 2 | Salary 2 | Date 3 | Salary 3 | Date 4 | Salary 4 | Date 5 | Salary 5 | Date 6 | Salary 6 | and so on.
However these values must be displayed on the first row of each employee, because afterwards I need to bring these values into another sheet using VLOOKUP.
And since I will be using the Name as Lookup value, the formula will bring whatever finds on the first row where it finds the Lookup value.
This is why I need to have all the changes on the first row for each name.

P.S. Basically the formula I had in mind would look like this:

For D2 cell:

IF(A2=A3,IF(C2<>C3,C3,
IF(A2=A4,IF(C2<>C4,C4,
IF(A2=A5,IF(C2<>C5,C5,
IF(A2=A6,IF(C2<>C6,C6,
IF(A2=A7,IF(C2<>C7,C7,
IF(A2=A8,IF(C2<>C8,C8,
IF(A2=A9,IF(C2<>C9,C9,
IF(A2=A10,IF(C2<>C10,C10,
IF(A2=A11,IF(C2<>C11,C11,
IF(A2=A12,IF(C2<>C12,C12,
IF(A2=A13,IF(C2<>C13,C13,
IF(A2=A14,IF(C2<>C14,C14,
IF(A2=A15,IF(C2<>C15,C15,
IF(A2=A16,IF(C2<>C16,C16,
IF(A2=A17,IF(C2<>C17,C17,
IF(A2=A18,IF(C2<>C18,C18,
IF(A2=A19,IF(C2<>C19,C19,
IF(A2=A20,IF(C2<>C20,C20,
IF(A2=A21,IF(C2<>C21,C21,
IF(A2=A22,IF(C2<>C22,C22,
IF(A2=A23,IF(C2<>C23,C23,
IF(A2=A24,IF(C2<>C24,C24,
IF(A2=A25,IF(C2<>C25,C25,
IF(A2=A26,IF(C2<>C26,C26,
IF(A2=A27,IF(C2<>C27,C27,
IF(A2=A28,IF(C2<>C28,C28,
IF(A2=A29,IF(C2<>C29,C29,
IF(A2=A30,IF(C2<>C30,C30,
IF(A2=A31,IF(C2<>C31,C31,
IF(A2=A32,IF(C2<>C32,C32,
IF(A2=A33,IF(C2<>C33,C33,
IF(A2=A34,IF(C2<>C34,C34,
IF(A2=A35,IF(C2<>C35,C35,
IF(A2=A36,IF(C2<>C36,C36,
IF(A2=A37,IF(C2<>C37,C37,
IF(A2=A38,IF(C2<>C38,C38,
IF(A2=A39,IF(C2<>C39,C39,
IF(A2=A40,IF(C2<>C40,C40,
IF(A2=A41,IF(C2<>C41,C41,
IF(A2=A42,IF(C2<>C42,C42,
IF(A2=A43,IF(C2<>C43,C43,
IF(A2=A44,IF(C2<>C44,C44,
IF(A2=A45,IF(C2<>C45,C45,
IF(A2=A46,IF(C2<>C46,C46,
IF(A2=A47,IF(C2<>C47,C47,
IF(A2=A48,IF(C2<>C48,C48,
IF(A2=A49,IF(C2<>C49,C49,
IF(A2=A50,IF(C2<>C50,C50,
IF(A2=A51,IF(C2<>C51,C51,
IF(A2=A52,IF(C2<>C52,C52,
But obviousily it exceeds the number of nesting levels (64)

your reaction had no added value.
8.000 rows, 1.300 employees, so in average 6 records per employee, but your example one employee had 55 !
That's (a lot of work/impossible : make your choise) and not very errorproof with formulas.
Here an example with VBA.
You have MS2019, perhaps somebody else can give a solution with PQ. (I have no experience)

The format used by bsalv is the one i advocated but WHY not limit it to changes in salary rather than report every salary whether changed or not as per Davsth response?

In response to NoJi:

I need to bring these values into another sheet using VLOOKUP.

: the above solution effectively does this.

Update: why don't you provide the sheet which results from the VLOOKUP and therefore possibly provide a single solution [i.e bypass the VLOOKUP stage].

The "solution" in post #6 is a "no-go".

the data in your database is very easy to work with.
The data in the desired layout is "for your eyes only", just for the picture but not workable.
I gave you answer, in my opinion, a fuzzy one, and you're not satisfied, so here it stops for me.
I agree with John Topley !