Filter formula to show people who haven't entered data in over 28 days

Tashat

Board Regular
Joined
Jan 12, 2005
Messages
118
Office Version
  1. 365
Platform
  1. Windows
Hi all

I'm looking for some help with the below problem - I believe I need to use the filter (array??) formula function in Excel 365, but I'm not familiar enough with it at the moment to know how to write the formula myself. =filter is as far as I've got 😁

I have a list of people who enter data into an online form. The should be entering the data on a daily basis. The table below I have created based on a raw data sheet and it shows all employees whether they are current or resigned or there is a vacancy. I want to use the formula function to present a list of all the "current" (column D) employees emails addresses (column A) who haven't entered anything into the online form for more than 28 days (column G). I have a lot of data and whilst I can use the standard filters using the drop down list, I am always against the clock and so to have something that just presents the names without having to click on drop down filter lists would be really helpful. It would also be helpful if, alongside the email address, I was able to show the name of the manager (column B). As always, any help would be much appreciated.

Book1
ABCDEFG
1CEM / CA Email AddressManagerCEM or CACurrent / ResignedEntered Data on FormNumber of Entries MadeDate/Time of Last Entry
2amy@test.co.ukJustinCEMCurrentYes3026/11/2020 17:34:07
3john@test.co.ukClaireCEMCurrentYes10602/11/2020 12:02:47
4sam@test.co.ukPaulCEMCurrentYes2502/12/2020 15:07:52
5dave@test.co.ukPaulCACurrentYes3003/10/2020 19:24:15
6becka@test.co.ukJustinCACurrentYes1904/12/2020 14:47:13
7al@test.co.ukJustinCEMResignedYes202/11/2020 10:07:30
8sarah@test.co.ukTinaCEMVacancyNo0
9sian@test.co.ukTinaCEMCurrentYes4315/10/2020 20:23:13
10danielle@test.co.ukPaulCEMCurrentYes2904/12/2020 09:06:21
11robert@test.co.ukClaireCEMCurrentYes6104/12/2020 16:22:18
12stephen@test.co.ukJustinCEMCurrentYes11701/11/2020 15:50:41
13nessa@test.co.ukTinaCEMCurrentYes7104/12/2020 19:05:20
14geoff@test.co.ukClaireCACurrentYes9304/12/2020 17:48:08
15Eloise@test.co.ukTinaCEMCurrentYes1902/12/2020 13:37:47
16lexi@test.co.ukClaireCEMCurrentYes2801/12/2020 17:25:16
Sheet1
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

KevCarter

Board Regular
Joined
Dec 7, 2013
Messages
147
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Something like this? I returned all the columns and we can fine tune from there.

Filter Response.xlsx
ABCDEFGHIJKLMNO
1CEM / CA Email AddressManagerCEM or CACurrent / ResignedEntered Data on FormNumber of Entries MadeDate/Time of Last EntryCEM / CA Email AddressManagerCEM or CACurrent / ResignedEntered Data on FormNumber of Entries MadeDate/Time of Last Entry
2amy@test.co.ukJustinCEMCurrentYes3026/11/2020 17:34:07geoff@test.co.ukClaireCACurrentYes93April 12, 2020
3john@test.co.ukClaireCEMCurrentYes1062/11/20 12:02john@test.co.ukClaireCEMCurrentYes106February 11, 2020
4sam@test.co.ukPaulCEMCurrentYes252/12/20 15:07lexi@test.co.ukClaireCEMCurrentYes28January 12, 2020
5dave@test.co.ukPaulCACurrentYes303/10/20 19:24robert@test.co.ukClaireCEMCurrentYes61April 12, 2020
6becka@test.co.ukJustinCACurrentYes1912/1/20 0:00stephen@test.co.ukJustinCEMCurrentYes117January 11, 2020
7al@test.co.ukJustinCEMResignedYes22/11/20 10:07danielle@test.co.ukPaulCEMCurrentYes29April 12, 2020
8sarah@test.co.ukTinaCEMVacancyNo0dave@test.co.ukPaulCACurrentYes30March 10, 2020
9sian@test.co.ukTinaCEMCurrentYes4315/10/2020 20:23:13sam@test.co.ukPaulCEMCurrentYes25February 12, 2020
10danielle@test.co.ukPaulCEMCurrentYes294/12/20 9:06Eloise@test.co.ukTinaCEMCurrentYes19February 12, 2020
11robert@test.co.ukClaireCEMCurrentYes614/12/20 16:22nessa@test.co.ukTinaCEMCurrentYes71April 12, 2020
12stephen@test.co.ukJustinCEMCurrentYes1171/11/20 15:50
13nessa@test.co.ukTinaCEMCurrentYes714/12/20 19:05
14geoff@test.co.ukClaireCACurrentYes934/12/20 17:48
15Eloise@test.co.ukTinaCEMCurrentYes192/12/20 13:37
16lexi@test.co.ukClaireCEMCurrentYes281/12/20 17:25
Sheet1
Cell Formulas
RangeFormula
I2:O11I2=SORT(FILTER(A:G,(D:D="Current")*(G:G<=TODAY()-28)*(A:A<>""),""),{2,1})
Dynamic array formulas.
 

Tashat

Board Regular
Joined
Jan 12, 2005
Messages
118
Office Version
  1. 365
Platform
  1. Windows
Something like this? I returned all the columns and we can fine tune from there.

Filter Response.xlsx
ABCDEFGHIJKLMNO
1CEM / CA Email AddressManagerCEM or CACurrent / ResignedEntered Data on FormNumber of Entries MadeDate/Time of Last EntryCEM / CA Email AddressManagerCEM or CACurrent / ResignedEntered Data on FormNumber of Entries MadeDate/Time of Last Entry
2amy@test.co.ukJustinCEMCurrentYes3026/11/2020 17:34:07geoff@test.co.ukClaireCACurrentYes93April 12, 2020
3john@test.co.ukClaireCEMCurrentYes1062/11/20 12:02john@test.co.ukClaireCEMCurrentYes106February 11, 2020
4sam@test.co.ukPaulCEMCurrentYes252/12/20 15:07lexi@test.co.ukClaireCEMCurrentYes28January 12, 2020
5dave@test.co.ukPaulCACurrentYes303/10/20 19:24robert@test.co.ukClaireCEMCurrentYes61April 12, 2020
6becka@test.co.ukJustinCACurrentYes1912/1/20 0:00stephen@test.co.ukJustinCEMCurrentYes117January 11, 2020
7al@test.co.ukJustinCEMResignedYes22/11/20 10:07danielle@test.co.ukPaulCEMCurrentYes29April 12, 2020
8sarah@test.co.ukTinaCEMVacancyNo0dave@test.co.ukPaulCACurrentYes30March 10, 2020
9sian@test.co.ukTinaCEMCurrentYes4315/10/2020 20:23:13sam@test.co.ukPaulCEMCurrentYes25February 12, 2020
10danielle@test.co.ukPaulCEMCurrentYes294/12/20 9:06Eloise@test.co.ukTinaCEMCurrentYes19February 12, 2020
11robert@test.co.ukClaireCEMCurrentYes614/12/20 16:22nessa@test.co.ukTinaCEMCurrentYes71April 12, 2020
12stephen@test.co.ukJustinCEMCurrentYes1171/11/20 15:50
13nessa@test.co.ukTinaCEMCurrentYes714/12/20 19:05
14geoff@test.co.ukClaireCACurrentYes934/12/20 17:48
15Eloise@test.co.ukTinaCEMCurrentYes192/12/20 13:37
16lexi@test.co.ukClaireCEMCurrentYes281/12/20 17:25
Sheet1
Cell Formulas
RangeFormula
I2:O11I2=SORT(FILTER(A:G,(D:D="Current")*(G:G<=TODAY()-28)*(A:A<>""),""),{2,1})
Dynamic array formulas.
That's fantastic! Thank you so much. Can I ask one more question - is there a way of not including some of the columns? I can see the range of a:g in the formula, is there a way to return columns A:C and then columns F:G??
 

KevCarter

Board Regular
Joined
Dec 7, 2013
Messages
147
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
That's fantastic! Thank you so much. Can I ask one more question - is there a way of not including some of the columns? I can see the range of a:g in the formula, is there a way to return columns A:C and then columns F:G??
You bet! I'll put it together and shoot it back shortly...
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,086
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How about
+Fluff v2.xlsm
ABCDEFGHIJKLMN
1CEM / CA Email AddressManagerCEM or CACurrent / ResignedEntered Data on FormNumber of Entries MadeDate/Time of Last EntryCEM / CA Email AddressManagerCEM or CANumber of Entries MadeDate/Time of Last Entry
2amy@test.co.ukJustinCEMCurrentYes3026/11/2020john@test.co.ukClaireCEM10602/11/2020 12:02
3john@test.co.ukClaireCEMCurrentYes10602/11/2020dave@test.co.ukPaulCA3003/10/2020 19:24
4sam@test.co.ukPaulCEMCurrentYes2502/12/2020sian@test.co.ukTinaCEM4315/10/2020 20:23
5dave@test.co.ukPaulCACurrentYes3003/10/2020stephen@test.co.ukJustinCEM11701/11/2020 15:50
6becka@test.co.ukJustinCACurrentYes1904/12/2020
7al@test.co.ukJustinCEMResignedYes202/11/2020
8sarah@test.co.ukTinaCEMVacancyNo0
9sian@test.co.ukTinaCEMCurrentYes4315/10/2020
10danielle@test.co.ukPaulCEMCurrentYes2904/12/2020
11robert@test.co.ukClaireCEMCurrentYes6104/12/2020
12stephen@test.co.ukJustinCEMCurrentYes11701/11/2020
13nessa@test.co.ukTinaCEMCurrentYes7104/12/2020
14geoff@test.co.ukClaireCACurrentYes9304/12/2020
15Eloise@test.co.ukTinaCEMCurrentYes1902/12/2020
16lexi@test.co.ukClaireCEMCurrentYes2801/12/2020
17
18
19
20
Main
Cell Formulas
RangeFormula
J2:N5J2=FILTER(FILTER(A2:G23,(D2:D23="Current")*(G2:G23<=TODAY()-28)),COUNTIF(J1:O1,A1:G1))
Dynamic array formulas.
 

KevCarter

Board Regular
Joined
Dec 7, 2013
Messages
147
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Filter Response.xlsx
ABCDEFGHIJKLM
1CEM / CA Email AddressManagerCEM or CACurrent / ResignedEntered Data on FormNumber of Entries MadeDate/Time of Last EntryCEM / CA Email AddressManagerCEM or CANumber of Entries MadeDate/Time of Last Entry
2amy@test.co.ukJustinCEMCurrentYes3026/11/2020 17:34:07geoff@test.co.ukClaireCA934/12/20
3john@test.co.ukClaireCEMCurrentYes1062/11/20 12:02john@test.co.ukClaireCEM1062/11/20
4sam@test.co.ukPaulCEMCurrentYes252/12/20 15:07lexi@test.co.ukClaireCEM281/12/20
5dave@test.co.ukPaulCACurrentYes303/10/20 19:24robert@test.co.ukClaireCEM614/12/20
6becka@test.co.ukJustinCACurrentYes1912/1/20 0:00stephen@test.co.ukJustinCEM1171/11/20
7al@test.co.ukJustinCEMResignedYes22/11/20 10:07danielle@test.co.ukPaulCEM294/12/20
8sarah@test.co.ukTinaCEMVacancyNo0dave@test.co.ukPaulCA303/10/20
9sian@test.co.ukTinaCEMCurrentYes4315/10/2020 20:23:13sam@test.co.ukPaulCEM252/12/20
10danielle@test.co.ukPaulCEMCurrentYes294/12/20 9:06Eloise@test.co.ukTinaCEM192/12/20
11robert@test.co.ukClaireCEMCurrentYes614/12/20 16:22nessa@test.co.ukTinaCEM714/12/20
12stephen@test.co.ukJustinCEMCurrentYes1171/11/20 15:50
13nessa@test.co.ukTinaCEMCurrentYes714/12/20 19:05
14geoff@test.co.ukClaireCACurrentYes934/12/20 17:48
15Eloise@test.co.ukTinaCEMCurrentYes192/12/20 13:37
16lexi@test.co.ukClaireCEMCurrentYes281/12/20 17:25
Sheet1
Cell Formulas
RangeFormula
I2:M11I2=SORT(FILTER(FILTER(A:G,(D:D="Current")*(G:G<=TODAY()-28)*(A:A<>""),""),{1,1,1,0,0,1,1}),{2,1})
Dynamic array formulas.
Cells with Conditional Formatting
CellConditionCell FormatStop If True
I2:M100Expression=AND($I2<>"",ISODD(ROW()))textNO
I2:M100Expression=$I2<>""textNO
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,086
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

If a person could have multiple entries, another option
+Fluff v2.xlsm
ABCDEFGHIJKLMN
1CEM / CA Email AddressManagerCEM or CACurrent / ResignedEntered Data on FormNumber of Entries MadeDate/Time of Last EntryCEM / CA Email AddressManagerCEM or CANumber of Entries MadeDate/Time of Last Entry
2amy@test.co.ukJustinCEMCurrentYes3026/11/2020 17:34dave@test.co.ukPaulCA3003/10/2020 19:24
3john@test.co.ukClaireCEMCurrentYes10602/11/2020 12:02sian@test.co.ukTinaCEM4315/10/2020 20:23
4john@test.co.ukClaireCEMCurrentYes10602/12/2020 12:02stephen@test.co.ukJustinCEM11701/11/2020 15:50
5sam@test.co.ukPaulCEMCurrentYes2502/12/2020 15:07
6dave@test.co.ukPaulCACurrentYes3003/10/2020 19:24
7becka@test.co.ukJustinCACurrentYes1904/12/2020 14:47
8al@test.co.ukJustinCEMResignedYes202/11/2020 10:07
9sarah@test.co.ukTinaCEMVacancyNo0
10sian@test.co.ukTinaCEMCurrentYes4315/10/2020 20:23
11danielle@test.co.ukPaulCEMCurrentYes2904/12/2020 09:06
12robert@test.co.ukClaireCEMCurrentYes6104/12/2020 16:22
13stephen@test.co.ukJustinCEMCurrentYes11701/11/2020 15:50
14nessa@test.co.ukTinaCEMCurrentYes7104/12/2020 19:05
15geoff@test.co.ukClaireCACurrentYes9304/12/2020 17:48
16Eloise@test.co.ukTinaCEMCurrentYes1902/12/2020 13:37
17lexi@test.co.ukClaireCEMCurrentYes2801/12/2020 17:25
18lexi@test.co.ukClaireCEMCurrentYes2801/11/2020 17:25
19
20
21
22
23
Main
Cell Formulas
RangeFormula
J2:N4J2=FILTER(FILTER(A2:G24,(D2:D24="Current")*(G2:G24<=TODAY()-28)*(ISNA(MATCH(A2:A24,FILTER(A2:A24,(D2:D24="Current")*(G2:G24>TODAY()-28)),0)))),COUNTIF(J1:O1,A1:G1))
Dynamic array formulas.
 

Tashat

Board Regular
Joined
Jan 12, 2005
Messages
118
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff v2.xlsm
ABCDEFGHIJKLMN
1CEM / CA Email AddressManagerCEM or CACurrent / ResignedEntered Data on FormNumber of Entries MadeDate/Time of Last EntryCEM / CA Email AddressManagerCEM or CANumber of Entries MadeDate/Time of Last Entry
2amy@test.co.ukJustinCEMCurrentYes3026/11/2020john@test.co.ukClaireCEM10602/11/2020 12:02
3john@test.co.ukClaireCEMCurrentYes10602/11/2020dave@test.co.ukPaulCA3003/10/2020 19:24
4sam@test.co.ukPaulCEMCurrentYes2502/12/2020sian@test.co.ukTinaCEM4315/10/2020 20:23
5dave@test.co.ukPaulCACurrentYes3003/10/2020stephen@test.co.ukJustinCEM11701/11/2020 15:50
6becka@test.co.ukJustinCACurrentYes1904/12/2020
7al@test.co.ukJustinCEMResignedYes202/11/2020
8sarah@test.co.ukTinaCEMVacancyNo0
9sian@test.co.ukTinaCEMCurrentYes4315/10/2020
10danielle@test.co.ukPaulCEMCurrentYes2904/12/2020
11robert@test.co.ukClaireCEMCurrentYes6104/12/2020
12stephen@test.co.ukJustinCEMCurrentYes11701/11/2020
13nessa@test.co.ukTinaCEMCurrentYes7104/12/2020
14geoff@test.co.ukClaireCACurrentYes9304/12/2020
15Eloise@test.co.ukTinaCEMCurrentYes1902/12/2020
16lexi@test.co.ukClaireCEMCurrentYes2801/12/2020
17
18
19
20
Main
Cell Formulas
RangeFormula
J2:N5J2=FILTER(FILTER(A2:G23,(D2:D23="Current")*(G2:G23<=TODAY()-28)),COUNTIF(J1:O1,A1:G1))
Dynamic array formulas.
Absolutely amazing as always! Thank you! This is helping me so much to learn. I'm getting better at adapting formulas now. Thank you.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
68,086
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,112
Messages
5,768,167
Members
425,458
Latest member
Jaspal1996

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top