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

T

Tashat

Board Regular
Joined
Jan 12, 2005
Messages
135
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

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
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.
 
Upvote 0
KevCarter said:
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.
Click to expand...
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??
 
Upvote 0
Tashat said:
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??
Click to expand...
You bet! I'll put it together and shoot it back shortly...
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Fluff said:
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.
Click to expand...
Absolutely amazing as always! Thank you! This is helping me so much to learn. I'm getting better at adapting formulas now. Thank you.
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question
How to filter/lookup rows of data based on the latest date in a cell
Replies
8
Views
886
Peter_SSs
Peter_SSs
R
  • Solved
Stumped on counting patients unique visits to a clinic
Replies
7
Views
363
Fluff
Fluff
T
  • Question
Filtering dates
Replies
5
Views
379
tigerzen
T
F
  • Solved
Formula determine if a date falls between a specific M/D range for multiple years for YTD comparison.
Replies
6
Views
698
flashgordie
F
B
  • Question
VBA to show and change list of names depending on selection
Replies
7
Views
461
mumps
mumps

Forum statistics

Threads
1,214,827
Messages
6,121,818
Members
449,049
Latest member
cybersurfer5000

Share this page

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
Back
Top