Array Function? Find FTE changes before 03/26/2023 that are diff from after this date

**wherdzik** · 05-24-2023, 04:03 PM

I dont know if this is possible but I have a large dataset with many employees. Each employee has repeating entries which identify any effective changes to their record. What I am interested in is to identify any FTE changes that happened to the first available record before 03/26/2023 to the first available record after 03/26/2023. The date I am referring to is the effective date.

There are two sheets contained in this file:

1. Desired Ouptut: basically what I want the formula to show me which are the instances of the changes before 03/26 and after 03/26
2. This is the actual data to filter.

This may be an impossible one, not sure or maybe I am over thinking it.

**AliGW** · 05-25-2023, 01:03 AM

See if this works for you:

Please Login or Register  to view this content.

**wherdzik** · 05-25-2023, 08:01 AM

Wow! Cool formula. When I added the formula to the real data I did get other data that started to appear that I did not want to.
I attached a file and you can see that Vatta, Alora is coming in but has no changes to FTE or Salary. Not sure if there is a way to prevent that?

Thanks!

**AliGW** · 05-25-2023, 08:06 AM

I don't see where you mentioned salary in the original post - I'll take another look.

**AliGW** · 05-25-2023, 08:58 AM

Try this:

Please Login or Register  to view this content.

**AliGW** · 05-25-2023, 09:13 AM

Or this (if you want Excel to generate the column headers as well):

Please Login or Register  to view this content.

**AliGW** · 05-25-2023, 10:18 AM

In case you want to include changes in the status column, too:

Please Login or Register  to view this content.

**AliGW** · 05-25-2023, 11:07 AM

Seen, but no feedback ... Is this solved?

**wherdzik** · 05-25-2023, 11:58 AM

I just had a chance to put into my master file but I cannot figure some stuff out. My actual data has more columns than what I have provided because I wanted to keep it simple but it back fired. I tired to change but it was not working. I do not know what the 1,3,6 and 10,11 Arrays are..I think they are referring to the colums but I am not sure how to change. I added extra columns so you can see. I didnt populate any data but just put them in as place holders. Thsi array formula also has to be in a new sheet not in the same sheet as where the data is. NOt sure if that makes a difference in the formula.
Can you please guide me or teach me how to fish?

**AliGW** · 05-25-2023, 12:10 PM

I will look tomorrow - I am now offline for the evening.

**wherdzik** · 05-25-2023, 01:44 PM

Thank you!

**John Vergara** · 05-25-2023, 02:42 PM

Hi to all!

Check this option:

Please Login or Register  to view this content.

Blessings!

**AliGW** · 05-25-2023, 04:32 PM

I am offering this as I don't think John's takes salary differences into account.

Please Login or Register  to view this content.

Thsi array formula also has to be in a new sheet not in the same sheet as where the data is. NOt sure if that makes a difference in the formula.

Only in the first three variables, which need to have the sheet name in the array references.

**John Vergara** · 05-25-2023, 05:31 PM

Hi again to both!

Thx AliGW for told me about the salaries. I fix the formula:

Please Login or Register  to view this content.

Check file. Blessings!

**Bo_Ry** · 05-26-2023, 01:30 AM

Please try

=LET(a,A1:A51,f,F1:F51,k,K1:K51,n,N1:N51,D,LAMBDA(d,DROP(VSTACK(d,0),1)),
c,(a=D(a))*(D(n)>=DATE(2023,3,26))*((f<>(D(f)))+(k<>(D(k)))),FILTER(A1:O51,c+DROP(VSTACK(1,c),-1)))

**AliGW** · 05-26-2023, 01:39 AM

@Bo_Ry

How would you build in changes in the status column?

EDIT: Never mind - I worked it out:

Please Login or Register  to view this content.

I am curious to know how this works - how does it find the row with the date BEFORE 26/03/2023?

**Bo_Ry** · 05-26-2023, 02:16 AM

how does it find the row with the date BEFORE 26/03/2023?

add this (n<DATE(2023,3,26))

=LET(a,A1:A51,f,F1:F51,k,K1:K51,g,G1:G51,n,N1:N51,
d,LAMBDA(d,DROP(VSTACK(d,0),1)),
c,(a=d(a))*(d(n)>=DATE(2023,3,26))*(n<DATE(2023,3,26))*((f<>(d(f)))+(g<>(d(g)))+(k<>(d(k)))),
FILTER(A1:O51,c+DROP(VSTACK(1,c),-1)))

**AliGW** · 05-26-2023, 02:19 AM

That wasn't my question. The formula already produces the right results - I was asking how. But I think I have worked it out - it relies on those two rows being consecutive rows in the table.

Would adding the less than date allow it to work if the rows were NOT consecutive?

**wherdzik** · 05-26-2023, 07:05 AM

Thank you guys! Wow...you guys are amazing. Appreciate all your help!

**AliGW** · 05-26-2023, 07:06 AM

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

Array Function? Find FTE changes before 03/26/2023 that are diff from after this date

LinkBack

Thread Tools

Rate This Thread

Display

Array Function? Find FTE changes before 03/26/2023 that are diff from after this date

Re: Array Function? Find FTE changes before 03/26/2023 that are diff from after this date

Re: Array Function? Find FTE changes before 03/26/2023 that are diff from after this date

Re: Array Function? Find FTE changes before 03/26/2023 that are diff from after this date

Re: Array Function? Find FTE changes before 03/26/2023 that are diff from after this date

Re: Array Function? Find FTE changes before 03/26/2023 that are diff from after this date

Re: Array Function? Find FTE changes before 03/26/2023 that are diff from after this date

Re: Array Function? Find FTE changes before 03/26/2023 that are diff from after this date

Re: Array Function? Find FTE changes before 03/26/2023 that are diff from after this date

Re: Array Function? Find FTE changes before 03/26/2023 that are diff from after this date

Re: Array Function? Find FTE changes before 03/26/2023 that are diff from after this date

Re: Array Function? Find FTE changes before 03/26/2023 that are diff from after this date

Re: Array Function? Find FTE changes before 03/26/2023 that are diff from after this date

Re: Array Function? Find FTE changes before 03/26/2023 that are diff from after this date

Re: Array Function? Find FTE changes before 03/26/2023 that are diff from after this date

Re: Array Function? Find FTE changes before 03/26/2023 that are diff from after this date

Re: Array Function? Find FTE changes before 03/26/2023 that are diff from after this date

Re: Array Function? Find FTE changes before 03/26/2023 that are diff from after this date

Re: Array Function? Find FTE changes before 03/26/2023 that are diff from after this date

Re: Array Function? Find FTE changes before 03/26/2023 that are diff from after this date

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Date error: 29/2/2023 shows Wed 01/03/2023

A formula that checks if the date in column DR is equal to, or greater than March 15, 2023

Formula for dates to quarters? 7/1/2023 to 3Q23; 12/1/2023 to 4Q23; 1/1/2024 to 1Q24

Need Formula to Convert this Date Format 2023.02.28 to this 28-Feb-2023

Timeline of pivot table shows the year 2023 where there is no data from 2023 year

[SOLVED] Index Match? - Find value in given row then return diff value in same row and diff column

[SOLVED] Error 2023 - triggered by variable - Cant find mistake

Bookmarks

Bookmarks

Posting Permissions