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

T

Tashat

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

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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

O
  • Solved
VBA - Filter columns, cut/paste rows new sheet, pivot table
2
Replies
12
Views
295
Skyybot
Skyybot
A
  • Solved
lookupifs?? lookup value so long as date value is greater than specified date
Replies
5
Views
103
Peter_SSs
Peter_SSs
willow1985
  • Solved
VBA Select all rows from active cell (selected cell) on table to 2nd last row
Replies
3
Views
256
rlv01
R
M
  • Question Question
How to filter/lookup rows of data based on the latest date in a cell
Replies
8
Views
2K
Peter_SSs
Peter_SSs
R
  • Solved
Stumped on counting patients unique visits to a clinic
Replies
7
Views
526
Fluff
Fluff

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

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